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

learn more… | top users | synonyms (1)

5
votes
2answers
428 views

Custom ID Numbering - fast way on insert?

As start: this is for an ETL stored procedure that is serialized, so paralellism is not of concern. I need to assign custom ID numbers for markers during loads. I can not use an identity field, ...
0
votes
1answer
38 views

TSQL LDAP query via Linked Server

I have strange problem, I have 3 SQL Server 2012 with Sp1 instances, and on all of them, the versions are identical. I am trying to query Active Directory services, on 2 of them this is working ...
2
votes
2answers
236 views

Incorrect syntax generating XML

I am trying to run below code in SQL server 2000 SP4 but getting error. Declare @Body varchar(8000); Declare @TableHead varchar(8000); Declare @TableTail varchar(8000); Set NoCount On; Set @...
-5
votes
1answer
56 views

can anyone please tell me what's wrong in this query [closed]

update ch1380.ComplexMiscLogic set logic = 'UNIDDisposition IN ('1','2','5')' where misclogic = 11 An error is showing incorrect syntax on line one of the above update statement. Please can ...
3
votes
2answers
108 views

Transaction in a stored procedure

I need to perform an UPDATE and an INSERT in a single transaction. That code works fine on its own, but I'd like to be able to call it easily and pass in the required parameters. When I try to nest ...
1
vote
1answer
41 views

Return Number Not String In Dynamic SQL

This is my SQL syntax and (this is only a snippet unfortunately I am stuck running dynamic SQL in this instance) - but my issue is at the very end of my statement when I do the Select @numreturned ...
1
vote
2answers
35 views

Excluding Null Values

In my syntax why does the NOT IN statement not exclude NULL values? To me, if NULL does not exist in #GoodAnswers it should return the dv Below is syntax, can someone help explain this to me? ...
2
votes
2answers
129 views

Using DateTime Variable In Where Clause

I am trying to pull all data where a sale date is >= to a certain datetime. When I execute my query I get an error of: Msg 241, Level 16, State 1, Line 15 Conversion failed when converting date ...
2
votes
2answers
38 views

Trying to drop database only if it does not exist by using the db_id function fails

When the database foo is not yet created and I I run this IF db_id(N'foo') IS NOT NULL BEGIN ALTER DATABASE [foo] SET OFFLINE GO DROP DATABASE [foo] GO END I get a message Cannot drop ...
0
votes
1answer
22 views

Accepted Values Stored in Variable

I am trying to write a re-usable query and this is pseudo-code of what I am after. Is this possible to do in Sql Server 2008 or do I just need to write a different stored procedure for each ...
2
votes
2answers
92 views

DBCC CHECKDB LastKnownGood

I created the following query to list the DBCC CHECKDB LastKnownGood timestamp for all Databases on a Server. Is there a better way to do this? IF OBJECT_ID(N'tempdb..#Results') IS NOT NULL BEGIN ...
1
vote
2answers
363 views

Re-Usable Query

I have 3 tables with the exact same DDL (I know that is a horrid set-up), but each one has a different values for a field. I am hoping to be able to have a way to write ONE query for all 3 selects as ...
0
votes
1answer
29 views

Returning only the row with maximum value from related rows

In the example below how do I get the rows where the swap id is the maximum value in relation to the reservationId? I am currently joining multiple tables to get customer, reservation, room etc ...
1
vote
2answers
106 views

SQL Query Optimization

