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

learn more… | top users | synonyms (2)

4
votes
3answers
16k views

How to turn off identity for a database column of a table

I have mistakenly deleted a table in one one my database server. I have same set of data in the other server. What I have done is clicked on edit top 100 rows for the table, then copied it and pasted ...
1
vote
1answer
39 views

Select entries based on a combination of columns

I have a table with a definition like this: CREATE TABLE [dbo].[Contact] ( ClientNumber [numeric] (20, 0) NULL, ContactDate [Date] NULL, Name [text] NULL, AdditionalInformation [text] ...
6
votes
3answers
340 views

Which is the most efficient way to run a particular select query?

If I run the following code: select PolicyNumber, MAX(decpageid) as decpageid, Risk from StatRiskDecpages where PolicyNumber = 'AR-0000301132-04' group by PolicyNumber, Risk I get the following ...
1
vote
1answer
66 views

Foreign key not working

This is a very basic foreign Key issue, I presume. Unfortunately I am not a dba so it is a bit baffling to me. I have two tables, Report and Request. The Report table has a primary key of [RP_ID], ...
8
votes
1answer
1k views

Very high transactions per second

Our production server runs on average 4,000 transactions per second. In the past few days the average has jumped to 175,000 transactions per second. That is not a typo, it's 175K per sec. Looking at ...
0
votes
1answer
23 views

drawback of including primary key creation along in the table creation script

Sample script: CREATE TABLE [dbo].[Countries]( [Id] [nvarchar](2) NOT NULL, [Name] [nvarchar](100) NULL CONSTRAINT [PK_dbo.Countries] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = ...
1
vote
1answer
54 views

Inserting rows into a truncated table will increase the size?

Today I came across this question. Everyone knows truncating table won't reduce the actual size of DB, in my scenario I have 5 tables on a DB which almost occupied 50 GB, so if I truncate all the ...
7
votes
3answers
2k views

Capturing datetime of change in SQL Server CDC

So we've started exploring using change data capture on one of our production databases. We'd like to know the datetime of each change. Reading through walkthrough's and tutorials etc it seems that ...
0
votes
0answers
17 views

How to find difference in usage date based on results

I have a ticket details table that stores the information of transactions. Here is an example of the table data: Ticket_Number Detail_type_ID Description Date_Created TotalAmount Barcode ...
36
votes
10answers
95k views

Passing array parameters to a stored procedure

I've got a process that grabs a bunch of records (1000's) and operates on them, and when I'm done, I need to mark a large number of them as processed. I can indicate this with a big list of IDs. I'm ...
3
votes
1answer
65 views

VLDB - Create SQL DB Snapshot And Run Granular DBCC CHECKDB Commands

I have a 25TB db (SQL2008 Enterprise SP4) that appears to not have had CHECKDB run on it in production. No idea if it was run against a restored backup somewhere at sometime. Right now I don't have ...
11
votes
3answers
950 views

Executing same request from C# VS SSMS give different execution time

I have a request like this one SELECT [EstimateId], [CreationUserId], [EstimateStatusValueId], [LanguageId], [LocationId], [EstimatorUserId], [FilterUnitSystemTypeId], [EstimateNumber], ...
8
votes
3answers
9k views

Executing Stored Procedure that accesses another SQL instance

I apologize if this question repeats another already asked. I have searched for hours and have not found one that fits my situation. Desired Outcome A user using SQL authentication has execute ...
5
votes
7answers
18k views

Can't Login to SQL Server 2008 when running single user mode

I'm following the MS documentation here to migrate logins from one instance of SQL Server to another as part of the process described here. This involves putting the server into single user mode ...
0
votes
0answers
57 views

Install SP4 on SQL Server 2008 SP3 - named clustered instance MQ and PQ in A/A configuration on two Nodes A and B

Current config - We have two named instances called MQ and PQ which are clustered in A/A configuration on two nodes A (for PQ) and B (for MQ). MQ and PQ instances/features on both nodes A and B are ...
1
vote
2answers
48 views

how do i create Periods and weeks (think business reports) using 2 date ranges

Basically Fromdate is every Sunday and ToDate is every saturday, i need to programatically create the PERIOD column on the fly through a stored procedure where period is the month and week is the week ...
12
votes
5answers
37k views

Restoring a backup to an older version of SQL Server

When trying to restore a backup to a SQL Server Express 2008 database I got the following error: Restore failed for Server '...\SQLEXPRESS'. (Microsoft.SqlServer.SmoExtended) ...
1
vote
1answer
18k 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 ...
3
votes
1answer
98 views

Optimizer gets the wrong number of estimated rows even with updated stats [closed]

I have a procedure running in SQL Server 2008 that contains a query similar to the one below; SELECT /* various columns from table 1 and table 2 */ FROM dbo.tbl_1 AS a INNER JOIN dbo.tbl_2 AS b ON ...
0
votes
2answers
73 views

SUM() In SQL Server

Running SQL Server 2008 I can not wrap my mind around why these two queries, which are identical except for the first one has COALESCE() wrapped around the entire statement and the second one has ...
14
votes
3answers
12k views
0
votes
0answers
32 views

What is the difference between Microsoft SQL Server 2008 R2 and MySql Server 5.6 for handling Select ISNULL(MIN(StartDate, '1/1/1900 10:00'))?

I would like to know the difference between Microsoft SQL Server 2008 R2 and MySql Server 5.6 for parsing the following SQL statements. Microsoft SQL Server 2008 R2 SELECT ...
3
votes
2answers
514 views

Ola Hallengren with database mirroring

I am about to setup Ola Hallengrens database maintenance plan. We have our database mirrored and I was just wanting to know if I need to run the script on both my Sql Server instances or only the ...
32
votes
3answers
12k views

Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN

So we had a long running proc causing problems this morning (30 sec + run time). We decided to check to see if parameter sniffing was to blame. So, we rewrote the proc and set the incoming ...
1
vote
1answer
101 views

Business Intelligence Development Studio installation for Visual studio 2010 problem

I want to install Business Intelligence Development Studio for doing report development in Visual Studio 2010, however in my installation process, I cannot find BIDS in the feature selection. What ...
0
votes
1answer
32 views

Determining CPU Usage of last week

We got a production issue last week (high CPU spike) on 25-Feb due to which server reboot was performed but ticket landed our DBA queue just today for analysis on CPU spike. Could you pls help me in ...
1
vote
1answer
62 views

Store values in select as a temp variable and reuse them

I am trying to clean up a query that is ugly but does the job. I am wondering if it is possible to create a temp variable inside of a SELECT statement. I have Googled and not found any help. Basically ...
11
votes
5answers
2k views

Backing up & restoring 10-20 SQL Server databases to a ~synchronous state?

I need to backup up 10-20 SQL Server 2008 R2 databases with sizes between 10-50 GB, while they are online and used simultaneously by a single enterprise app. I also need to restore them to a state ...
-4
votes
1answer
26 views

Grant permission to user

Cannot execute as the database principal because the principal "ahsan" does not exist, this type of principal cannot be impersonated, or you do not have permission. Please tell me solution of ...
27
votes
4answers
41k views

How do I assign an entire Active Directory group security access in SQL Server 2008?

I would like to use integrated security with my internal application which is all on a domain. Unfortunately, I've never been able to get this to work well. I would like to assign an entire Exchange ...
1
vote
1answer
2k views

How to generate replication snapshot without locking tables

When I reinitialize subscription and generate new snapshot it locks all tables and makes the database unusable for a very long time (never waited it out). Is there anyway to do this without locking?
2
votes
1answer
86 views

supplementary characters in FreeTDS

I have a C++ application that connects to SQL Server 2008 through FreeTDS unixODBC on an RHEL box. However, i am unable to insert supplementary characters to the database through FreeTDS unixODBC. ...
0
votes
0answers
20 views

Operator Disable enable in sql server via JOB when fail over occured

I want to disable and enable the the operator in sql server when fail over is occurred either manual or automatic. I have created below alert on both principal as well as mirrored server and in ...
4
votes
3answers
7k views

How to compare two SQL Server 2008 configurations

I have a problem with my application that works on my dev SQL Server 2008 (Developer Edition), but not on a production SQL Server 2008. Is there an easy way to export the settings from the production ...
5
votes
4answers
12k views

How to find all positions of a string within another string

How can I find all the positions with patindex in a table or variable? declare @name nvarchar(max) set @name ='ali reza dar yek shabe barani ba yek ' + 'dokhtare khoshkel be disco raft va ali ...
3
votes
1answer
75 views

Login issue in Logshipping

I have configured log shipping in my primary serverA and secondary serverB. Everything works fine until I bring the standby database online. Users are not able to login to the secondary serverB ...
8
votes
4answers
19k views

Get Job id OR Job name from within executing job

I have a central DB that I write job outcomes on all my servers back to. I pass 3 parameters via powershell in a SQL job back to a sp on the central server that verifies the job should be running at ...
1
vote
1answer
15 views

execute as login for nonexistent login successful

On Sharepoint database server, we found 18456 state 11: infrastructure error for account Domain\machineA$ every 10 minutes and nt authority\anonymous every hour. I checked syslogins but didn't find ...
2
votes
2answers
679 views

Scripting all of available databases to .sql files containing schema+data+users with single command

I have a SQL 2008 Express with 80 databases in it. I want to move these databases completely to another server which is hosting a SQL 2008R2 Enterprise instance. As i'm told it is better to script ...
3
votes
1answer
390 views

find total money of parent node of tree according to a particular pattern

actually i am working on a project in which members are added in a tree pattern, and get the payment accordingly. My tbltestingtree structure is ass follow: Id ParentId IsLeft ...
0
votes
1answer
33 views

Maximum Server Memory adjust in blocks of 1024MB?

Our protocol is to set Maximum Server Memory at about 75% of RAM. For a 4GB RAM, sometimes it gets set at 3000MB and sometimes to 3072MB. There is a 2 or 3 percent difference in these values. Is ...
2
votes
5answers
10k views

SQL Server mirrored database is stuck in recovery mode

I have a database that is stuck in recovery mode for few days already. There are multiple threads about this, but the solutions there don't work for me. Here is what I tried already: RESTORE ...
7
votes
6answers
16k views

A query that lists all mapped users for a given login

When looking at the properties of a particular login, it's possible to see a list of users mapped to that login: I profiled SQL Server Management Studio (SSMS) and I see that SSMS connects to every ...
3
votes
3answers
67 views

Assigning a domain user to run MSSQLSERVER service 2008R2

I have done this on a server (windows server 2008) before, but can not achieve the same thing on another virtual server I want a low level domain user to run the MSSQLSERVER service so that I can ...
9
votes
5answers
5k views

Attach/detach vs. backup/restore

I need to transfer the database (as a whole) to another server, to make a duplicate database to setup another test environment. I have two choices: Make a full backup at source server/restore at ...
3
votes
3answers
148 views

How can I make a database backup go faster?

I recently attended an interview for my next job and was asked this: How can I speed up the backup of a database sized close to 1TB or 800 GB in SQL Server? My Answer: We can use Database ...
0
votes
1answer
498 views

Automate SQL Server restore from Prod to Dev

I would like to setup an automated restore job which takes the latest timestamp .bak file and restores it into the DEV instance. I currently have a job on the DEV box that uses the UNC path to the ...
0
votes
0answers
16 views

SQL Server users when restoring from older version to newer version WITH REPLACE

Let's say we have a SQL Server 2000 database with a user named user2000. We have a database called foo on SQL Server 2000, to which user2000 has been given access. We also have a SQL Server 2008 ...
0
votes
1answer
58 views

SQL Server Shrink

When running the Maintenance Plan Wizard to shrink a DB ... I am selecting all databases, however I have it set to shrink only when it grows beyond a specific size. If a database grows beyond a ...
1
vote
0answers
34 views

How can I make a database backup faster? [duplicate]

I recently attended an interview for my next job and was asked this: How can I speed up the backup of a database sized close to 1TB or 800 GB in SQL Server? My Answer: We can use Database ...