Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.

learn more… | top users | synonyms

1
vote
1answer
269 views

Joining multiple tables results in duplicate rows

I am running SQL 2005. I am getting more rows then I expect returned by my query. I believe it has something to do with my join statements. Here is my query: There are multiple tables that have ...
2
votes
1answer
205 views

Arithmetic overflow error varchar to numeric

I'm getting an arithmetic overflow error and I can't seem to find out the case. I've recently had to adjust some stored procedures, to allow for dates in the future to be put in. The following part ...
-2
votes
2answers
227 views

SQL Server failed stored procedures log [closed]

How to find errors that was occurred in stored procedures in some timespan in Sql server management studio 2008 ?
0
votes
4answers
54 views

The multi-part identifier > “a.surveyid” could not be bound [closed]

I am getting the error : Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "a.surveyid" could not be bound. Below is the query ,what could be the issue? SELECT a.surveyid,* ...
3
votes
1answer
108 views

Decode and aggregate a BitMask in t-sql

I have a table that contains a bitmask field that stores permissions, where each bit denotes whether a particular permission is granted or not. Here's a simplified example: DECLARE @T TABLE (id ...
1
vote
0answers
56 views

SQL Server - Project based on the TDS protocol [closed]

From what I understand, TDS is an application level request/response protocol used for communication between a client and SQL server. Now, I'm in the process of finding a master thesis (computer ...
1
vote
1answer
68 views

DBCC DBREINDEX in MS SQL Server 2008

We have done DBCC DBREINDEX for our production database which size is about 300 gb, it was running for 12+ hours, but i found that DBCC DBREINDEX is obsolete and recommended approach is using ALTER ...
0
votes
1answer
59 views

Some records are missing while selecting from table

I am trying to match the records having MAX(WORKITEM_ID) but some PROCESS_INSTANCE_ID are not coming though they are present in WORKITEM table. select WI.WORKITEM_ID as WORKITEMID, ...
1
vote
0answers
103 views

How to speed up for huge tables in SQL select query?

I have the following huge tables. Table_1 with 500000 (0.5m) rows Table_8 with 20000000 (20m) rows Table_13 with 4000000 (4m) rows Table_6 with 500000 (0.5m) rows Table_15 with 200000 (0.2m) rows ...
3
votes
1answer
95 views

Select where bit is not 0

I have made the following query to select a record that isn't 0. SELECT * FROM Table WHERE Bit != 1 OR Bit IS NULL; Why is the check Bit != 1 not enough for a nullable BIT?
1
vote
2answers
66 views

How can I Insert when not in table and when max timestamp T-SQL

