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

learn more… | top users | synonyms

1
vote
1answer
31 views

how to define trigger scope variable [on hold]

My trigger is meant to calcalute the grade of a student base on the column value for every insert.But if a column value for the column is modified it the corresponding Grade should change. Currently i ...
0
votes
1answer
51 views

TSQL vs SSIS Lookup

I was trying to find some well performing Tsql equivalents for the SSIS Lookup component. Especially for Fact table loading. After few different query patterns the most obv performed the best: ...
0
votes
0answers
36 views

Backup table is larger than original table

I am cleaning up my database by deleting unwanted tables such as temp tables, backup tables. While doing clean up I see that I have a backup table and the original table. The size of the backup table ...
0
votes
1answer
19 views

Import CSV file - dynamic path

I am trying to import a csv file dynamically. This works: BULK INSERT #CSV FROM 'C:\Users\MyName\Desktop\Players.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) I would like to ...
2
votes
0answers
46 views

Quirk update for cumulative sum of chances for a raffle. What can go wrong?

I need to make a raffle where users have different chances. | USER | CHANCES | |------|---------| | A | 10 | | B | 25 | | Z | 15 | Looking in this answer: drawing a riffle ...
2
votes
1answer
28 views

How can one select a static value as a column based when using UNPIVOT?

I'm trying to unpivot a few columns in a table. This is for a reporting tool. The source data is already normalized. I am able to get my desired results with a series of UNION ALL statements. I can ...
6
votes
2answers
532 views

Does MERGE use tempdb?

Consider the following query: MERGE [Parameter] with (rowlock) AS target USING (SELECT @AreaId, @ParameterTypeId, @Value) AS source (AreaId, ParameterTypeId, Value) ON ...
0
votes
1answer
69 views

Stored procedure to compare two columns from different tables and make the insert

I have two tables: Project with fields fld_id (integer), fld_allocated_hours (integer), fld_project_id (integer) Timesheet with columns fld_id (integer), fld_allocated_time (integer), ...
8
votes
1answer
666 views

TSQL Why is Top faster with a variable?

Morning all, I have been working on some moderately complex sql to 'obtain' some data from a third party products database, to display it in our own in house applications. I added in a select to ...
1
vote
3answers
101 views

Select only rows with minimum values from union table

SQL Server 2005 Here is a sampling of the data from a union of two tables (named "vProducts_All"): DBID UPC Price1 Price2 1 023614025559 689.0000 649.0000 2 ...
0
votes
2answers
40 views

SQL: TSQL to Find invalid views

we have a Tuesday/Thursday Maint window for one application we support. On Tuesdays we modify one facility and thursday if there are no errors we roll all the changes out to the other facilities. The ...
2
votes
1answer
288 views

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values

One of INSERT INTO script is written as following. INSERT INTO tableName (Column1, Column2,....) VALUES (value1, Value2,...), (value1, Value2,...),.... Following is the error we are facing on ...
1
vote
2answers
68 views

TSQL substitute for MAX() string ranking

I have a table with repeating values (col1) and I need to return col2 based on a ranking system. In my example the ranking system is handled by MAX() giving me the highest value. I then return the ...
0
votes
1answer
33 views

Using a string with single quotes in a table

I'm trying to insert the following string, as written, into a table: 'affinity mask', 0 However I can't figure out how exactly I need to escape the quotes. It always wants to treat the , 0 as a ...
0
votes
0answers
28 views

Restoring a SQL Server 2012 database in high availability

I have a database which is in always-on high availability mode synchronized with another database on a different instance. How can I restore from a .bak file into the primary database using T-SQL? ...
0
votes
1answer
51 views

Is there a way to minimally log insert under Always on?

I have a primary database in FULL recovery mode which is part of Always On group. Is there a way to minimally log insert operation under FULL recovery model? I have a process that is executed each ...
0
votes
2answers
197 views

How to create a versioning DDL/DML change script for SQL Server?

I have an application which has a database, and some versions of the application introduce schema changes. To make installation easier (?), I figured I want to create a meta table which contains the ...
2
votes
4answers
293 views

Saving a variable globally in SQL server it self and use it when required

We are using ENCRYPTBYPASSPHRASE to encrypt some columns of some database. To decrypt the values every time we need to pass passphrase with DECRYPTBYPASSPHRASE. So I planned to create a stored ...
0
votes
1answer
474 views

Selecting from an excel spreadsheet into SQL Server table

This question has been asked before but I tried giving full admin rights to the SQL Server user on C:\temp\ folder location (I am using Windows authentication into SQL Server Express). So for the ...
0
votes
2answers
54 views

Restore Database with Multiple Restore commands

For automation, I have to dynamically construct the restore commands and thus I have created a script. A .bak file is created on a remote server and I receive it daily. The script builds the restore ...
0
votes
1answer
27 views

Reducing size of datatype involved in index fails where as increasing size work

