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

learn more… | top users | synonyms (2)

1
vote
1answer
30 views

Table frequency metric Count (minimum 2 hits every 5 seconds using a datestamp within 24 hours)

I need to find out how many times a table has an insert within a 24 hour period per 5 seconds, if the table is inserted at least twice (2x) or more for each 5 seconds of the day, it should be counted. ...
5
votes
2answers
4k views

Why isn't the objectname and indexname populated in deadlock graph?

I got a request to check out some errors on a 2008 sp3 server (10.0.5500.0 - Enterprise Edition (64-bit)) and see if I could find deadlocks that were happening earlier. Traceflag 1222 wasn't enabled ...
17
votes
2answers
2k views

If positive, sum all items. If negative, return each one

I am needing to find a way to SUM() all of the positive values for num and return the SUM() of all positive numbers and an individual row for each negative number. Below is a sample DDL: Create ...
4
votes
1answer
770 views

Cannot Delete FileStream Group from Sql Server 2008

We have migrated all our varbinary(max) column data to Azure Blob Storage and so we want to remove the old filestream columns that remain in our Sql 2008 database and the filestream filegroup but when ...
3
votes
1answer
48 views

Decrypting Previously Encrypted Data Returns NULL

I'm having trouble decrypting data that has previously been encrypted and stored in a table. After opening my symmetric key and encrypting the data using EncyptByKey(), I get NULL values when ...
0
votes
0answers
18 views

sql the job was stopped prior to completion by shutdown sequence 0

I've transaction replication set up and running fine but today my Log Reader agent started having this warning : sql the job was stopped prior to completion by shutdown sequence 0 . The job was ...
2
votes
2answers
263 views

Recommended memory configuration for many SQL Server instances per server

I am currently managing a server cluster for a SaaS system. We currently have 6 SQL servers running MSSQL 2008 R2. Each server is a quad core virtual machine with 16GB of RAM provisioned on a server ...
0
votes
1answer
352 views

Prevent gaps while using Identity/sequence object in sql server

I have a table and I am trying to insert values in it. I want to prevent gaps in the primary key column. For primary column I have tried using Identity / sequence objects. I know that this is ...
0
votes
0answers
29 views

how to restore database without the physical backup sql server 2008 [on hold]

I have one database is recently have data loss after stop mirroring(sql server 2008), the best part is the principal database is also data loss, can i recover back the loss data?. i don't have the ...
0
votes
2answers
166 views

Mapping a Login to a User

On my production system I login to SQL Server with CPSDOM\mconnors. When I execute SELECT CURRENT_USER; it returns CPSDOM\mconnors. We use Windows authentication on our DBs. On my test system I ...
2
votes
1answer
3k views

Could not open File Control Bank (FCB) for invalid file ID X when restoring from snapshot

We have a scenario in which we, during integration tests, make a backup of an existing database and restore it under a new name. We then create a snapshot of our "new" database. After each test we ...
11
votes
4answers
36k views

Query to report disk space allocation and used space

We are using 6 databases in total for an application, and we can only share 4TB of space among all 6 auto-grow databases (via SAN storage). I'd like to write a query (report) for a single database ...
1
vote
3answers
196 views

DISTINCT Role in SELECT

I have a table with commercial agents and some invoices that they have manipulated. I am searching the agents based on very simple conditions, such as whether the invoice identifiers are null or not. ...
2
votes
1answer
464 views

How to include concatenated values in a dynamic pivot column value?

I am extending a dynamic pivot query. The original query works fine. In the second query, instead of displaying the date as a value for the pivot column, I would like to display a concatenated column. ...
1
vote
0answers
33 views

Sql - sort according to other PK column

I Have this table : table : [SheetGroupRank] ------------------------- | Sheet_ID | int | | Group_ID | int | | Rank | int | ------------------------- Sheet_ID and Group_ID ...
0
votes
2answers
25 views

KILLED/ROLLBACK query doesn't stop running

I've tried to kill a query on ms sql server management studio. Now it has the status killed/rollback. When I use: kill 74 with statusonly it doesn't make any progress. It is still at 0%. How can I ...
1
vote
1answer
141 views

How to backup SQL Server database once evaluation period has expired

