SQL Server 2008 (major build version 10.00.xxxx). Please also tag with sql-server.

learn more… | top users | synonyms (2)

8
votes
2answers
3k views

How do I write portable SQL that refers to a linked server?

I've got a stored procedure that refers to a linked server. In several places throughout the procedure I've got something like the following: INSERT INTO [TableName] (...Columns...) SELECT ...
2
votes
1answer
496 views

SQLDriverConnect fails when attempting to connect to Failover_Partner

SQLDriverConnect fails when attempting to connect to Failover_Partner. Connection string being used: Driver={SQL Native Client};Failover_Partner=tcp:dby;Server=tcp:dbx; ...
0
votes
1answer
181 views

Accepting only SSL connections to MS SQL server [closed]

I have followed http://support.microsoft.com/kb/316898 to force encryption on my MS SQL server. My intention is to reject any client connection that does not use SSL to connect. Am I on the right ...
1
vote
1answer
1k views

TSQL DateTime DataType - fractional seconds vs milliseconds

Wanting to get all records from a single day. So everything between 2013-03-05 00:00:00.000 and 2013-03-05 23:59:59.999. We're getting overlap at the backend with things that happened at 2013-03-06 ...
1
vote
1answer
16k views

Trying to install SQL Server 2008 Management Studio Express

On a Wndows Server 2008 R2 64 bits, I installed Visual Studio 2010 Express, which installed a SQL Server 2008 Express: Trying to install a SQL Server Management Studio, first I'm required to ...
0
votes
2answers
3k views

Check Constraint with Case Statement

I want to add a check constraint on a column if entered value for column is greater than 3 than it should be saved as 1 else entered value. For that scenario I am using below query but on insertion of ...
5
votes
3answers
1k views

What could cause the wrong ID to be inserted?

I have a SQL Server 2008 server (build 10.0.5500). Earlier this week I ran this on a table that already had data in it: delete from dbo.table go dbcc checkident('dbo.table',reseed,0) When the ...
3
votes
1answer
873 views

Doing a point in time restore with CDC enabled; Possible?

I discovered this week, the hard way, that the database restore options NORECOVERY and KEEP_CDC are mutually exclusive. So, this begs the question. How do you do a database restore, keeping CDC ...
2
votes
1answer
263 views

varchar storage and comparisons in SQL Server 2008

I have a large table with a structure similar to this: Id bigint primary key Sequence int Parent int foreign key Data1 varchar(20) Data2 varchar(20) Data3 varchar(20) Data4 ...
6
votes
6answers
1k views

Move SQL Server 2008 database from machine A to B with no downtime

I got a bit of a problem. I have a SQL Server database in box A which has approximately 60gb of data. I need to move this to a new machine but I cannot have downtime at all. Every second that goes by ...
3
votes
1answer
52 views

Are database connections from an application expensive from a performance point of view?

A colleague tells me that connections to a database are expensive and could harm performance of the application. They ask me to create a procedure that calls say another 8 procedures, that way they ...
5
votes
3answers
134 views

Is there any limit on how deep the Server Owner can access my data?

this is my first post on DBA. I'd like to get some clear ideas from you. First of all, this may sound as a discussion but it's not a debate. What I'm looking for could be something like "Yes, they ...
2
votes
2answers
181 views

I want to create a mirror in SQL Server 2008, but the database is in the status “restoring …”

I'm trying to create a mirror in SQL Server 2008, and did the following: On the principal server USE master; GO ALTER DATABASE Test SET RECOVERY FULL; GO; BACKUP DATABASE Test TO DISK = ...
1
vote
1answer
264 views

Recover Jobs from a MSDB

I need to recover the jobs from a MSDB file. I had to fully reinstall a new SQL Server and Windows Server, but apparently I forgot to ever do a MSDB backup. Yup, big blunder. Now I need to restore it ...
1
vote
3answers
433 views

dangers of using SHRINKFILE in sql server 2008 [duplicate]

I'm trying to shrink the file size of a massive log file and was wondering how I estimate the size of the log file and what the dangers in running this are
0
votes
1answer
60 views

MSSQL how do I query sql for a latest state record after latest date