This question is related to my previous question Database events that will cause deletion of index For example if i create a table like CREATE TABLE dbo.Customers (ID INT IDENTITY(1,1) PRIMARY KEY ...
0
votes
2answers
112 views

Error Grouping By Multiple Columns using CASE

Is it possible to group by multiple columns in a CASE statement? I get error "Incorrect syntax near ','." in below code... SELECT CASE WHEN Type = 'Test' THEN Description ELSE ...
1
vote
1answer
235 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 ...
0
votes
1answer
43 views

12 hour format time in SQL datatype

I can convert time datatype to 12 hour format using convert etc. But is there any option to save the time in 12 hour format in SQL tables using any settings or property or any other methods?
1
vote
1answer
412 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
2answers
243 views

Physical Server or a Virtual Machine SQL Server?

Is there a anyway to figure out via a SQL Script to know whether SQL Server is running on a Physical Server or Virtual Server ?
3
votes
3answers
82 views

SET IDENTITY_INSERT inside INSTEAD OF INSERT trigger not working

I'm trying to perform a indentity insert inside a instead of insert trigger, but when I try to insert data into the data, it throws and error msg "Msg 544, Level 16, State 1, Line 36 Cannot insert ...
0
votes
1answer
100 views

Delete and merge duplicate records from joined tables?

I am trying to delete duplicate records from the output of the query below. If they also meet certain conditions i.e., 'different address type', then I would merge the records. From the following ...
0
votes
0answers
40 views

select random tuples but prefer old dates

With this table: CREATE TABLE T_TEST ( id int identity, somevalue varchar(256), lastupdate smalldatetime ); i want to select n random tuples which age is older than x days but prefer old tuples ...
2
votes
1answer
867 views

Bitwise operators affecting performance

I have recently seen some stored procs coming my way that contain BITWISE operators that are causing the optimizer problems. I know from playing with execution plans that these are the cause of the ...
0
votes
0answers
34 views

T-SQL Parsing XML escaped in format %3E (less than) [migrated]

First question here so please be gentle :) I am a junior SQL developer. I am try to parse a string in xml format that has been escaped like below: ...
4
votes
2answers
6k views

A Script to insert dummy Data in all tables of Database

I wanted a Script that can loop through all tables and their columns in the database and insert dummy data based on column type and size, so that i can start using the database for testing, and ...
2
votes
2answers
353 views

What is the best way to get all data for a date range, plus the last event just before the range?

For this question assume the following: There is a table of events [Event] There is a table of employees [Employee] There is a table that relates the two [EventEmployee] (EventID + EmployeeID) Each ...
0
votes
1answer
69 views

Invalid object when selecting from temporary table

I'm creating a temporary table called #PrevStatus. I've confirmed that the create statement is correct. When I run, exec (@TableQuery) which contains the table create statement and then right ...
1
vote
3answers
155 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
3
votes
1answer
1k 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 ...
2
votes
2answers
192 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?
2
votes
2answers
100 views

How can I use expressions as arguments to EXEC?

I am building some scripts and found an annoyance to be that I can't provide expressions as arguments to EXEC. Here is an example I have run into. I want to setup various items using a consistent ...
3
votes
1answer
341 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 ...
0
votes
1answer
53 views

How to set a restart on a TSQL Job?

I currently have an important TSQL job that hangs probably once a week. It moves data from one database to another. It is on a schedule running every 5 minutes indefinitely throughout the day. ...
0
votes
1answer
58 views

SQL Server 2008 and constant expressions

If I have the following scenario, would Replace be evaluated a single time? DECLARE @text nvarchar(32) = 'This is a test field' SELECT textField FROM Table WHERE Id = @idICareAbout AND textField = ...
11
votes
2answers
282 views

Unexpected results with random numbers and join types

I have a simple script that gets four random numbers (1 through 4) and then joins back to get the matching database_id number. When I run the script with a LEFT JOIN, I get four rows back every time ...
1
vote
0answers
40 views

Foreign Key constraint remain untrusted and disabled even after ALTER TABLE CHECK CHECK CONSTRAINT command [closed]

In this database I support, there are more than a few FK constraints set as disabled or untrusted. I'm using this querys to find that out: SELECT 'ALTER TABLE [' + s.name + '].[' + o.name + '] WITH ...
2
votes
2answers
245 views

How to use local variable in USE statement

How to use local variable in USE statement? I get error: Msg 911, Level 16, State 1, Line 6 Database 'DWSource_@Country' does not exist. Make sure that the name is entered correctly. Code: ...
2
votes
2answers
62 views

Using collate: When to use and when not to use

In one of my applications we had tsql like shown below SELECT DISTINCT [Dblist].[DbName] AS [DbName] FROM [Common].dbo.[Dblist] WHERE dbname not in ( SELECT [name] FROM ...
1
vote
3answers
96 views

How to run sql agent jobs in a sequence order.with T-sql

My requirement is to run the SQL Agent jobs (EX: job1 to job10) with T-sql in a sequence order. That means job2 starts automatically when job1 completes. This process should continue until job10 ...
3
votes
1answer
49 views

How to join by most recent preceding date?

Imagine I have a database with historic price data and a second table with dates Item | Price | Date Customer | Item | date ------------------------- --------------------------- ...
7
votes
5answers
35k views

Writing select result to a csv file

We need to write the SELECT query results to a csv file. How can it be done using T-SQL in SQL Server 2008 r2? I know that it can be done in SSIS, but for some reasons, we don't have this option. I ...
0
votes
0answers
47 views

What is the bare minimum access needed in the public role?

I need to lock down access to a specific database for a user - they should only have access to execute a couple of stored procedures. However, by default the user gets everything in the "public" ...
0
votes
0answers
62 views

How to check if all the extendend stored procedures (xp_) are enabled or disabled?

These are the stored procedures that I want to check if they are enabled or disabled. xp_availablemedia xp_cmdshell xp_deletemail xp_dirtree xp_dropwebtask xp_enumerrorlogs xp_enumgroups ...