I'm using Microsoft SQL Server Management Studio from last one month that is evaluation period software. I'm not able to login in management studio. when I enter my user name and password it gives the ...
1
vote
1answer
41 views

Trim a value in an xml column in MS SQL Server

I have a database with an XML column. I am trying to update (trim) an xml value of the xml column. I am running on Windows, SQL Server 2008 Express. I did not find any way to trim directly in Xquery ...
1
vote
0answers
27 views

SQL Server Maintenance Plan Help

SQL Server Maintenance Plan Help I'm using SQL Server Maintenance and found a way to selectively turn on and off various features of the plan via it's associated job shown in Figure A. This can be ...
7
votes
0answers
165 views

ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) doesn't use index on (A,B,C)

Consider these two functions: ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) As far as I understand, they produce exactly the same result. In other ...
12
votes
5answers
8k views

Why do linked servers have a limitation of 10 branches in a CASE expression?

Why does this CASE expression: SELECT CASE column WHEN 'a' THEN '1' WHEN 'b' THEN '2' ... c -> i WHEN 'j' THEN '10' WHEN 'k' THEN '11' END [col] ...
4
votes
1answer
2k views

Receiving error Error: 18059, Severity: 20, State: 1 in the SQL Logs

I'm using SQL Server 2008 Sp3 Lately I'm having errors in the SQL Logs: Error: 18059, Severity: 20, State: 1. The connection has been dropped because the principal that opened it ...
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
vote
0answers
18 views

Automatically updating Remote Server(B) Table when there is a Update in Server(A) Table with different schemas

I am having an Update trigger in Server A calling a Stored Procedure on Server B(through Linked Server) to update rows in Table2 in Server B, whenever there is a update in rows in Table1 (in Server A)....
-1
votes
0answers
26 views

How Can I Optimize a query using SUM fields in one table, Group by a fields in other table [closed]