I have a table with openingdays, location, state (Open, Day Closed, Shift Closed) and the actual date. I would like to know when the day is closed for the last time (state = D) and everything what ...
0
votes
0answers
21 views

Broken partition function/schemes

The dilemma SQL Server 2008 Enterprise 2 partition schemes using the same partition function partition function splits on date column, monthly basis maintenance script to split partition function ...
1
vote
2answers
1k views

Increasing timeout period for schema compare

I need to increase the timeout period for schema comparison. I'm using Visual Studio 2012 to compare two SQL Server 2008 R2 databases on Windows Server 2008. I believe this is something I need to ...
2
votes
2answers
1k views

Specify Values for Template Parameters — command

When I create a stored procedure in SQL Server 2008 by using the template, the comments specify a hot-key combination that no longer works: -- Use the Specify Values for Template Parameters -- ...
2
votes
1answer
3k views

Why does SQL Server fail to create a procedure referencing a linked server?

Red Gate Deployment Manager (RGDM) today failed with a "login failed" error even though it had administrative control of the target server: 2013-11-04 17:13:22 +00:00 INFO Creating ...
11
votes
2answers
1k views

alert when any job in a job category fails

Is it possible to set up an alert in SQL Server 2008 that will send an e-mail anytime a job in a specific category fails? I am wondering because I would like to set up an e-mail anytime an SSRS ...
7
votes
1answer
5k views

SQL Server Job History Disappears After 5 Days

I feel like this is a silly question but for the life of me, I can't track down what is going on. I have just taken over administration of several servers and on all of them, the job history is being ...
0
votes
0answers
25 views

Rowlock hint is doing Lock escalation at table level. i dont know why.? [closed]

