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

learn more… | top users | synonyms

3
votes
0answers
33 views

why “select” work in mariadb but doesn't work in mysql

set @row_number = 0; SELECT * FROM (SELECT (@row_number:=@row_number + 1) AS num, id, tbl_user_id, title, description, length lengths, ...
2
votes
0answers
60 views

How do I provide a failsafe for retrieval of data from excel to SQL

So say there is a process in place for importing data from excel spreadsheet to SQL using SSIS. And there are two tables, one with new data and one with data from its previous run. I sometimes have ...
0
votes
1answer
153 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 ...
2
votes
0answers
23 views

How to map a drive in a SQL Server script

I am trying to execute below script to Network a Drive use master go exec sp_configure 'show advanced options', 1; go reconfigure go exec sp_configure 'xp_cmdshell',1; go reconfigure go EXEC ...
4
votes
2answers
49 views

Charindex, Getting specific data from a string

Hello I am very new to charindex and have tried to create a query that would extract my needed information but am running into an error. I searched for similar problems on stackexchange as well as a ...
2
votes
1answer
38 views

One SQL different Results by using SQLOLEDB or MSDASQL

I'm using VB6 with SQLOLEDB and MSDASQL. By switching the provider I notice that I'm getting different results with one query. My query checks some server settings and executes one of two BACKUP ...
4
votes
0answers
46 views

How can we improve this PIVOT operation? ( SQL Server 2008 R2 )

We have this query, that runs in 1:10. We need to improve this. This is just a test query, but we will implement this upgrade on our real query. select top 100 * from ( select codCliente ...
0
votes
1answer
53 views

Run Query Against All DB's on Server

I am trying to run this against all the DB's on my server and not just on "TEST_1". Is there a simple way to do this? Ideally the select would also return the DB name. use TEST_1 go declare ...
-1
votes
3answers
69 views

Joins in Stored Procedure

Below is my code, which returns billable and non billable total hours for their projects. I want to show in single row, but i am getting two result sets like below. DECLARE @UserName NVARCHAR(50); ...
0
votes
1answer
66 views

Is there a way to run this query in all databases ? ( SQL Server 2008 R2 )

How can I use this query for each database? I can easily use sp_msforeachdb but it breaks the results for every database. So I tried to insert the results in a temp table, but it's giving me syntax ...
0
votes
2answers
2k views

How to set the timeoffset in a Datetimeoffset column without changing the date part

Is there any way to set the timeoffset of a datetimeoffset column directly without having to use SWITCHOFFSET which changes the date as well? In the post below, we can see the SWITCHOFFSET will ...
-2
votes
1answer
45 views

Business Date and Route Sequence

+-------------------------------------------------------------------+ |Route |Sequence| Planned Time | Business Date | ...
1
vote
3answers
40 views

SQL Server 2012 window function

I've the following table which holds status of jobs in a history table. JobHistory jobOrderId | dateAdded | Status -----------|-------------------------|------- 4909 | 2015-08-26 ...
1
vote
2answers
3k views

Getting current date as part of an Stored Procedure

I have the following SP: SELECT moncallAdd.FirstListing, DATEADD(MINUTE, mOnCallAdd.StartOnCallTime, DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallStart, ...
0
votes
1answer
21 views

How to Create Insert Statement from Table Data using sp_export_data without Id?

How to Create Insert Statement from Table Data using sp_export_data without the Id? I have the following statements and want to omit the Identity. How would I accomplish this? This is how I'm ...
3
votes
1answer
48 views

How to wait for SQL Server replication reinitialization via TSQL

We use transaction and push replication. Every night after the data import 2 replication targets are initialized with the following commands. USE [dbImport] -- Execute at the Publisher to ...
3
votes
2answers
73 views

Using sp_getapplock to implement a queue. Is it correct? Is there a better way?

I've been reading a series of posts by Paul White about SQL Server Isolation Levels and came across a phrase: To emphasise the point, pseudo-constraints written in T-SQL have to perform ...
1
vote
1answer
35 views

How to measure the performance improvement that NUMA brings?

I am testing how one of our stored procedure is working on SQL Server Standard vs SQL Server Enterprise editions. I have created two virtual machines and install a SQL Server(SQL Server 2014) and ...
3
votes
1answer
96 views

Select where bit is not 0

I have made the following query to select a record that isn't 0. SELECT * FROM Table WHERE Bit != 1 OR Bit IS NULL; Why is the check Bit != 1 not enough for a nullable BIT?
4
votes
3answers
85 views

Return even empty groups when grouping by date

I have the following query: SELECT DISTINCT COUNT(CD_BarCode) ,CD_Carrier ,SUBSTRING(CONVERT(VARCHAR,DT_Arriving,103),1,11) Date FROM TB_AGIL WHERE ...
1
vote
1answer
15 views

Query Relating Rows W/O A Corresponding Value

I have a table setup like this: ID Code DateActive DateInactive statusCode 15 20090413 null A 15 null 20090416 I 15 20100101 ...
4
votes
1answer
49 views

Select when group by contains more than one row with the same value?

I am trying to write a query that returns one row from table A whenever a column in joined table B contains multiple distinct values for a single matching row in table A. (A -> B is a 1 -> many ...
1
vote
1answer
69 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
28 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
38 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
25 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
47 views

Where clause in the query [closed]

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
67 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
219 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
81 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
848 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
53 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
20 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
23 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
77 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
58 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
238 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
50 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
52 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
126 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
47 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
34 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 ...