I have a code as bellow SELECT T1.CustomerID, COUNT(T2.TransID) AS NoOfTrans, SUM(T2.Qty) AS TotalQty, SUM(isnull(T2.DiscountAmount,0)) AS TotalDiscountAmount, SUM(...
2
votes
3answers
43 views

SQL Login currently in use or not

Recently we did a change on a client's production server. The change was to rename an sa login. Once we renamed the sa login we found out the client was having a problem because sa login was used in ...
1
vote
1answer
53 views

Help me about syntax STUFF (SELECT… for XML PATH)

Here is script of 3 table: create table products( pid varchar(10), pname varchar(20)); create table [Order Details]( oid varchar(10), pid varchar(10), quantity int); create table orders ( oid ...
2
votes
1answer
200 views

CPU Time Slower in SQL Server 2014 than in SQL Server 2008

We’re upgrading a virtualized database server from an old SQL Server 2008 R2 Standard (vmware) to a new shiny SQL Server 2014 Standard server (hyper-v). We were doing some performance testing and to ...
0
votes
1answer
323 views

Migration script fires 'The data definition language (DDL) command cannot be executed at the Subscriber'

Yesterday I had run a migration script, produced by Visual Studio 2008 schema compare option, on a SQL Server 2008 database. It had run correctly. But today, when I executed the same script, it threw ...
7
votes
3answers
44k 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 | ======================================================...
0
votes
1answer
146 views

Please explain why deadlock is detected in my example

I think Deadlock will happen when T1 lock tabA and T2 lock tabB. Then querying T1 to tabB (locked by T2) and T2 querying to tabA (locked by T1). So T1 and T2 will wait and wait till end of life! But ...
0
votes
1answer
311 views

ERROR: Failed to convert parameter value from a String to a DateTime

I have faced this error: Failed to convert parameter value from a String to a DateTime when trying to pass multiple selected dates from checkboxlist to my parameter to be used in sql. I have tried ...
0
votes
0answers
7 views

Transient Log Shipping Restore Error - Could not redo log record (172980:741440:22), for transaction ID (0:1889746485), on page (1:28106052)

I'm running SQL Server 2008 r2 sp3 and am taking transaction log backups every 5 minutes from my primary server in a log shipping scenario. I have multiple standby servers that are log shipped to. On ...
3
votes
2answers
95 views

Collect disk space metrics for whole SQL Server instance

I am quite new to SQL Server. I would like to collect database disk space metrics for the SQL Server instance as a whole. I have found a code sample that can collect disk space information per ...
0
votes
2answers
90 views

Can I convert date format for this value '19920000'?

I have a value 19920000. This is date format yyyyMMdd. I want to save by date field in the table. You may see it is invalid data because day and month value is zero. Can I convert date format to this ...
2
votes
1answer
231 views

Is there a better alternative than LIKE for this query?

I am trying to retrieve data from a table where there are codes for projects which are identified by the first three numbers, the rest are unique. So, for example, the query is: SELECT * FROM TableA ...
-1
votes
2answers
2k views

Deadlock from select statements

I am getting deadlock from 2 select statements. What is the reason for that? And how can I fix this issue? Deadlocked processes: 11278 Victim process: 5185 Lock type: Index/(Key) Both ...
0
votes
1answer
19 views

SQL export select statement to Excel using script

I have a project that requires me to create a script for migration between test and live data-bases. What i want to do is create an extract from the test database into an excel sheet or a txt file ...
17
votes
3answers
5k views

How can I add a rowversion column to a large table with minimal downtime

Using SQL Server 2008 and later, I want to add a rowversion column to a large table however when I simply ALTER TABLE [Tablename] ADD Rowversion [Rowversion] NOT NULL Then the table is unavailable ...
14
votes
4answers
80k views

How to handle TimeZone properly in SQL Server?

My local development server is in the Middle East, but my production server is in the UK. I need to show the date to the user in their time zone. For example, if a user is in Saudi Arabia then I ...
4
votes
2answers
1k views

SQL: transfer database schema

If i want to transfer schema ownership to another schema i know that i need to use something like: ALTER SCHEMA destinationschema TRANSFER sourceschema.objectname; But how is it different from: ...
0
votes
2answers
3k views

Error installing SQL Server 2008 - all componets installed successfully except database engine

Below is the error I copied, please someone help me, I have tried at least 10 times in the last 3 days. It is SQL Server 2008 R2 and I wanted to install it on Windows 7. Microsoft SQL Server 2008 ...
5
votes
2answers
3k views

SQL Server 2008 R2 Restore COPY_ONLY full backup with transaction logs

After doing some research I cannot seem to find an answer to this question. Background I am attempting to setup a backup plan that fits the following three requirements: Reliability of backups, ...
2
votes
1answer
31 views

Pre-checking execute privs for SPs

We have a number of DBs where EXECUTE privs are required on a SP by SP basic. We have other DBs where we don't need to do this at all. In the latter case, I understand that this is a blanket GRANT, ...
15
votes
1answer
8k views

Are there any risks to granting users SQL Server SHOWPLAN permission?

I'm doing some performance tuning on a large SQL server 2008 database, and the IT group is unwilling to give SHOWPLAN permission. In the past, "Show Execution Plan" has been the most effective way to ...
1
vote
0answers
35 views

How to convert datetime string of different formats in a valid datetime in SQL 2008

I have a table in SQL database in which there is an NVARCHAR(MAX) column. In it, the data is stored as 'Datetime1&Datetime2&Datetime3&Datetime4...'(& separated dates. I know this is ...
14
votes
4answers
59k views

Easily show rows that are different between two tables or queries

Imagine you have two different tables/queries that are supposed to have/return identical data. You want to verify this. What's an easy way to show any unmatched rows from each table just like the ...
3
votes
2answers
2k views

What does Reinitialize All Subscriptions actually do to the databases

I tried to find the answer and came across these MSDN pages but they don't actually explain what happens really: http://msdn.microsoft.com/en-us/library/ms152771.aspx http://msdn.microsoft.com/en-us/...
8
votes
2answers
17k views

Automatic decimal rounding issue

The question is relatively simple. I need to calculate 3 columns where the mid results are huge decimals, and I'm running into a problem early on with SQL Server basically rounding the decimals ...
4
votes
2answers
333 views

How do I order by NULLS LAST if my database doesn't support it?

Select FullName, Login FROM [User] ORDER BY FullName desc, login asc Results FullName Login ----------------------------- ------------------------------ M...... ...