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

learn more… | top users | synonyms (2)

2
votes
1answer
1k views

How to handle huge table

We have a problem with a few tables that are very very big slowing down the system. so my question is if that size is normal for the amount of rows in the table, and also what could be done to make it ...
1
vote
0answers
30 views

Why am I getting this network error when setting up database mirroring?

I have two servers, one in the Portsmouth, NH datacenter, one in the Kansas City datacenter, same domain. Trying to set up mirroring. First, does it matter that one server has Windows 2008R2 server ...
5
votes
4answers
2k views

Speeding up COUNT(*) - WHERE clause slowing query

The following query takes 497 ms to run, if I remove the AND portion onwards it only takes 320 ms. Is there any way to speed this up? The only indexes I have on 'messages' table is a PK on ...
4
votes
2answers
115 views

MSDB log file size

We have a SQL Server 2008 cluster with 60 databases. Full backup runs every night, log backup runs every 15 minutes. The MSDB database recovery model is set to simple (default). To be able to see ...
6
votes
2answers
670 views

Is ELSE 0 implied in my COUNT CASE WHEN statement?

What's the difference between COUNT(CASE WHEN [Column A] = ____ THEN 1 END and COUNT(CASE WHEN [Column A] = ____ THEN 1 ELSE 0 END? I've been using the former and I haven't seen the difference thus ...
3
votes
1answer
38 views

Clarification on Database Replication

SQL Server 2008 I have to do database replication which is a new for me. Goal is have mirror database on servers 2 and 3. When we set up database replication in the Server 2, 3 from Server # 1, ...
4
votes
2answers
2k views

How to remove identity specification for multiple tables

I am having total 109 table in Database and now i am migrating to Amazon server with identity insert and now i want to remove auto increment column specification for all tables. How set auto increment ...
6
votes
3answers
2k views

Domain group as single login, but still many different database users?

We want to use Windows Authentification on SQL Server 2008 within Active Directory. Is it possible to use an AD Group as a LOGIN to the server, but then to map the actual AD USER to a database user? ...
0
votes
1answer
48 views

SQL Dependent Percentage

Sample data Excepted output Math Operation Sum all Period time for each EmpName as Total Employee Time Sum Similar Tasks For EmpName, divide it by Total Employee Time and multiply by 100% ...
4
votes
3answers
1k views

Twice job results in sql server, last one is left in progress

I have the job MP - Create CSV which execute a package SSIS in sql server 2008, when I check for the job history I see the result at step 0, I expand and I have 2 results for the step 1. Fist result ...
2
votes
2answers
40 views

simpler way to write if today or tomorrow is between ColA or ColB

SELECT * FROM my_table WHERE getdate() between SPRFRDTE and SPRTODTE or dateadd(DD, 1, getdate()) between SPRFRDTE and SPRTODTE is there a way to write this as if TODAY or TOMORROW is between ...
8
votes
2answers
1k views

How to schedule jobs in Sql server 2008 for less than 10 sec?

I want to run a job every 3 seconds, however in SQL Server 2008 we cannot define an interval of less than 10 seconds. The job is used to insert/update visitor information, and segmentation ...
8
votes
2answers
741 views

Are SQL Server Database writes slower with Snapshot Isolation?

I have a lot of deadlocks going on in my system. I would like to use Snapshot Isolation to fix them, but my DBA has reserves about it. One of his concerns is that Snapshot Isolation slows down ...
3
votes
2answers
1k views

Is there a way to calculate yesterday / today of last month / year in SQL?

Is there a way to calculate today (and yesterday) of last month (and last year) in SQL? Assuming today is 26-May-2015 then how to find? Today of last year i.e. 26-May-2014? Today of last month i.e. ...
1
vote
1answer
419 views

Selecting rows from same name table from multiple databases in single result set

For some reason, I have a table Table_1 in n databases (same name and schema structure) instead of one single databases. The columns are like this: | Column_1 | Column_2 | I can select data from ...
1
vote
1answer
231 views

Print the execution time on messages Pane?