I want to first do select to read rows and then do some calculations and then finally update the read row. So my transaction will be in 3 steps: select the records (In this session only allow other ...
4
votes
1answer
104 views

Viewing Temp Tables

I am using SQL Server 2008 and my stored proceduce does inserts into #temptable. My procedure has been running for 32 hours (which isn't unheard of considering the amount of data that it has to ...
1
vote
0answers
42 views

Window function vs group by method: find value for MAX ID

I'm trying to understand the performance issue with Window function when i select value for MAX ID --Old group By method SELECT tj.MainProcedure , tj.StartTime , tj.EndTime , ...
1
vote
2answers
3k views

SSMS: Show execution plan for a stored procedure as a single tree

I execute a stored procedure with an option "Include Actual Execution Plan". The execution plan always is represented by multiple statements each of which has its own execution plan. MSDN states the ...
0
votes
0answers
27 views

Do both the sessions need to have UPDLOCK to avoid deadlocks

The benifit of UPDLOCK is that it avoids deadlock in case both sessions run the below query at the same time - The table has clustered index on ID column ----session 1 -------- begin transaction ...
0
votes
0answers
30 views

SQL Server: Update linked table times out

I have 4 linked SQL Server 2008 R2 servers in the same network, and distributed selection queries works fine. However when I try to run something like that: UPDATE [LINKED SERVER NAME].[DB ...
1
vote
1answer
35 views

Setting up SQL Server to handle queries from external domains

I am not a SQL Server admin. I have tried to google this but can't seem to use the right words to get the right info and they all talk about remote connections to a SQL Server in terms of admin and ...
15
votes
2answers
2k views

Why does SQL Injection not happen on this query inside a stored procedure?

I made the following stored procedure: ALTER PROCEDURE usp_actorBirthdays (@nameString nvarchar(100), @actorgender nvarchar(100)) AS SELECT ActorDOB, ActorName FROM tblActor WHERE ActorName LIKE '%' ...
4
votes
2answers
36k views

Get the 2nd or 3rd occurrence of a value in a delimited string

I have the following table: ========================================================== | Name_Level_Class_Section | Phone Num | ...
3
votes
1answer
86 views

Determine Which Parameters Passed Into Stored Procedure

Is there a way to determine (from within a stored procedure) which parameters were passed into a stored procedure (vs. those which just took the default value)?
5
votes
2answers
14k views

How to Drop Database in Single_User Mode

How do I drop a database which shows DatabaseName (Single User) as its name? When I try to delete it, I get the following error: Alter failed for Database 'DatabaseName'. ...
0
votes
2answers
44 views

SQL Server AlwaysOn from multiple origins to a single secondary replica server

I started working in a small company which have three separate databases, for three different systems. They are SQL Server 2008, 2012 and 2014, installed in different servers. We'd like to set up an ...
1
vote
1answer
101 views

How to get SQL Server CPU and Memory read and write usage within two weeks?

Is there any a quick way or quick script on how to get the CPU and Memory usage within a time range (at least two weeks)? I did my research earlier and it seems that the procedures available seems ...
0
votes
2answers
213 views

How to restore a database using mdf and ldf files in SQL Server 2008?

Asking for expert opinion. A very unfortunate event happened. One of our PCs crashed few days ago. That PC had SQL Server 2008 installed. Unfortunately no backup was running on that server. We sent ...
3
votes
2answers
12k views

CREATE DATABASE permission denied in database 'master'. unable to get the permission

I am making a database in sql server and it shows an error that "CREATE DATABASE permission denied in database 'master'" I am using the administrator log in itself. What i need to do? Please suggest a ...
3
votes
1answer
1k views

Database design for Magazine website

I need to design a CMS based website using asp.net web form (c#) and MS SQL Server as backend database for a magazine website which will have monthly issue. I am finding it hard to design a database ...
-1
votes
2answers
26 views

how do i take the end value of a colum, convert to date and insert into a new column

I have a column [week] in a table [wkd] with a value of 11/08 - 11/14. They are strings with 2 date ranges. (nov 8 - nov 14). what I need to do is get 11/14 into a new date column [weekending] so ...
5
votes
4answers
1k views

SQL Server renaming table from another database

Is there a way in SQL Server to rename a table in another database? When you are in current database and migrating data over to another database in the TEMP table and then renaming TEMP table in that ...
2
votes
1answer
66 views

looking for a cleaner way to select from multiple tables

I'm currently using this code to select from 2 tables using a stored procedure. DECLARE @now as varchar(26) set @now = CONVERT(VARCHAR(26), SYSDATETIME(), 9) --Select 'SMD_' + ...
0
votes
3answers
55 views

Connecting to a SQL server whose IP changes

I want to connect to a server whose IP is dynamic via a VB.net application. I have configured the connection string in my VB application to a static IP. I also gave a static IP to the database. ...
1
vote
1answer
42 views

Aggregate SQL Rows into single column

I would like to aggregate results of a column into a single row using MS SQL. I have create a sql fiddle which showcases the issue: http://sqlfiddle.com/#!3/384d5/2 Basically I would like all 'Tag' ...
1
vote
1answer
24 views

SQL Server 2008 job failure

I have a problem on my servers sitting on different domains. I created a linked server that executes successfully and I can list the catalogue but when I schedule a job to execute I get this error: ...
1
vote
0answers
2k views

EXECUTE AS over Linked Servers

I have a front end that logs into the DB as "logInA", and only has permission to "schemaA" stored procs, which use EXECUTE AS "logInB", (I have also tried EXECUTE AS 'DBO'), to handle all the business ...
0
votes
1answer
1k views

Sql Server merge replication primary key violations

Using Sql Server 2008. Pretend I have a publisher and 2 subscribers A and B. The replicated table has a guid on it and also a primary key that is not the guid. Frequently, updates happen at both ...
1
vote
2answers
4k views

Database after restore process still display Restoring

I restored Database from Backup File (in Management Studio) and after restore, Database showed Restoring, then I deleted files from disk (engine's stopped), then I tried Restore again with success, on ...
4
votes
2answers
22k views

Convert a datetime column to number of seconds

In my SQL Server database, I have a datetime column. What is a good way to create a new column that represents the long value for the datetime column. The long would represent a number of seconds.
0
votes
0answers
41 views

Issue with multiple execution of sys.sp_addextendedproperty

I'm using SQL Server 2008 R2. I need to store a text as a description of each table of my DB. But I don't want to do this with wizard. I handled to build some code for this purpose. Here's a part of ...
0
votes
0answers
61 views

Help diagnosing a single-table, single row deadlock

We're currently using an ORM-controlled, table-based, identity generation scheme to generate the IDs for our entities. The table is as follows: create table id_seq ( tbl varchar(255) not null, id ...