Tagged Questions

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
0answers
2 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
38 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
26 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 ...
8
votes
1answer
637 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 ...
0
votes
2answers
36 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
286 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 ...
0
votes
3answers
94 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 ...
1
vote
2answers
66 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
26 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
49 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 ...
2
votes
4answers
292 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
63 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), ...
0
votes
2answers
106 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 ...
0
votes
1answer
40 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?
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 ...
3
votes
2answers
242 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
79 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
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 ...
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: ...
0
votes
1answer
66 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 ...
0
votes
1answer
97 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 ...
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 ...
2
votes
2answers
184 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?
0
votes
1answer
51 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
281 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 ...
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 ...
2
votes
2answers
244 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 ...
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 ------------------------- --------------------------- ...
1
vote
3answers
92 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 ...
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
56 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 ...
2
votes
1answer
39 views

Defining a column as nullable in a view (but not in the referenced table)

My company is working on implementing a new HR application that will unify our existing HR processes. We've run into a bit of a sticking point, though; according to business rules, the EmployeeID and ...
0
votes
1answer
53 views

CTE returns an empty set even though anchor is non-empty

I'm got an adjacency list consisting of two tables: CREATE TABLE permission (id SMALLINT AUTO_INCREMENT(-32768, 1) PRIMARY KEY); CREATE TABLE permission_graph (parent_id SMALLINT NOT NULL, child_id ...
0
votes
2answers
40 views

capture output of table-valued function

Is it possible to capture output of table-valued function? For example, function is defined as follows. CREATE FUNCTION [dbo].[ufn_MyFunction] ( @ItemId INT ) RETURNS @ReturnTable TABLE ( ...
0
votes
1answer
31 views

split string for calculations

I'm doing some basic statistical calculations on the results of a survey. A survey consists of several sections and each section has some questions in it. The answers to each question in each section ...
0
votes
0answers
57 views

SA Admin SQL-Server 2012 x64 keeps changing constantly

I know this is a newbie question, but really I can't find some precise info on this issue. I'm trying to figure out how to create a new user with admin server role on SQL Server 2012 x64. I mean, I ...
4
votes
4answers
374 views

How does SQL Server determine precision / scale?

I'm running SQL Server 2012 SELECT 0.15 * 30 / 360, 0.15 / 360 * 30 Results: 0.012500, 0.012480 This one is even mor confusing to me: DECLARE @N INT = 360 DECLARE @I ...
0
votes
1answer
51 views

Select complete next row based on the current row

Hi i have a complicated condition. I have a table lets say 'Test' ID Partner Type Amount 143854 CSTC Purchase -0.81 144029 CSTC Purchase -0.69 144030 CSTC Purchase ...
1
vote
1answer
27 views

Separating system databases from MDW SQL Server 2008 R2

We have the Management Datawarehouse feature configure on two of our SQL Servers. Both point to the same central database and upload their results to it. Everything works fine with the built in ...
0
votes
1answer
72 views

T-SQL How to Pivot on the Values of a Table?

How do you pivot a table on the value in the field? I have to admit that while I can write some pretty nifty queries, have my CTEs down, and enjoy a wide knowledge of T-SQL programming, Pivots get ...
1
vote
1answer
53 views

Attempting to Pivot a column of grades and coming up with NULL values

I have Data in a table that i'm attempting to pivot from rows to columns, and the script im using is working, but the pivot is coming up with NULL for everything. Here's the source data: ...
2
votes
1answer
90 views

Inserting variables into another variable [closed]

I've been working on the code below. I'm trying to insert results of a declared variable into a sql statement. I've highlighted the section below that I'm talking about. What am I doing wrong here? ...
1
vote
3answers
164 views

Extracting an an integer value from an xml column

I'm just wondering how I can extract an integer out of an XML column. Currently all the digits are 4 which the below query is able to do. But the number is going to grow to 10,000's soon which will ...
0
votes
2answers
119 views

Tsql Query speed slow due to Or within where clause, causing index scan instead of seek

I have been working on making this query work more efficiently. I found that the number of Ors within the where clause are the biggest problem within this query. This query sits in a stored ...
1
vote
1answer
64 views

Connection refused when adding ApplicationIntent

Environment : AlwaysOn HA group, 2 nodes, 1 database, SQL2012, Server2012R2. Here is my .NET C# code : string connectionString = ...
-4
votes
2answers
58 views

sql server date issue [closed]

I have a select statement that when run on the live production server, returns mm-dd-yyyy regardless of what I do. this only happens though when selecting the date from an another aliased select. ...