I am trying to insert rows from one table to another that are not in the one I am moving them to. I also want to only move the ones that have the highest datestamp. (I want to only insert rows that ...
0
votes
1answer
57 views

Which columns (and order) to choose for a clustered index to maximize query performance

In our database we have a table that looks more or less like the example below. In the past we always created the clustered index on the ID column on the table. CREATE TABLE [Measurement] ( [ID] ...
0
votes
1answer
105 views

How to modify maintenance plan using T-SQL script in SQL Server 2008

How can I modify a maintenance plan in SQL Server 2008 using a T-SQL script? I want to change the schedule of a maintenance plan and change the path of the .bak file . All these changes should be ...
0
votes
1answer
36 views

Script works but Im not sure this is the best format

I have a survey table which contains mostly integer. I want to make it readable for end user so I use select statement as below: SELECT CASE s.Country WHEN 1 THEN 'One' WHEN 2 THEN 'Two' ...
0
votes
1answer
62 views

How to store a version sequence per product

I need to create a table like the following one: +----+---------+---------+ | Id | Product | Version | +----+---------+---------+ | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 2 ...
1
vote
0answers
41 views

Certification Test 70-461 [closed]

I am a student at University of Houston-Downtown and looking to take the Database Administrator certification test sometime in October. I am hoping that someone has been recently been certified or ...
2
votes
0answers
41 views

Code required to make column not nullable

I have a table where I mistakenly allowed NULL values in a column: CREATE TABLE EXAMPLE ( EXAMPLE_ID INT IDENTITY(1, 1) NOT NULL, FOO_ID INT, CREATED_ON DATETIME DEFAULT ...
-1
votes
1answer
133 views

How to merge same values in a column and rows

How can i merge same Stock item value and get the sum of both value in the table This is Query: select Invoice_TBL.InvoiceNo ,Invoice_TBL.ClientNo ,Invoice_TBL.ClientFullName ...
0
votes
0answers
16 views

How to Customized the Where Statement based on UDT

Goal: Display customized data based on customized where statement from UDT. The UDT is a parameter inside of a stored procedure. Problem: A parameter from a stored procedure is @communication ...
5
votes
0answers
105 views

SQL Server - Best practices on debugging extremely complex stored procedures [closed]

I recently inherited a MESS of a search. It's around 10,000 lines of code in the procs/functions alone. This search is aptly named the "Standard Search." It's a proc that calls about 6 other procs, ...
1
vote
2answers
119 views

How to pull number code from strings buried in parentheses?

I have string data (ProductDescription) that includes product names and ID numbers and need to pull the ID numbers for cross-referencing... Data Samples: Widget (1234) Different Widget (123) Many ...
-2
votes
1answer
42 views

Usage of COALESCE? [closed]

Today: table abc a b c d ---- ---- ---- ---- 1 null 2 3 null null null 7 null 5 null 4 1 = e 7 = b 5 = f 3 = i 2 = w 4 = o The Goal is to retrieve this ...
1
vote
3answers
99 views

Can you Schedule a job inside another job?

I have many jobs that need to run but they cant run at the same time. the first step of each checks to see if any others are running if yes it waits a little bit and checks again. This can lead to a ...
9
votes
1answer
990 views

Why 0 is equal to empty string?

I need some help in finding why the following T-SQL statement returns 1 (true): SELECT IIF( 0 = '', 1, 0) I guess someone has change an ANSI option like SET ANSI_NULLS or something else that is ...
1
vote
3answers
76 views

Load specific element from XML

I have the following xml: <root> <123 type="line"> <aa type="item"> <value>1</value> </aa> </123> <234 type="line"> <bb type="item"> ...
0
votes
1answer
34 views

How to serialize on SQL server metadata?

I tried to find an answer in Kalen Delaney's SQL Server Concurrency but there is no mention of metadata. The problem is how to ensure that only one of these reads and index creates executes from one ...
3
votes
1answer
184 views

How to union four different tables

I have two tables whereby both of them use sub queries. These two tables are combined by using UNION ALL. I have two other tables that only uses a WHERE clause and both of these are combined using ...
2
votes
1answer
102 views

When using DBCC INPUTBUFFER to find query associated with spid, is there a way to show variable values of that query?

I have been troubleshooting an issue within a database that has some serious blocking issues. I found the command DBCC INPUTBUFFER a while back that allows me to see the query run for that spid. I ...
0
votes
0answers
25 views

Mass update taking to long [duplicate]

After searching through many posts I have come up with the code below to handle updating 200,000 to 2 million records daily. The problem is its still takes hours to complete, I have tried to narrow ...
0
votes
1answer
57 views

Stored Procedure Resultset Row Count and Resultset Size in Bytes/KBs

Is there any way to get total row count and size of result-set generated by a stored procedure execution? @@ROWCOUNT is a good option if its already added in each stored procedure but I just want ...
0
votes
0answers
42 views

Deep cloning a hierarchical database structure

I am trying to clone a hierarchical structure in my database, and I almost have it working; however I can't seem to get the output clause to access a column from a select statement. This is generally ...
11
votes
2answers
539 views

Help with PIVOT query

I have a table with below structure : CREATE TABLE [dbo].[AUDIT_SCHEMA_VERSION]( [SCHEMA_VER_MAJOR] [int] NOT NULL, [SCHEMA_VER_MINOR] [int] NOT NULL, [SCHEMA_VER_SUB] [int] NOT NULL, ...
3
votes
1answer
287 views

Delete using a composite key

I am trying to wrap my head around creating a delete statement for a table with a composite key. I would like to create something like: DELETE FROM table_example1 WHERE COLUMN1, COLUMN2 ...
3
votes
1answer
69 views

xp_cmdshell in a transaction

I need to run multiple scripts in a certain folder in a transaction and rollback if there are any errors. At this point I'm just trying to get the rollback to work without a try catch. Currently when ...
0
votes
1answer
62 views

Replacing a Cursor with a single Query possible?

I have this challenge where I'm trying to figure out if I can write something in a single query rather than using a cursor. The problem is I need to get items from a batch table based on a list of ...
1
vote
1answer
56 views

Having issues with a CTE in a Nested Join [closed]

I'm unfortunately dealing with someone else's code here, but needing a little push. What I have happening is that I have a CTE that is needed to run in a nested join. ...Start of CTE INNER JOIN ( ...
0
votes
0answers
38 views

Can a critical section cause deadlock in SQL Server? [duplicate]

We get the next ID for our tables from a separate table with two columns, TABLE_NAME and NEXT_ID. We have a stored procedure which reads the NEXT_ID and then adds an increment to the NEXT_ID. The work ...
2
votes
2answers
102 views

String that contains letters representing a negative number

I would like for every string that has a letter on the end make it a negative value. <field> = '00000000014545p';--Varchar(15) <field> = '00000000012645x';--Varchar(15) <field> = ...
0
votes
1answer
189 views

Fastest way to disable indices and drop constraints and rebuild SQL Server table

Below are some T-SQL commands that we're running on a table with a few billion records. Most of the database's size is taken up by 5 tables like this. What is the quickest way to perform these steps ...
4
votes
1answer
44 views

Does NTILE work over floating point numbers?

I am trying to use NTILE on my data using a float data type in my PARTITION BY clause. Please excuse the contrived example, but I think the best way to illustrate my question and confusion is through ...
0
votes
1answer
111 views

How to know that , Is it Maintenance Cleanup Task is working properly from maintenance Plan or not through T-SQL?

I would like to say that i had configured "Automatic DB Backup" as well as "Automatic DB Cleanup Task" through "Maintenance Plan" in MS SQL Server 2012 on my Production Database. And that is working ...
1
vote
1answer
70 views

How to Locate Specific Word

Goal: Locate specific word among tables in a database and display the name of the table and column in a list. Problem: There are TOO many tables to locate a specific word in the database. Info: ...
0
votes
1answer
59 views

Using of OPTIMIZE FOR hints in a sql query

as I know, OPTIMIZE FOR hint helps to avoid parameter sniffing. For such a stored procedure: SELECT * FROM Tabele WHERE param=@param1 We have two parameter S1 with high selectivity and s2 with low ...
1
vote
4answers
300 views

T-SQL insert value based on identity column

Is it possible to have a table with PK based on Identity columns? Lets say I have a table of customers, two fields are important for our case Id which is PK nvarchar(20) and SequenceId which is int ...
0
votes
2answers
45 views

why the optimizer doesn't choose the optimal plan [closed]

I know, if the statistic is not updatad, it is possible, that the optimizer doesn't choose the optimal execution plan? Does any other reason/reasons exist?
2
votes
2answers
255 views

Half of a transaction completes, when the other half fails?

I have done some searching on SO, MSDN, and various other sources, and I found plenty of questions about transactions, but none that seem to be exactly what I'm dealing with. Admittedly I'm still ...
4
votes
1answer
123 views

SQL Server : parsing with XPath Performance

I have a UI that allows a user to select certain data points they want to filter the results by. There are things such as location, department etc. Once they have selected all of the options, I ...
10
votes
2answers
687 views

Select all records, join with table A if join exists, table B if not

So here's my scenario: I'm working on Localization for a project of mine, and typically I would go about doing this in the C# code, however I want to do this in SQL a bit more since I am trying to ...
2
votes
1answer
40 views

How to generate an error when backing up to an existing file

I'm using SQL Server's BACKUP command and I want the operation to generate an error if I attempt to write to an existing backup file. By default, SQL Server seems to append to an existing file. ...
3
votes
2answers
243 views

How do I pass multiple statements into sqlcmd?

Wanting to automate some tasks instead of firing up SSMS for everything, I'm trying to write a batch file to run these statements, which work perfectly when I paste them into the sqlcmd prompt. They ...