My SQL Server version is Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53 and the query below just runs without producing an error or updating. The table [dbo].[...
1
vote
2answers
102 views

Must Declare Scalar Variable Error

My syntax keeps giving me the below error, which is blowing my mind as I think (and please kindly correct me if I am incorrect), I have declared and set this variable above. Msg 137, Level 15, ...
1
vote
2answers
30 views

Getting the error log location in an efficient way

I am using this sql to get my error log location: EXEC xp_readerrorlog 0, 1, N'Logging SQL Server messages in file' However, sometimes I have very large log file (about 1gb, and I do not have ...
1
vote
1answer
21 views

How does SQL Server determine the expression type when mixing Unicode and non-Unicode strings?

When you say a + b in T-SQL and a and b are of a string type (e.g. varchar(...) or nvarchar(...)): How is the resulting type determined? I found no clear rules for this when experimenting. It seems ...
1
vote
2answers
70 views

Why does formatting a time with AM/PM specifier return null?

I have a query where a formatted expression is returning null: declare @FormatString nvarchar(max) = N'hh:mm tt' select M_STARTTIME as [Memo Time 1] , convert(time,M_STARTTIME) as [Memo Time 2] ...
2
votes
2answers
50 views

Show Query Results Vertical

I am wanting to show these results vertical as opposed to the default horizontal. My desired output is like this Passed w/ notes 26 Passed w/o notes 28 Passed w/ notes 52 ...
4
votes
1answer
133 views

Query All Databases Listed In Table

I have been using a cursor to get my results, but it is taking an extended period of time to return my result set. I am hoping to find a way to QUICKLY get the results returned (if at all possible). ...
7
votes
4answers
470 views

How to get response from stored procedure before it finishes?

I need to return partial result (as simple select) from a Stored procedure before it is finished. Is it possible to do that? If yes, how to do that? If not, any workaround? EDIT: I have several ...
4
votes
1answer
128 views

How to use COLUMNS_UPDATED to check if any of certain columns are updated?

I have table with 42 columns and a trigger which should do some stuff when 38 of these columns are updated. So, I need to skip the logic if the rest 4 columns are changed. I can use UPDATE() function ...
0
votes
2answers
91 views

How to insert numerous data into temporary table?

I'm building a program which generates a T-SQL query in following form: DECLARE @In TABLE (Col CHAR(20)) INSERT INTO @In VALUES value1, value2... value1000 GO INSERT INTO @In VALUES value1001, ...
1
vote
0answers
25 views

Compare Strings After Character

I need to check email addresses against an internal known bad domain database. My internal database has domains in the form of myfreepaysite.com just no @ sign. I have been using this syntax to ...
4
votes
1answer
365 views

Insert results from a stored procedure into a table variable

I have a stored procedure that stores values in a table variable. I select these values and return them when the procedure is called. I am trying to set these return values in another table variable ...
5
votes
1answer
57 views

T-SQL (SQL Server 2012) Update based on lookup table

I have the following tables related to this question: CREATE TABLE tblTitan( titanId int IDENTITY (15483,1) PRIMARY KEY, userId int UNIQUE NOT NULL, titanName varchar(15) NOT NULL, titanExperience ...
1
vote
1answer
54 views

Get rows having different values for a column based on the duplicate values of combination of other 3 columns

I want to get only rows having a different values in a column(column name DEF) based on the duplicate rows having unique combination of other 3 columns. Example: In the below example first two rows ...
0
votes
0answers
40 views

Search All Views On A Database

We have about 800 views on a SQL Server 2000 database, about once a week I run a procedure to give me information so I can see which stores have LIKE '%Camo%' in there inventory so I know who to send ...
1
vote
1answer
70 views

TSQL Script Execution Through Power shell, Special Characters Rendering Problem

We have a simple T-SQL script file (.sql), which we execute through Powershell. Everything works fine, except the script portion, where we are using some special characters as string (example: 'ô' ). ...
3
votes
1answer
125 views

Export/import data from Excel into a table with queries

I'm a newbie interested in learning and implementing things I learn. I've been working on SQL Server 2014 these days and I'm loving it. I've come across some scenario's where I would have an Excel ...
1
vote
1answer
39 views

Select entries based on a combination of columns

I have a table with a definition like this: CREATE TABLE [dbo].[Contact] ( ClientNumber [numeric] (20, 0) NULL, ContactDate [Date] NULL, Name [text] NULL, AdditionalInformation [text] ...
2
votes
2answers
106 views

SQL Server changes execution plan

I have SQL code that is executing slow DECLARE @ccode varchar(500) SET @ccode = '%NIP%' SELECT [CVN_UID] FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS] WHERE DESCRIPTOR_TEXT LIKE @ccode Why is SQL ...
4
votes
2answers
55 views

Query to get queue position for each group

I have a queue table with the following sample data: id company location 1 acme new york 2 acme philadelphia 3 genco st.louis 4 genco san diego 5 genco san francisco 6 acme ...
1
vote
1answer
32 views

SQL Server - Analyzing Multiple Stored Procedure Statistics

I'm trying to analyze 1000+ stored procedures and I need to find out the: Elapsed Time in Sec (AVG based on # of iterations) CPU (AVG based on # of iterations) Logical Reads At first I'm using SQL ...
0
votes
1answer
65 views

Extract Date Portion Only From DateTime Field

In SQL Server 2008 I use this to take out only the date piece Cast(field as date). For example, if I use the below garbage DDL Create Table #Bbb ( id int ,datetime1 datetime ) ...
0
votes
1answer
34 views

What is the request_id in sys.dm_exec_requests and how does it change?

According to MSDN, the request_id in sys.dm_exec_requests is the "ID of the request. Unique in the context of the session. Is not nullable." It always appears to be 0, from casual observation....
1
vote
3answers
80 views

Is having Estimated number of Rows > Actual Number of Rows an issue to worry about?

I've a table [order] where a simple select shows cardinal difference between Estimated Number of Rows and Actual Number of Rows and in a strange way. Generally Estimated Number of Rows skews as ...
0
votes
1answer
124 views

AlwaysON Availability group query very slow

I am running below tsql to find the secondary AlwaysON servers from the primary replica : set transaction isolation level read uncommitted set statistics time, io on SELECT ISNULL(agstates....
0
votes
2answers
74 views

SUM() In SQL Server

Running SQL Server 2008 I can not wrap my mind around why these two queries, which are identical except for the first one has COALESCE() wrapped around the entire statement and the second one has ...
1
vote
1answer
63 views

SSIS push and pull performance

After reading Which one is more efficient: select from linked server or insert into linked server? I wonder if this is true also for SSIS? For example, I have 2 SQL Servers (A and B) and SSIS ...
0
votes
1answer
22 views

Scripting execute permissions on stored procedure

My goal is to write a script which: Creates a stored procedure Grants execution permissions to a particular user on that new stored procedure On this SO question the recommendation is to add GRANT ...
1
vote
1answer
69 views

Trigger Not Updating

Do I have an issue with my syntax? I created this trigger as sometimes the sales personnel forget to input the sale date (I Know it should be automatically captured which is an update in progress) ...
1
vote
1answer
73 views

SQL Data in form of Date+Time bulk insert into one column

Background of problem: To insert 2 seperated fields of data (from a big flat file document of data) with bulk insert. Columns in question that i want to get data from is in its current form in date ...
3
votes
1answer
281 views

Adding Where Clause Skews Data

This is the result set that I get returned using my query: empname itemssold JJ 14 But this is my desired result set: empname itemssold AA 0 BB 0 CC 0 DD 0 JJ 14 RR ...
0
votes
1answer
53 views

What is the meaning of & in where clause

What does & indicate in below query? What is the meaning of it? select id, name from test where num & 1 = 1;
1
vote
1answer
48 views

SQL Stored Procedure with one parameter and several variables that will change over time

I am attempting to build a stored procedure with variables that will change over time. on SP execution (Right clicking SP in programmability under the database and execute) results in Error output: ...
2
votes
2answers
133 views

Adding a new primary key ID column to an existing table

I want to add a new column to a table. The table is already populated with data. I can do this by making a complete copy of a table, inlcuding the contents, and add a Primary Key ID column to it. I ...
1
vote
2answers
34 views

How to return all the unique column values on a per column basis for all metadata tables?

assume multiple tables of various sizes table one col one, col two, col three,... a,d,j a,e,k a,e,m b,e,o c,e,o d,e,o f,e,o return (preferably in this format) col one, col two, col three a,d,j b,...
1
vote
1answer
47 views

Find if column exist in certain tables

I want to find out all tables having a column let's say test. How to obtain this? I need DBName, TableName, testcolumnexist(Yes/No), TableCreateDate and ProductName details. The tricky part is ...
1
vote
2answers
105 views

Can I create a new user in MS SQL from windows and provide the SID

I am trying migrate databases and along with the database I am trying to also migrate the users without having to re-map the users. I was trying to recreate the windows user with the old server SID. ...