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

learn more… | top users | synonyms (2)

0
votes
2answers
14 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
134 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
36 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
21 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
134 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
7k 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
25 views

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

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 ...
-4
votes
1answer
40 views

Insert and Update a sql table at same time is not working [closed]

I am facing an issue with my SQL DB. I have a stored proc which inserts data into a tables [Table1],[Table2],[Table3]. But when I tried to update some data which already exists in those tables at the ...
-1
votes
0answers
45 views
1
vote
1answer
52 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
199 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
320 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
145 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
302 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
91 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
89 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
227 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 ...
13
votes
4answers
79k 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, ...
1
vote
1answer
29 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
33 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
329 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...... ...
3
votes
2answers
525 views

Migration to databases Domain Users do not have access

I migrated databases to new servers, however the applications that were previously used with the databases are failing to load. I have changed the connections and etc. The jobs also seem to be failing....
1
vote
2answers
44 views

SSRS 2008 Broken in Chrome

Note, this is 2008, not 2008 R2. This isn't an unknown problem, but I can't seem to get it to work. Seems like all of the solutions I've tried relate to 2008 R2 and above versions. Chrome updated and ...
0
votes
1answer
57 views

Effect of Reorganizing and Rebuilding an index [closed]

What are the effects of Index Rebuilding and Reorganizing on clustered and non-clustered indexes. How does these 2 operations affect the logical storage and physical storage of data in Hard disk?
1
vote
0answers
31 views

Migrating from DEV SQL Server to PROD - Any Point to Script the Statistics from DEV to prod?

I have a new database (powers a new website) in a dev environment. I have everything scripted out to create the DB, objects, and data in my production environment. In that DB, auto create and auto ...
0
votes
1answer
145 views

Maintaining multiple build versions of asp .net / SQL Server applications

We have new versions of .net applications deployed every week for testing given in form of new build files. Can any one suggest how I can maintain many versions of the builds without having to keep ...
0
votes
1answer
766 views

Select Query takes more than a minute to retrieve data

I have a table that has 99 columns, all of them are not needed but they were there when we started. There is a Primary Key (Objectuid) which is a GUI and has the Clustered Index on the PK. The Table ...
8
votes
1answer
1k views

MSG 666 when running an insert query on 80M-row indexed table

Strangely, my stored procedure started to receive Msg 666 for some input data. Stored procedure fails on the last step when it tries to insert a row into a table with the following structure: ...
1
vote
0answers
19 views

Minimum permission needed for GRANT CRUD ON

I have a certificate set up to do cross database CRUD operations. The certificate is linked to a user in each destination database. I sign any stored procedures in the source database with the ...
-2
votes
0answers
63 views

Replace string between single quotes

How to replace text between quotes in SQL? I am retrieving the top 50000 expensive queries from the database. In the query output I am seeing the same queries with different parameters populated. ...
4
votes
1answer
2k views

SQL Server: update trigger fires before insert trigger

I have an application with a SQL Server-hosted database. I do not have access to the application code, but I do have full access to the database. I have added my own custom auditing to a table to ...
0
votes
1answer
50 views

SQL Server : View names

I'm coming from an Oracle background and came across code to create a SQL Server view which I've never seen before. We have a view called viewA (for example) Within the script to generate the view I ...