How I can print the execution time of each query on messages Pane? For example the Select * from tbl1 runs for 10 mins then Select * from tbl2 runs for 15mins and so on.. Select * from tbl1 Go Select ...
4
votes
4answers
60 views

How do I fix an Orphaned User in Log Shipped version of a SQL Server 2008 database

Forgive my greenness.. but would appreciate some help on this... We are doing development work on an instance of SQL Server 2008 and have multiple databases on that server that are core to our ...
2
votes
1answer
44 views

SSIS export to flat file - how to configure all DataTypes for columns w/o manual entry?

In my OLE DB Source to Flat File Destination Data Flow, I have quite a few columns and it's excruciating to manually set each one to decimal with a scale of 4. I tried adjusting my SQL code in the ...
-8
votes
1answer
74 views

FULL OUTER JOIN much faster than UNION ALL in SSMS, but slower in Excel [closed]

I am trying to optimise a view pulled from SQL Server 2008 to Excel, and I've managed to drastically improve the performance on SSMS. Using FULL OUTER JOIN instead of UNION ALL, I've managed to ...
4
votes
1answer
766 views

Transaction log growing on a mirrored principal

I have a pair of SQL Server 2008 R2 servers hosting a synchronously mirrored database with no witness. This weekend, we had to do some network maintenance so I paused the mirroring. Upon resuming, the ...
-1
votes
1answer
649 views

dead lock when updating [closed]

