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
3answers
655 views

How to use DAY/WEEK/MONTH/YEAR constant as parameter in stored procedure?

Assume I have a table looks like this: Scores { Id uniqueidentifier, ScoredAt datetime, Value int } Basically I want to create a stored procedure that works similar to this ...
6
votes
1answer
4k views

Difference between EXEC() and sp_executesql with no parameters?

I just learned about the new sp_executesql stored procedure. I like the fact that there's a way to execute parameterized code from within SQL Server. But, I'm wondering what the difference is ...
1
vote
2answers
2k views

Adding Data to Multiple Tables Using Single Query [closed]

I want to add data to multiple tables using a single INSERT INTO query, can I do this? Please guide me and provide me syntax if this is possible.
3
votes
1answer
669 views

Set simple recovery mode and shrink log files for all user created databases

I hope you can point me in the right direction. I'm not a frequent user of T-SQL, but I did some googleing, and found the script below. I corrected the script a bit. I want the script to: To select ...
5
votes
2answers
9k views

How can I strip non-numeric characters out of a string?

Users enter a search term in a box, and that value gets passed to a stored procedure and checked against a few different fields in the database. These fields are not always of the same data type. One ...
0
votes
2answers
301 views

Converting MSSQL Stored Procedure to MySQL - Syntax Error

I am converting a MSSQL Stored Procedure to MySQL, and am running into a problem. Maybe it's because I have been staring at the code for so long, but I can't seem to figure it out: CREATE PROCEDURE ...
0
votes
1answer
84 views

Problem creating database using default mdf and ldf path

I'm trying to create a database using the default paths on several SQL Servers at once. To achieve this I'm trying to use a script that should create the database on each server, however I've run into ...
0
votes
1answer
144 views

SQLCMD mode: environment variable syntax?

I assume the best way to set my SSMS (SQL Server 2005) output file path during runtime of T-SQL, is to use SQLCMD mode. How do I use environment variables (e.g. %systemroot%) in the code? The ...
3
votes
3answers
723 views

T-SQL Calculate average value for Duration Field

Can anyone please help me on this.. I have a column in my table that stores the duration in an nvarchar field formatted as '00:00:00'. How do I calculate the average duration? I read some other ...
-2
votes
1answer
47 views

Add default constraint from another table

How to add a default constraint to a table such that the value that will be used as default will be from another table's columns? i have created a table MY_TBL in which having a column CURRENCY_CD ...
2
votes
1answer
72 views

SQL CMD Mode Issue

SET QUOTED_IDENTIFIER on declare @source_server_name sysname IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp; create table #tmp( id int identity(1,1), server sysname ) insert into #tmp ...
2
votes
1answer
100 views

Queries for unique names from a site table using multiple criteria

So I'm just learning SQL but have wound up as a database manager. The software they use is overall easy but is very restrictive in the reports it can create, though it does allow custom SQL reports. ...
0
votes
2answers
298 views

NULL value returned when concatenating a string in SQL while loop! [closed]

I'm trying concatenate a string in a while loop, but it only returns NULL. Is there something I'm missing? declare @o int = 10000000/1000000; declare @i int = 0; declare @s nvarchar(200); while @i ...
1
vote
1answer
269 views

How to use COALESCE with multiple rows and without preceding comma?

I'm trying to achieve the following: California | Los Angeles, San Francisco, Sacramento Florida | Jacksonville, Miami Unfortunately, I'm getting ",Los Angeles, San Francisco, Sacramento, ...
0
votes
0answers
29 views

“Not In” returns no rows all the time [duplicate]

Whats wrong with the following query. The first part of the select always return 0 rows. DECLARE @AgentID varchar(20), @StringValues varchar(max), @ContactKey int, @Note ...
0
votes
2answers
153 views

Run “USE” with linked server alternatives

