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

learn more… | top users | synonyms

0
votes
1answer
39 views

Determining the specific command type in a DDL trigger

Given strings corresponding to DDL commands, how can I determine which DDL command (column addition, constraint addition etc.) it precisely is? I have a DDL trigger on a database that will be fired ...
2
votes
2answers
22 views

Error 4108: Windowed functions can only appear in the SELECT or ORDER BY clauses

The error in the title is produced when add the second condition in my where clause. I understand the error, have tried using subqueries as seen in other posts to fix this but no luck. Does anyone ...
4
votes
1answer
30 views

Calculating daily growth using previous row value in new column - SQL Server

I have the following query below that shows size of a database and when this data was collected. I am trying to create a new column that has "average daily growth" which would be determined by the ...
0
votes
1answer
21 views

SQL query to get output

I have two queries. First query: select files.originalfilename from files inner join digitalfiles on files.fileid = digitalfiles.fileid where digitalfiles.path in ( select * from ...
-1
votes
2answers
44 views

Where clause in the query [on hold]

I can group by Qty but not woitempart. I \tTried to use a where clause but in vain due to this exception: Exception: ERROR #50805215 Query failed to execute. GDS Exception. 335544569. Dynamic SQL ...
5
votes
2answers
62 views

Using Left or substring with wildcard in SQL

I am looking to retrieve information from a field, but am having troubles getting exactly what I want. The information I want has no set order and I am also looking for two different things. Here is ...
2
votes
2answers
203 views

Creating a stored procedure and adding date manually to filename

I am new to SQL administration and have been tasked with creating some nightly jobs that send an email with certain details contained within a spreadsheet and so far I have the following: EXEC ...
5
votes
2answers
80 views

Combining data from multiple rows

I am relatively new when working with queries so let me apologize in advance. This is a MS 2012 Server that I will be working with. This data has been brought into a staging table through a nightly ...
16
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 * ...
7
votes
1answer
93 views

sysschedules datatype choices

I am curious, why does SQL Server save the values in msdb.dbo.sysschedules for date and times as int instead of datetime. I assume the reason dates back to something in SQL Server 2000. Was it a ...
2
votes
2answers
834 views

Design best practices for last tracking with indexed view

My tables structure is below : TbDoc (ID int , ...) TbDocActions( ID Int, DocID Int, Date DateTime, col1 int, col2 int, ...) I want to have indexed view to get last TbDocActions columns for ...
3
votes
0answers
36 views

SQL DatabaseMail smtp error 5.7.57 at one site, email works at another with the same details

I have several SQL databases on different servers. I have used a script to set up Database mail. EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'Emailer' ,@description = 'Email out ...
0
votes
0answers
23 views

Can I use Data Quality Services for checking data integrity?

As Data Quality Services documentation seems too broad, could anyone tell me if the DQS is suitable for solving such issue: find if each row in one table has corresponding row in second table and if ...
2
votes
1answer
19 views

Selecting closest, non expired value [duplicate]

I'm trying to select multiple rows where the value is closest to the end date but not passed, and return only those rows. Here's a sample of some data: School Code | School Year | Semester | Term | ...
1
vote
1answer
21 views

Sql parent Child Query not working

I want to get parent and their child data which means, parents first, then children. Below is the query that I tried to convert to parent and their children SELECT a.FactorTitle, a.FactorCode, ...
5
votes
1answer
74 views

Detect if two time periods overlap

I have an HR system and I need to detect if a new leave (time off) request conflicts with an existing one. I have written code to do this based on the entire day; I now need to do the comparison based ...
2
votes
1answer
56 views

What is the most efficient way to concatenate strings in SQL Server?

I have this code: DECLARE @MyTable AS TABLE ( [Month] INT, Salary INT ); INSERT INTO @MyTable VALUES (1,2000), (1,3100); SELECT [Month], Salary FROM @MyTable; Output: I want to concat ...
4
votes
2answers
225 views

How to extract pipe-delimited data using BCP?

I tried the following command USE <DBNAME> SELECT 'exec master..xp_cmdshell' + ' ''' + 'bcp' + ' ' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + ' out' + ' D:\' + TABLE_NAME + ...
2
votes
0answers
21 views

restore script variable date and time

Goodafternoon, i am looking for a variable for the date and time. I have several backups in one directory and want to restore the latest to a another server. Is there a variable i can use in my ...
0
votes
1answer
27 views

Referring system views and functions in production functions

I am using the following system objects in a stored procedure in order to build dynamic T-SQL statements: [sys].[objects] [sys].[parameters] [sys].[dm_exec_describe_first_result_set] [sys].[columns] ...
39
votes
6answers
3k views

Retrieving n rows per group

I often need to select a number of rows from each group in a result set. For example, I might want to list the 'n' highest or lowest recent order values per customer. In more complex cases, the ...
7
votes
2answers
6k views

How to changes or update local server connection in Maintenance plan job

Two days back our client changed one of our Dev Server name After Server Renamed, All my maintenance jobs and other jobs are failing because server name mismatch. We are using sql server 2012 ...
5
votes
2answers
92 views

Will I need to create a pivot or can I alter this query

The following Query that I wrote is working to a point. The problem that I found is that in each pump, cable, etc can be listed in each of the part columns. Example: What I would like to get is ...
6
votes
1answer
45 views

Round-robin T-SQL problem with a twist

I have a table where I need to populate values, which are incremented using given step size. In addition I also have a notion of range, so that certain number of steps will fit inside the range (not ...
1
vote
2answers
46 views

Turning a multiple row resultset into columns with multiple rows [duplicate]

I currently have a resultset that looks like this: Title Value Start Time              1998-01-04 01:38:00.000 End Time                1998-01-04 01:43:00.000 Product Name         ...
2
votes
1answer
124 views

Order by values with most incidence

I have a table as this example: CREATE TABLE customerEvent( customerID int NOT NULL, eventID int NOT NULL ) customerID may appear in many records. I use the following query to identify ...
1
vote
0answers
44 views

Unable to insert explicit value in table due to IDENTITY_INSERT is ON Error

I am trying to insert explicit values in one of my Real time Audit Production database. There are more than 80 columns, I have just taken here 5 columns. I am writing syntax is as below: use ...
1
vote
1answer
33 views

Nightly Excel Spreadheet import into mssql database

I am currently working with a nightly import that I need to create, but am not sure what the best route would be to update/insert into the current table. This is all done in MS SQL Server 2012 and ...
2
votes
1answer
553 views

SQL Server 2008 R2 Database Diagram Error Code

I have sysadmin privileges on a SQL Server 2008 R2 server. I'm working with the development database and want to save a database diagram I created. When I try to save I get the following error ...
3
votes
1answer
399 views

Getting each status change in a table

I have a table that holds status changes per item. The item can go in and out of a status multiple times. ItemTable (ItemId int, Status varchar(50), DateOfChange date) I need to pull the date of ...
3
votes
1answer
660 views

SQL Script to Drop old AD logins, Inactive SQL Logins and all related Users

Related Question: SQL Server script to delete accounts no longer in Active Directory Whilst the above question points out the rather handy sp_validatelogins procedure, i'd like to extend this ...
2
votes
0answers
67 views

Techniques for moving database from Enterprise to Standard Editiion

I have a SQL Server 2012 Enterprise edition and a Model database. On a virtual machine I have installed SQL Server 2012 Standard Edition and want to create a copy of the Model database on it. I have ...
1
vote
1answer
40 views

Is it worth time converting Access query to T-SQL query

Let me just say, before anything else, that I am a enormous noob when it comes to databases in general, so please forgive me for my rather stupid thoughts and actions. I had a database built in ...
5
votes
2answers
3k views

The order of INNER JOIN's

I would like to know if there are any rules about the order of INNER JOIN's in T-SQL 2000. I had 62x the performance on my query when my INNER JOIN that act like a filter is placed at first instead ...
10
votes
4answers
52k views

What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?

Many times I need to write something like the following when dealing with SQL Server. create table #table_name ( column1 int, column2 varchar(200) ... ) insert into #table_name execute ...
3
votes
2answers
63 views

How do I get Brent Ozar's sp_BlitzIndex to run on Azure?

I downloaded the SQL Server First Aid Kit from Brent Ozar's website. When I tried to run the sp_BlitzIndex script against my master database via Microsoft Sql Server Management Studio while logged in ...
2
votes
2answers
781 views

How to get OUTPUT $action, Inserted.*,Deleted.* records in In SSIS

I was just wondering, in MERGE in T-SQL, you can output the OUTPUT $action, Inserted.* and Deleted.* records. I want to ask how can I get OUTPUT $action, Inserted.*, Deleted.* records in SSIS?
5
votes
1answer
72 views

Compound Primary Key - Index both fields?

I have a many-to-many join table between users and roles. CREATE TABLE AppUserRole ( UserId INT NOT NULL, RoleId INT NOT NULL, CONSTRAINT PK_AppUserRole PRIMARY KEY (UserId, RoleId), ...
3
votes
1answer
324 views

sp_startpublication_snapshot Parameter(s)

I am creating a stored procedure that: Restores a DB from a .bak giving the .mdf and .ldf a new name (so we have have several copies of the same DB up (If specified in the SP's parameter) Creates ...
2
votes
4answers
118 views

Effiiency of multiple OR predicates versus a single range

Say I have a SQL statement: SELECT * FROM MyTable WHERE KeyColumn1 = 1 OR KeyColumn1 = 2 OR KeyColumn1 =3 OR ... KeyColumn1 = 10 And another: SELECT * FROM MyTable ...
2
votes
3answers
76 views

Show all stored procedure definitions (content) for editing

Apologies if this has been asked before, the suggested 'related' questions weren't relevant and my own search turned up very little. I need to list the definitions (code) of all my stored procedures, ...
2
votes
2answers
38 views

Ensure only one of the several records for a given item has a column with true value

I have a table called Computer with columns: id NIC_name is_primary My primary key is (id, NIC_name) and is_primary is a bit field for a boolean value. I would like to ensure that for all ...
0
votes
1answer
139 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 ...
0
votes
0answers
12 views

Is it possible to move more than one database object into a schema using the `ALTER SCHEMA` statement in SQL Server 2008 R2? [duplicate]

I have a TSQL Statement like this: ALTER SCHEMA FindUsedAndUnused TRANSFER dbo.UsedUnusedNotSure_the_counts; And when I try to run it with multiple TABLEs or VIEWs I receive an error; Is there ...
0
votes
1answer
29 views

How to merge consecutive record pairs over a table?

I have a table where I need to merge consecutive record pairs (when ordered by id, data and from). Note that for pairs being merged, the first row's prevfrom is NULL and the second row's nextto is ...
2
votes
1answer
92 views

Update one column based on count of records of grouped value in another column

Given a table CREATE TABLE TABLE_ITEMS ( serial_num int null, item_group int null ); INSERT INTO TABLE_ITEMS VALUES (0,1), (1,1), (2,1), (3,1), (4,1), (0,2), (1,2), (2,2), (3,2), (4,2), ...
1
vote
2answers
213 views

Why does FETCH NEXT return second row after opening cursor?

I have a cursor declaration: DECLARE idCursor CURSOR FOR SELECT DISTINCT [id], [data] FROM #tempRemaining OPEN idCursor And the statement: SELECT DISTINCT [id], [data] ...
2
votes
1answer
34 views

How to copy non clustered indexes in transactional replication

I create transactional replication ,but forget to include non clustered indexes and now my subscriber have only clustered indexes (primary key) and I want to transfer all nonclustered indexes to ...
1
vote
1answer
41 views

Strange behaviour in TSQL function (parameter with int variable or NULL behaves differently)?

I experienced very strange problem today with TSQL function. The function has several parameters (int and bit). One of the (int) is set with value NULL. When calling a function with NULL inside the ...
31
votes
1answer
20k views

MERGE a subset of the target table

I am trying to use a MERGE statement to insert or delete rows from a table, but I only want to act on a subset of those rows. The documentation for MERGE has a pretty strongly worded warning: It ...