Update Operation.TrTable Set RecordId = RecordTABLE.newRecordId From Operation.TrTable tr Inner Join ( SELECT r.Id AS newRecordId, r.KeyM AS MappingKey From ...
1
vote
2answers
194 views

How to order by alphanumeric codes in SQL Server [closed]

I have a table with some project codes. These codes are e.g.: B001, B002, Z15001, Z14001, P003,... I need this result table: Z15001 Z15002 ... B001 B002 ... C001 C002 ... Z14099 Z14098 Z14097 ... ...
0
votes
2answers
88 views

SQL Job Fails - Why?

This is my log - if I run the procedure locally it will still perform as it should and generate the message, but when it is run as a SQL Job it fails. (For security I have changed the server name and ...
1
vote
1answer
301 views

Error when start to attach mdf - : parameter name: ncolindex actual value was -1 (Microsoft.Sql Server.Grid Control)

Got the following error when first attempted to attach a database parameter name: ncolindex actual value was -1 (Microsoft.Sql Server.Grid Control) What does this mean and how do I correct ?
10
votes
2answers
5k views

Tracking stored procedure usage

Besides using SQL Server Profiler, is there any way to track which stored procedures are being used, or at least when they were last executed?
10
votes
1answer
845 views

Why is “Lookup” colored as a function reserved word in SQL Server?

In SSMS 2208, the identifier "Lookup" is colored hot pink as if it were a function (same color as, say, "Power" or "Convert"). Why? I cannot find it in the official list of reserved words. Searches ...
0
votes
0answers
12 views

List All Stored Procedures that Query Specific Table [duplicate]

I am using this syntax, and it returns no stored procedure names (however I know their are 2 stored procedures that query the table). This is SQL Server 2008 and this is my syntax: SELECT DISTINCT ...
1
vote
1answer
86 views

Sum of time spans

I have table like that Code Time Value RowNum -------- ----------------------- ----------- ------- I370902P 2015-12-04 09:00:00.000 5.0 1 I370902P 2015-12-04 ...
1
vote
2answers
116 views

How should I delete branch from adjacency list in SQL Server?

Given a basic acyclic adjacency list like this: What is the most effective way to handle deletions of branches? My current approach uses a recursive CTE to find all descendant nodes and their ...
0
votes
1answer
23 views

SQL Server authentication failed after server promote

we recently promoted a Windows 2008R2 server to domain controller. We then found that it hosted a SQL Server 2008R2 Express database instance. If we try to login to the DB with the domain admin ...
1
vote
0answers
55 views

SqlException (0x80131904): Cannot open database

We keep getting the error SqlException (0x80131904): Cannot open database in our environment. We are sure the connectionstring used is correct. The user has the necessary permissions in the database ...
1
vote
1answer
29 views

SQL Server 2008 running schema query without identifiable cause

I am analyzing the performance on login to an application and in particular making sure the database response is acceptable. In running just the simple Activity Monitor I can see that there are two ...
-7
votes
1answer
47 views

Need Immediate Help To Develope T-SQL

Hi DBAs, I want to Develop a T-SQL For Fetch data from following Table. But Condition is : I want to select only those data which status has all three status. suppose for id 1 : It has Only two ...
0
votes
0answers
14 views

How to move SQL Server to a different domain [duplicate]

I am new to data base usage and I want to know a simple steps on how to move an application server to a different domain without losing any data. Right now I'm not very good with SQL but I want to ...
3
votes
1answer
3k views

Create view in a different database with dynamic SQL

As part of a larger project I'm trying to create a new view on each database in a SQL Server instance. I've created a stored procedure that among other things is looping through the databases in ...
3
votes
0answers
28 views

Network Traffic increase with Small Result Set

I am currently executing a stored procedure that is using up a ton of network traffic when it executes. The stored proc executes multiple stored procs within itself as well as functions and ...
1
vote
3answers
4k views

copy only tables with data from one database to another database [duplicate]

I have two database , dbOne(version - 10.50.1600 - locate in office server ) and dbTwo(version - 10.0.1600 - locate in my local server) . I want to copy dbOne's tables with data to dbTwo . Is there ...
18
votes
6answers
57k views

How do you kick users out of a SQL Server 2008 database?

We need to do a restore, and cannot because other users are connected. We thought we had disconnected every process, but apparently not. How can we, from Management Studio, kick off everyone else so ...
10
votes
2answers
14k views

Suppress SQL Server Management Studio auto complete

Is there a way to temporarily suppress SQL Server Management Studio's auto-complete while typing a query? I don't want to disable auto-complete completely just say hold down some key while typing in a ...
1
vote
2answers
46 views

SQL Query to find Records companywise

I want a query that will display rows of those COMPANIES who are giving theft cover and key loss Having status is yes See the Sample Data below : addon status amt1 amt2 company ...
7
votes
1answer
162 views

Can I recover a SQL database from Standby/Read-Only without a standby file?

Is there any way to recover a SQL database from Standby/Read-Only mode if the "standby file" is missing? (I see some references calling this a TUF file in a log-shipping scenario, but in my case, it ...
2
votes
2answers
87 views

idera - why it restores databases faster

We have a 2gb live database that takes forever restore whenever I need some live data to test something. If I use idera it takes 5 mins at most. When I use the restore database command it can take ...
3
votes
1answer
503 views

How to track overhead of Change Tracking in SQL Server

We're going to implement a solution based on Change Tracking feature from SQL Server. Before going in production (even after), we'd like to keep track of the overhead the CT will introduce. Once in ...
1
vote
1answer
59 views

Using NOCOUNT to Improve Procedure Performance

I am looking to improve the performance of a certain procedure and I wanted to begin with the insertion of the SET NOCOUNT ON. I've read a couple of articles on the subject: Aaron Bertrand SET ...
3
votes
1answer
66 views

Safe to start Mirroring on Production Server?

We have a 350 Gig DB on SQL Server 2008. We intend to backup and restore it then immediately start mirroring. Is starting a mirror a dangerous operation to do on a production database ? Can I expect ...
8
votes
2answers
99 views

SQL Server equivalent of Oracle USING INDEX clause

Is there a SQL Server 2008 equivalent of the USING INDEX clause in Oracle? Specifically for the construct: CREATE TABLE c(c1 INT, c2 INT); CREATE INDEX ci ON c (c1, c2); ALTER TABLE c ADD CONSTRAINT ...
5
votes
1answer
4k views

Who changed/modified stored procedure in SSMS 2005 or 2008? [duplicate]

Possible Duplicate: Determining how a schema change occurred? I have a stored procedure that was modified late last night and just spent the majority of the morning tracing issues back to ...
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
495 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
180 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 ...