I am using SQL server linked servers and find some difficulties while running queries. Below is the actual query. USe testdb SELECT 'Identitiy property OFF' where COLUMNPROPERTY( ...
13
votes
4answers
17k views

Logical operators OR AND in condition and order of conditions in WHERE

Let's examine these two statements: IF (CONDITION 1) OR (CONDITION 2) ... IF (CONDITION 3) AND (CONDITION 4) ... If CONDITION 1 is TRUE, will CONDITION 2 be checked? If CONDITION 3 is FALSE, will ...
-1
votes
1answer
86 views

combining two CTE in a single query

Here i have two CTE and i need to select values from these two CTE as a single query. I can able to select values from a single CTE at a time but i dont know like how to merge these two CTE and select ...
0
votes
1answer
159 views

getting error incorrect syntax near @certificate

I have created a certificate and now I want to remove certificate signature and drop the certificate. When I supply the certificate name directly it is working but when I'm making use of local ...
1
vote
2answers
130 views

Change DateTime format

How can i return a date only format (DD/MM/YYYY) from a DateTime format column? example below Modified Date 2007-09-01 02:00:00.000 2005-09-01 05:00:00.000 Output: Date 09-01-2007 09-01-2005
6
votes
5answers
4k views

Why can't I use a CASE statement to see if a column exists and not SELECT from it?

Why does something like this not work? SELECT CASE WHEN NULLIF(COL_LENGTH('Customers', 'Somecol'), '') IS NULL THEN NULL ELSE Somecol END AS MyTest FROM Customers; I am just checking if the column ...
2
votes
1answer
68 views

Joining on same table without duplicating data in a field or using c#

I have the following table (a few fields omitted): CREATE TABLE [Device]( [DeviceId] [int] IDENTITY(1,1) NOT NULL, [AuthorizedDate] [datetime2](0) NULL, [DeauthorizedDate] [datetime2](0) ...
2
votes
2answers
68 views

Adding a varchar to a datetime column and dealing with nulls

I have two columns in my table a varchar(8) titled Trans_Time which holds data in an XX:XX:XX time format and a DateTime column named Trand_Date which holds just a the date component in XX-XX-XXXX ...
0
votes
1answer
54 views

Right outer join seems to not work because of where clause filter

I have the following query, I need to modify it, so that it gives me the result for originDocForCurrency.id = 68 if there is no matching for commission.referenzid = 68. I tried the RIGHT OUTER JOIN ON ...
2
votes
1answer
66 views

Find Rows with the Minimum Date per Group

I have table like this and I need to remove duplicates and only leave the min date +-----+-------------+-------+------+-------+-------+-------+ | ID | Date | Alloc | Prod | Water | Tub | ...
1
vote
1answer
267 views

Does the Inserted table contain Identity values?

I have an issue with this AFTER INSERT trigger that I'm using for auditing: Cannot insert the value NULL into column 'TermID', table 'AuditTerms'; column does not allow nulls. INSERT fails. ...
0
votes
1answer
45 views

Unable to check is schema is present first

I've created a SQL script that will release a process to numerous SQL Server instances so I want to check if a paticular schema is present before creating the schema. I'm using the below and it ...
1
vote
1answer
190 views

Given the same input, is the HASHBYTES function guaranteed to return the same output?

If I use the SQL SERVER HASHBYTES() function in a script on my machine, is it guaranteed to produce the same output if run on another machine/instance? For example, will SELECT HASHBYTES('SHA1', ...
3
votes
2answers
210 views

Synonyms and transactions

I'm working with an application that uses SQL synonyms to switch between tables on local and remote databases without duplicating code. BEGIN TRANSACTION if (@isRemote = 1) BEGIN CREATE ...
0
votes
1answer
158 views

How to restore Network backup to Network Path in SQL Server 2005?

I am trying to restore network backup file to network path.. is there any way to do it? I am using SQL Server 2005 and code.... RESTORE DATABASE DB_NAme FROM DISK = ...
0
votes
1answer
35 views

group by+where on calculated column [duplicate]

SELECT SUM(a)-SUM(b) AS c1, SUM(a) AS c2, SUM(b) AS c3 FROM tbldoc GROUP BY Cid WHERE c1<>0 If you take ( WHERE c1<>0 ) not error Thank you Answer
1
vote
1answer
133 views

Include fields from a table function in a select

I'm running this query on SQL Server 2008 R2: SELECT orr.Rate, orr.ID, orr.UserKey, fn.NetRate FROM OngoingRate orr CROSS APPLY dbo.GetRateNetView(orr.UserKey) as fn WHERE ...
1
vote
2answers
90 views

How to restore a database then upgrade it in a single SQL script?

I'm trying to write some SQL that will restore a backup if the database doesn't exist, then run SQL on the database. It is used for installing a database we received from a 3rd party in the form of a ...
4
votes
3answers
295 views

Splitting a deck of cards, and returning the results as if they were shuffled once

Lets say I have a table with a deck of cards, numbered 01-52. I could return the top and bottom cards, as if I held each side of the union's select query in my left and right hands by doing a: select ...
2
votes
1answer
298 views

SQL Server Full-Text Search: combining proximity term and thesaurus

Scenario I am developing a full-text search over a products table. The full-text indexed field of th table contains a brief description of the product, the name of the category and the product code. ...
2
votes
2answers
157 views

Datetime column vs Date + Time columns

I am designing a table for a system that saves a number of logs. We are looking at ~200 entries per second. We are using SQL-Server 2012 Enterprise Edition. I have a question regarding breaking up a ...
-4
votes
2answers
119 views

How can insert Data one Linked Server to Other Linked Server.

declare @sql nvarchar(max) set @sql='DECLARE @SourcePKTable TABLE (rowno int identity(1,1),myOldPK INT) IF EXISTS(SELECT Company_ID FROM [LServer2].[CMS].[dbo].[tbl_MasterCompany] WHERE Is_Sync IS ...
2
votes
1answer
316 views

Is there any difference on performance when choosing DISTINCT or GROUP BY to bring distinct values?

I would like to know if there is any difference concerning performance when choosing DISTINCT or GROUP BY to bring distinct rows from a query. I've tried comparing the execution plans, but they seem ...
1
vote
2answers
2k views

deleting large volumes of rows from table efficiently in SQL Server [duplicate]

I have a table that has gotten a little out of control. I am not a DBA per se but seem to recall that deleting a huge volume of rows in one shot can cause transaction log issues, hamper overall system ...
0
votes
3answers
113 views

How to improve this query performance by indexing properly?

I have following select query, and I need to improve the performance of this query. Can anyone suggest me how to convert the clustered index scan to index seek? DECLARE @now DateTime; DECLARE ...
0
votes
2answers
145 views

Remove a period if it is the first character in a varchar column

I am migrating data from an old system into a new system I am building and have found someone is doing a bad practice of adding a period to the start of some people's names to make them appear first ...
1
vote
1answer
261 views

What causes 'Unknown token received from SQL Server'? [closed]

I have a SQL Server 2005 box with a classic ASP front end (IIS6) calling a stored procedure that uses dynamic SQL. It works except for a single day of data that somehow throws this error even though ...
24
votes
1answer
837 views

Why is the speed of executing statements dependent on the network connection?

It looks like the speed of executing T-SQL is dependent on the latency of the network connection against the server. I assumed that if SQL Server has nothing to report back to the client about, it ...
3
votes
1answer
96 views

What happens when you create the primary key of a table on a different filegroup?

Creating a table on a different filegroup is absolutely no problem when using Sql Server. create table common.test ( id int, CONSTRAINT [pk_test] PRIMARY KEY CLUSTERED (id ASC) ON ...
0
votes
0answers
24 views

Trying to find unused stored procedures, but dm_exec_procedure_stats seems to be resetting [duplicate]

I need to identify stored procedures that are no longer used by our application (~2,000 sprocs, duplicated in ~1,000 databases). To do this, I'm querying dm_exec_procedure_stats, which initially ...
2
votes
1answer
152 views

How to improve this queries performance by indexing properly?

Hi here i have a query and i need to improve the performance of this query.Can anyone suggest me like how to convert the clustered index scan to index seek. DECLARE @now DateTime; DECLARE ...
-1
votes
1answer
102 views

Find day values in bigint date?

This query is working good. select DATENAME(DW,getdate()) AS [Weekday] My Problem is suppose I give input values like select DATENAME(DW,InvoiceDate) AS [Weekday] from store; is not working in ...
2
votes
2answers
156 views

How do I safely drop a list of tables, when i have the table names listed in a column?

Assuming I have a database column containing table names, how do I safely delete all of the tables it lists? The tables themselves are fairly simple - they may have indeces, but definitely don't ...
3
votes
4answers
213 views

How to find duration in a dataset

I have a dataset of the following structure: Target, PollTime, Value ------------------------ 1. A, 2013-12-13 13:31:44.000, 15.00 2. A, 2013-12-13 13:32:44.000, 16.00 3. A, 2013-12-13 13:33:44.000, ...
4
votes
1answer
172 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 ...