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
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: ...
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
43 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
160 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
226 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
29 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
50 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 ...
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. ...
0
votes
1answer
80 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
89 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
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 ...
3
votes
0answers
44 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 ...
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 ...
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
1answer
463 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
84 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
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 ...
3
votes
1answer
35 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
81 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
47 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
26 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 ...
0
votes
1answer
68 views

Create Calculated Fields from Distinct Values in Second Table

I'm trying to create a report in Microsoft SQL Server Report Builder 3.0 that has a date field populated with dates for a specified year and alongside, calculated fields for each distinct error code ...
16
votes
1answer
2k 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 ...
2
votes
2answers
15k views

how to calculate difference of first row and last row fields value in each group

I have table with structure like this: +-------+------------------+ | Value | Date | +-------+------------------+ | 10 | 10/10/2010 10:00 | | 11 | 10/10/2010 10:15 | | 15 | ...
0
votes
2answers
51 views

TSQL Delete All dates and stores data

This might sound like a stupid question but I can't seem to find a better way. It's a posting system where stores send us info on a daily basis (can be for multiple days). That info is placed in ...
19
votes
6answers
3k views

Index performance on ON versus WHERE

I have two tables @T1 TABLE ( Id INT, Date DATETIME ) @T2 TABLE ( Id INT, Date DATETIME ) These tables have a non-clustered index on (Id, Date) And I join these tables SELECT * ...
1
vote
1answer
50 views

Concatenate data in a single row [duplicate]

I have the following table: TicketID Type Code 44899B82-41C9-4020-9C2B-007C6000D283 ARTI 22150012 44899B82-41C9-4020-9C2B-007C6000D283 ...
4
votes
2answers
167 views

How to optimize this tricky SQL self-join?

I want to perform a complex self-join on a table. I know that this could in theory be done very efficiently (see below), but I have trouble getting SQL (on Microsoft SQL Server) to do so. My question ...
8
votes
2answers
1k views

Is there a way to force Deferred Name Resolution even if the table exists when creating a sproc?

When creating a Stored Procedure in SQL Server you are allowed to refer to tables which do not exist. But, if the table does exist then any column you refer to in the procedure must exist in that ...
6
votes
3answers
354 views

The fastest way to get the most recent records [duplicate]

I am looking for the fastest way to return the most recent record for each reference number. I quite liked the solution from BrentOzar.com, but it does not seem to work when I add a third condition ...
0
votes
2answers
52 views

SQL With Multiple Condition and Column

I have 3 tables: Employee +----+--------------+--------------------+----------------+ | Id | EmployeeName | EmployeePermission | Position | ...
0
votes
1answer
24 views

Perform Sum In Update Statement [closed]

I am trying to run this update statement, but it keeps giving me the error of (3 row(s) affected) Msg 157, Level 15, State 1, Line 9 An aggregate may not appear in the set list of an UPDATE ...
2
votes
0answers
53 views

TSQL Generic Row number theoretical question

To start, We have a pretty complicated system that's been a compilation of over 20 years of code... And the code needs to work with SQL2005 all the way to SQL2014 (can't use the offset Fetch Next) ...
2
votes
1answer
25 views

Getting Dates In Range Into Table

I have a calendar table that shows all dates from 01/01/2015 to 12/31/2016, essentially the calendar shows the date and isworkday. I need to be able to pass a begindate and enddate to a stored ...
4
votes
4answers
594 views

Delete into new table (like select into)

The select into syntax will create a new table to insert data into (I know that types may not be consistent etc. but it gives you a rough copy). Is it possible for a delete output into statement to ...
0
votes
2answers
63 views

Create new instance MS SQL Server 2014

I have installed MS SQL Server 2014 , but after installation there is no instance created. There is only the default instance. How make a new instance in existing MS Sql Server ? I found a lot of ...
0
votes
1answer
49 views

Select Into removes IDENTITY property from target table

I am doing an exercise which removes the IDENTITY property from the target table when using a select into statement. source table name: Sales.Orders column name: orderid column property: int ...
1
vote
2answers
115 views

Transforming multiple rows into a single row

In my company, I need to join table A to table B: Table A is the sources table Table B is the empty table I want to know that how can I do it and the result like table B? Source table A: ID ...
0
votes
1answer
123 views

EXEC master.dbo.xp_cmdshell 'The user name or password is incorrect'

I am trying to move a file from a network drive to another server and when I execute this statement. I get an output of The user name or password is incorrect DECLARE @SourceFile AS ...
2
votes
0answers
60 views

What is the correct way to rename/swap a table in production

I have two tables, tblCode and tblCodeAlternative tblCode has about 4100 rows and is used in production. tblCodeAlternative has about 4200 rows and is in the production database but not used ...
3
votes
1answer
142 views

Aggregate data from multiple rows into single row

In my table I have rows, and each row has some data and its version. When column is not-null, it overrides an old value otherwise nothing happens. Pseudocode (in C#) var resultTable = new ...
1
vote
2answers
187 views

How to use SET operation in CASE clause in SQL?

I have the following structure, and I'm doing a SET operation in a CASE clause but it is producing an error: Incorrect syntax near SET Query: DECLARE @tbl_Result TABLE ( RowID INT ...
1
vote
1answer
50 views

Compare Timestamp Difference Between Consecutive Records

How do I compare Timestamp difference between 2 consecutive records? This is my example: DECLARE @x TABLE(ProductID INT, SingleRunFailure bit, SampleDate date); INSERT @x VALUES (101, 1, ...
1
vote
3answers
421 views

Using Echo statement writes Echo is ON in the text file

Following code is used for write a number in the text file but instead it is writing "Echo is ON" into the text file. SET @CMD = 'ECHO '+@Ver+'>'+@Vercheck Print @CMD EXEC xp_cmdshell ...
7
votes
1answer
752 views

What is the correct way to ensure unique entries in a temporal database design?

I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read this answer, but I'm afraid I ...
3
votes
1answer
91 views

Select data divided in groups evenly distributed by value

I would like to select into 4 groups the data from a table having the sum of a values in the groups as evenly distributed possible. I am sure that I am not explaining it clear enough so I will try to ...