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

learn more… | top users | synonyms (1)

1
vote
2answers
51 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
42 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
128 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
464 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
77 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
59 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
61 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
47 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 ...
16
votes
4answers
565 views

SARGable WHERE clause for two date columns

I have what is, to me, an interesting question on SARGability. In this case, it's about using a predicate on the difference between two date columns. Here's the setup! USE [tempdb] SET NOCOUNT ON ...
1
vote
1answer
31 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
39 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
54 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
0answers
77 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
37 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
105 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
50 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
31 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
46 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
27 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 ...
1
vote
3answers
76 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
74 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 ...
0
votes
2answers
70 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
47 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
18 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
63 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
47 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
47 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
40 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
71 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 ...
1
vote
1answer
42 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
93 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. ...
5
votes
1answer
43 views

Database stored procedure with a “preview mode”

A fairly common pattern in the database application I work with is a need to create a stored procedure for a report or utility that has a "preview mode". When such a procedure does updates, this ...
1
vote
0answers
28 views

What's the deal with transactions in a stored procedure called by an SSRS report?

Consider the following: sp_a contains an sp_executesql call to exec sp_b sp_b begins and commits a transaction, then immediately logs @@TRANCOUNT. When I call sp_a manually from within SSMS, the ...
1
vote
0answers
34 views

Greatest N per Group in SQL Server [duplicate]

I want to find classical Greatest N per Group. I have two methods to solve this problem Dense_rank Over() method Min Over() method Both work flawlessly. Now I want to find which one is better ...
-1
votes
1answer
70 views

Impact of WHERE Clause on Table-Valued Function and query performance

I have taken over a legacy application that has had many performance issues. One I found is on a table valued function. The TVF runs fine as a basic SELECT. However, it is incredibly slow when it has ...
0
votes
2answers
49 views

Inline SQL To Set Fully Qualified Name [closed]

Why does my variable @FQN not set accordingly? Is this a problem with my syntax? A Problem with using a cursor? What should I do so that my variable @FQN sets appropriately? This is what my print ...
0
votes
2answers
41 views

Restore without specifying location for each file

I am working with a database that has a dynamic filegroup structure. The number of filegroups and filegroup files can and do change. Is there a way to specify one data and one log folder in a script ...
0
votes
1answer
64 views

Create New Database user to login to SQL Server Management Studio

Please see the T-SQL below: create database db; go use [db] create login [standard] with password ='dkdkdkqajHGgbh56' CREATE USER [standard] FROM LOGIN [standard]; exec sp_addrolemember ...
3
votes
1answer
46 views

SQL Server 2012 Client Statistics Incorrect

When I run my query, I get the following Client Statistics Results :- Number of SELECT statements = 2 The query being ran is only 1 SELECT statement :- SELECT TOP 1000000 * FROM ...
0
votes
1answer
43 views

Get Sale Info By Employee

I need a way to get detailed sale information for each employee in the StoreInfo table. Each employeename in that table has it's own table with data structure mirroring the table information below. ...
1
vote
1answer
56 views

column alias in dynamic pivot [duplicate]

I need help with a dynamic pivot query. I would like to modify the column name (alias) but I can't get it done in the pivot. Please help. The query itself works perfectly but the username contains the ...
0
votes
1answer
79 views

Delete Works in one Stored Procedure but not another

I've built an app that uses stored procedures to insert into, update, and delete rows from tables in my database. All domain users have been given execute permissions on these procedures, all ...
1
vote
0answers
28 views

How to properly re-run a deadlocked trasaction in TSQL

In TSQL (SQL Server 2012), is this a proper way of re-running a transaction that was killed due to deadlocked - it was chosen as the deadlock victim. EX: "Transaction (Process ID 118) was deadlocked ...
3
votes
1answer
88 views

How to compare current record with previous records

Is there a way to group by and compare current record to previous records when reading from a table. My table looks something like below. ID PASSED LEVEL CREATEDATE 100 1 2 ...
3
votes
0answers
43 views

How do I load data from a single table to multiple tables & keep referential integrity

Using SQL Server 2008R2. I have a single table with a bunch of columns. I have built a new database with multiple tables to copy the data too. How do I copy the data, and still keep the ...
2
votes
1answer
51 views

Identify When Dates Returned Are 1st, 2nd Or 3rd Date in Table

For the 1st 3 orders, a credit is given, so I am needing a way to flag the 1st 3 dates for a customer. My issue that I am having with my query is it flags the first 3 dates returned regardless of if ...
0
votes
3answers
70 views

how to convert this varchar to datetime format?

I have a varchar column has data like (2015-12-02 20:40:37.8130000) which i have imported from csv file to SQL table. i need to convert this column to datatime as (2013-03-17 14:15:49.687). ALTER ...