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
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 ...
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
366 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 ...
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: 'ô' ). ...
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 ...
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 ...
3
votes
3answers
76 views

Most recent order from multi join query

Here's the CTE version of the query, but it is returning multiple rows for the same customer. Not just the row containing the most recent open order date: with LatestOPenOrderDate_CTE AS ( Select ...
5
votes
2answers
200 views

'+' operator with one operand!

How does '+' operator behave in following statement? select + 'taco'; --Result is 'taco' Is it doing string concatenation with first string blank ('' + 'taco'), or does it mean something else?
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] ...
0
votes
1answer
76 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 'db_owner',...
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 ...
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
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....
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....
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 ...
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 ...
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 ...
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 ...
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) ...
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 ...
4
votes
2answers
149 views

DayOfYear as per Indian fiscal year

I want to get a sequence number according to the Indian fiscal year. I tried to write a function using SQL built-in function: DATEPART(DAYOFYEAR,'2015-04-01') but I am facing a challenge to get day ...
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 ...
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
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. ...
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: ...
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 ...
4
votes
4answers
188 views

Validate three digits before decimal and after decimal point six digits

I have columns called longitude and latitude with nvarchar data type. The user can enter something like (23.23424432),(432423.343), which should not be allowed. Can anyone help me to validate this in ...
0
votes
2answers
272 views

How do I extract a repeating string pattern from multiple rows?

I have the following data: | ID | BadCol | +------+-----------------------------------------------+ | 1 | All of R489804 R489805 R489806 R489807 ...
1
vote
0answers
34 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
74 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
52 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
43 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 ...
3
votes
1answer
48 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 [DATABASE].[SCHEMA]...
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. ...
0
votes
1answer
110 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 ...
3
votes
1answer
103 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 ...
1
vote
1answer
73 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 ...
1
vote
0answers
31 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 ...
2
votes
1answer
56 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
1answer
517 views

Automate SQL Server restore from Prod to Dev

I would like to setup an automated restore job which takes the latest timestamp .bak file and restores it into the DEV instance. I currently have a job on the DEV box that uses the UNC path to the ...
1
vote
4answers
160 views

Script to create multiple databases

I need to create 90 databases. Something like this. Do you have any idea? My script: declare @db_name nvarchar(100) set @db_name = 'name'; CREATE DATABASE @db_name CONTAINMENT = NONE ON PRIMARY ...
0
votes
3answers
125 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 ...
3
votes
1answer
37 views

Display Data Using Pivot

Is a pivot the best way for me to display this data? I want to show id, uuid on the left then show each event1 across the top with a 1 if exists or a 0 if it does not. So look like this id uuid ...
3
votes
2answers
92 views

Return values from Left Table Only Once

This is my table structure and a sample query. However, my query returns these results: ID event1 Exists 1 cycle No 1 cycle Yes 2 run Yes 3 walk No As you can see ID 1 is ...
0
votes
0answers
49 views

Issues with the @query parameter in sp_send_dbmail

When I use the following: select * from table A where repID in ( select repID from table B where id = 5 ) as the string I'm assigning to the @Query parameter. ...
0
votes
1answer
27 views

Alter Time of DateTime Field

I am needing to alter the time piece of a datetime field. I tried to use this syntax, but it gives me an error of Incorrect syntax near the keyword as What syntax can I use to alter the end ...