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

learn more… | top users | synonyms (2)

5
votes
3answers
204 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 sample DDL Create Table #...
0
votes
0answers
23 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 ...
2
votes
0answers
24 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 ...
1
vote
0answers
31 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 ...
1
vote
0answers
23 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 ...
0
votes
2answers
22 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 ...
7
votes
0answers
149 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 ...
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
vote
1answer
37 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
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(...
-4
votes
1answer
42 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
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 ...
-1
votes
0answers
45 views

Sum of total hours in sql server [closed]

8:17:3 7:10:33 7:54:20 8:6:35 6:5:55 9:40:58 8:12:5 ...
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 ...
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 ...
2
votes
1answer
230 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 ...
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 ...
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 ...
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, ...
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 ...
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 ...
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 ...
4
votes
2answers
332 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...... ...
0
votes
1answer
59 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
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 ...
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 ...
0
votes
0answers
29 views

Export Stored Procedure Results To CSV

I have seen many options for doing such, however most of them involve using bcp or using exec master..xp_cmdshell which I am unable to use on my server (security restrictions). I was looking at ...
-2
votes
0answers
64 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. ...
0
votes
1answer
20 views

Howto transfer tracefile into a trace table without loosing the sort order?

I have traced some data into a trace table into Sql Server using the Profiler GUI. This table has the following structure: It contains the SQL statements as well as execution plans in the TEXTDATA ...
1
vote
1answer
26 views

Combining multiple-row data into a single row

I have a table that has columns like following ID Number Value1 User1 Value2 User2 Value3 User3 -- ------ ------ ----- ------ ----- ------ ------ 1 123 0 Jack 1 123 ...
1
vote
0answers
29 views

Re integrate MSSQL 2008 to new Active Directory

I have a server which was joined to an AD Domain, then installed with MSSQL Server 2008 R2 and System Center Operations Manager 2012's Reporting Server on it. The same server had the Active Directory ...
0
votes
1answer
30 views

Upgrading SQL Server 2008 failover cluster node to SQL Server 2012 failover cluster node [closed]

Please I want someone to help me with step by step method of upgrading SQL Server 2008 with failover cluster node on two different machines to SQL Server 2012 without losing the existing settings/...
1
vote
2answers
53 views

Query to extract all SQL database roles with INSERT and/or UPDATE privileges

I've taken over the support for a SQL 2008 database and there are a multitude of custom roles (25+) associated within. I'm trying to figure out which of the custom roles have the ability to perform ...
1
vote
2answers
45 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 ...
3
votes
1answer
65 views

Delete User View named the same as system view

I am exporting a database to Azure and have found a user view created in a previous version of MSSQL (Currently running 2008) that is name INFORMATION_SCHEMA.TABLE_CONSTRAINTS which obviously should ...
0
votes
2answers
32 views

text positioning for select list

I have following issue, I have hierarchy table where every record has level, code and description and I need to have description to look as it is was one column. Example code CREATE TABLE #tmp (...
1
vote
2answers
47 views

User cannot select from table but their permission appear to show that they should be able to

I attempted to revoke delete permissions to most users in a database by using the deny and revoke sql commands and checking the deny box for delete when adding the table as a securable in the UI. ...
2
votes
3answers
86 views

Join between different frequency tables

Assume that I have two tables, DailyTable and QuarterlyTable, where each have 3 columns: ID, Date, and Value. As the names indicate, the frequency of data stored in DailyTable is daily, while the ...
0
votes
2answers
47 views

Create a copy of a SQL Server Job

Using T-SQL or Powershell, how can I create a stored procedure that performs the following: Copy a SQL Server Agent Job to another backup Job on the same server (with a new name) Disable the backup ...
0
votes
0answers
32 views

Bypass or Override SQL Server DB related error during WSUS Setup Installation?

When trying to install the latest WSUS release from here: Windows Server Update Services 3.0 SP2 We tried it once with the DB and then someone suggested detaching the DB and moving to another drive ...
7
votes
1answer
153 views

Two columns with and without index in where clause - How index works

I am executing a query on SQL Server 2008: SELECT col1 FROM table1 WHERE col2=val2 AND col3=val3 Here col2 has a non-clustered index, col1 is the PRIMARY KEY, and col3 does not have any index. ...
0
votes
0answers
31 views

Why are schema changes I've made reverting?

I have been experiencing issues when making schema changes/stored proc definition changes. The changes that I make are not always persistent. I can make a change today then a day or two later I'll ...
0
votes
1answer
46 views

Query distinct emails not logged in within a period of time

I need help in writing a query to return distinct emails that have not logged into ABC database in the past 5 months. The exact task I have been asked is as following: Create a table in ABC ...
0
votes
3answers
67 views

How to check if range between two numbers is available in table of ranges

I have following table of number ranges Create table Ranges { Id int identity(1,1) primary key, RangeFrom int not null, RangeTo int not null } Insert into Ranges(RangeFrom, RangeTo) VALUES (1,...
1
vote
1answer
57 views

Slow-running query returns incomplete resultset

So I'm trying to get some data from an SQL Server 2008 instance, the query is quite simple in structure but it has an extremely poor performance, it takes just under a minute to retrieve a bit over 1,...
3
votes
2answers
234 views

Why database snapshot creation is faster than normal database backups?

Could you tell me any reason why creation of database snapshot is faster than database normal database backups in MS SQL server
1
vote
1answer
32 views

sql getting sum of calculated columns

How can I get the sum of TotalAmount as single value for example Product Name UnitPrice Quantity Discount% Amount SharingCost% ShippingCost CustomsCost TotalAmount pro1 250....
4
votes
1answer
99 views

When executing a command via xp_cmdshell what user issues the command to the system?

I'm executing the following command to stop a service on a remote server from within a stored procedure in SQL Server. ---------------- -- Stop Service ---------------- SET @Command = 'sc \\' + @...
2
votes
1answer
36 views

msdb.dbo.sysjobs and last run date and time

I know that the last_run_date and last_run_time are stored as Int, but when coverted over to datetime (of which I have examples of), is the time GMT, or what? When converted it doesn't match the same ...
0
votes
0answers
15 views

Would snapshot replication avoid the “repl table shuffle” when making schema changes?

I support an application that sits upon a SQL 2008 database and has an admin tool that allows app admins to do some field customization. This issues an ALTER TABLE command when saved, and since the ...