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

learn more… | top users | synonyms (1)

0
votes
1answer
250 views

Breaking up long running query into batches runs fine for a while and then produces fatal error

I'm using a variation of this post which details using "careful batching" to delete records in batches from a table that contains 38MM records. The script below runs for a while and shows results ...
3
votes
2answers
49 views

SQL Server- Limit to Concurrent table queries?

In SQL Server, or RDBMS systems in general, is there a limit to the number of concurrent queries that can be made against a table? How is it that N number of users can access the same physical data ...
0
votes
2answers
299 views

Create new instance MS SQL Server 2014

I have installed MS SQL Server 2014 , but after installation there is no instance created. There is only the default instance. How make a new instance in existing MS Sql Server ? I found a lot of ...
1
vote
1answer
43 views

Update all tables containing column name

In SQL Server, I can find all tables containing a COLUMN_NAME: SELECT * FROM INFORMATION_SCHEMA.columns WHERE COLUMN_NAME = 'COLUMN_NAME' How can I update all of these columns like: UPDATE <...
17
votes
2answers
2k views

If positive, sum all items. If negative, return each one

I am needing to find a way to SUM() all of the positive values for num and return the SUM() of all positive numbers and an individual row for each negative number. Below is a sample DDL: Create ...
-1
votes
1answer
26 views

SELECT rows according to another table

Table A (Id, Value), Table B(AId, Value). I need to select only rows from A, for which the pair (Id, Value) is not in table B. Solution with "JOIN" would probably better, because of performance.
41
votes
1answer
33k 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 ...
2
votes
1answer
1k views

Is someone trying to hack into my SQL Server Database?

I was asked by a friend, who isn't technical at all, to help her with her application. I thought I would just being doing a little C# development and some SQL queries. Turns out it has been a lot ...
2
votes
1answer
28 views

Insert if something changed other than specific column

I have a table with many columns but my snippet is simplified. I have the trigger shown below but how can I have it so that nothing is inserted if only the created_at column has changed? ALTER ...
0
votes
0answers
19 views

ERROR: Arithmetic overflow error converting float to data type numeric

I unfortunately have some bad data and need to work around it. I have a Parcel column in a spreadsheet with some parcels that have inadvertently been converted to floating point numbers. I posted a ...
1
vote
1answer
54 views

Contradiction in book related to variable scope in Stored Procedures

I was reading the 70-461 TSQL training book and the part about stored procedures said this: Variables declared in Proc1 and Proc1's parameters are not visible to any of the procedures called by ...
1
vote
1answer
38 views

How to search by text in SSIS packages? [duplicate]

Is there a way to search by text in SQL Server Integration Services packages using T-SQL statement (or other language)? I want to check if certain table is used in any package as I am going to drop ...
3
votes
1answer
15 views

System Database Migration and Security - Log Shipping or DB Mirroring

I've setup log shipping between two servers (they used to be mirrored). What is the simplest or most efficient way to mirror the system databases between the primary and secondary? Is it just a ...
2
votes
2answers
697 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 message:...
1
vote
0answers
33 views

Sql - sort according to other PK column

I Have this table : table : [SheetGroupRank] ------------------------- | Sheet_ID | int | | Group_ID | int | | Rank | int | ------------------------- Sheet_ID and Group_ID ...
0
votes
1answer
36 views

Using SQL MERGE to UPDATE a CTE

I have the following SQL... DECLARE @FromDate DATETIME ,@toDate DATETIME IF @toDate IS NULL SET @toDate = GETDATE() IF @FromDate IS NULL SET @FromDate = DATEADD(MONTH, -13, @toDate) SET @...
0
votes
1answer
20 views
8
votes
2answers
382 views

Is there a way to make this select retrieve the same results with a single seek?

Is it possible to retrieve the same data as the following with a single seek or scan, either by modifying the query or influencing the optimizer's strategy? Code and schema similar to this are ...
0
votes
1answer
31 views

Linkserver update not working in Transaction

I am trying to do operation on my linked server. I have created one linked server and everything is ok. I just observed one thing which I am not able to understand. if I run: update SERVER.testdb.[...
5
votes
2answers
5k views

SQL Server 2012 is putting [brackets] around Table and Column Names

Is there anyway to turn that off? I'm unable to find anything in options, and they're really a distraction.
1
vote
2answers
64 views

Applying single quotes across multiple lines using ssms code snippet

further to Applying Quotes Across Multiple Lines Is it possible to create a "surround with" ssms code snippet to turn a list of values across multiple lines into an IN clause, so I could: select the ...
1
vote
2answers
83 views

XML column performance in TSQL

I'm finding very limited reading on how a large xml column affects table performance in sql server 2014/6. For reference, it is a 36gig table and minus one xml column, it is 36megs. Some queries ...
1
vote
1answer
322 views

Get the old version of stored procedure code in sql server

We have modified one of our stored procedures code and accidentally committed it in production DB also. Now we need the older version of the code. Is there any way to get the old version of a stored ...
0
votes
0answers
45 views

Is it possible to inspect data in NORECOVERY mode [duplicate]

We are trying to restore a dataset to our online database that was inadvertently deleted. However we aren't quite sure as to when. Is it possible to restore a test database using NORECOVERY, but still ...
0
votes
1answer
33 views

Bulk Update Based Off Values In A Table

In Sql Server 2014 I need to run an Update statement that will update NULL values with values found previously in the table. To better illustrate, here is a very small subset of my data Create Table ...
0
votes
1answer
38 views

Insert into matching on a WHERE clause

I'm sure this is simple but can't get it work. I have two tables, A and B, where one only A has ID and they both have Number. I'd like to copy all the ID's from A into B where their Number matches (...
5
votes
1answer
256 views

Microsoft SQL Server creating an Inline function

Hey guys I'm completely new to SQL where I have to write a user-defined function (UDF) that calculates a student's GPA for a given time frame. The Inputs are StudentId int, ClassStartDateStart ...
5
votes
2answers
102 views

RIGHT() works in a SELECT but not in an UPDATE?

I am trying to run a script which does some work including stripping the leading two characters from the string inside each row of a column. To do this I am using RIGHT() and it works fine when I use ...
2
votes
4answers
77 views

Query To Return Data From MAX(ID)

I need to return only the 1 row of the MAX(ID), but my query is not returning such. This is the result set that I get returned id bbid indate st val 200817 16543 01/01/16 1 ...
0
votes
1answer
36 views

Unique key constraint violation not escalated to parent stored procedure

I have a stored procedure which calls 2 nested sub procedures. For Example, MyProc BEGIN BEGIN TRY BEGIN TRAN EXEC UpdateProc EXEC InsertProc COMMIT TRAN END TRY ...
3
votes
1answer
54 views

How to script XML Schema collection in SQL Server 2012?

I need to make script of XML schema collections in my Database and then insert each xml inside a table field as string. I need to do this to compare current schema collections with the new schema ...
1
vote
1answer
61 views

Convert hierarchy in an ordered list of rows

I tried to make a simple list that represents hierarchy of items. Tags have simple structure like [Id] ,[Name] ,[ParentTagId] I try to make the follwing result table: ID, ParentID, Name, Lev,...
5
votes
3answers
563 views

use of coalesce to check for all variables being null

How can I use coalesce to check for all variables with different type precedence to all be NULL without encountering errors. DECLARE @StartDate DATETIME = NULL, @EndDate DATETIME = NULL, @StateCode ...
-3
votes
2answers
867 views

UPDATE() Function (Insert and Update Triggers)

Is there a way to have the column name dynamic in this function? For example, passing a parameter to a variable named @FldName = 'Name'. e.g. UPDATE(@FldName). Basically, what I want to do is to have ...
0
votes
0answers
24 views

Azure invalid SID - is SID even necessary?

I'm migrating a database from SQL Server to Azure Database Server. I'm doing this manually, because many SQLs have to be rewritten (sp_addextendedproperty, XML Open commands etc). So I grab the ...
3
votes
3answers
108 views

Update Null Values With Other Values

I am new to the SQL Server world. In my example below, I have two companies: Building Blocks Red Cement Only one entry for each company has a value for the columns power1, power2, power3. I need ...
4
votes
1answer
118 views

INSERT INTO inserting multiple times

I have a table that contains all information about user called 'DataSource'. I have columns called 'First Name', 'Surname', and 'Address' (there's more but as an example). I have two tables called '...
7
votes
3answers
157 views

T-SQL: If parameter1 is null, use parameter2

This is a simple one but I can't seem to figure it out. I have two parameters, 1 and 2. If one parameter is passed a null value, use the other parameter in there WHERE clause. Ex: SELECT ... ...
2
votes
0answers
54 views

SSDT Syntax Error 80001 on every Select statement starting with “(”

Visual Studio / SSDT marks the following script line as wrong giving "SQL80001 Wrong Syntax next to SELECT" and also "SQL80001 Wrong syntax next to ')'" error. (select a from [tab1]) union (Select a ...
2
votes
1answer
67 views

How to merge multiple rows into one row with SQL? [duplicate]

Originally, I have one table, storing UserID and the corresponding value based on Year, Month, as shown below: Now, I want to 'merge' the rows according to the Year and Month value and to store ...
2
votes
2answers
115 views

Concatenating rows into a single string query running for 5 hrs and counting

I have a table with 2.6m records. It looks like this: email prject_name rafael.nadal@xyz.com lab1 rafael.nadal@xyz.com lab2 rafael.nadal@xyz.com ...
3
votes
2answers
51 views

Alter the letters after “ACCOUNTS\” to lower case?

I have a database table, users, with different username cases (see the example). How can I update the letters after ACCOUNTS\ to lower case? ACCOUNTS\James.McAvoy ACCOUNTS\michael.fassbender ACCOUNTS\...
0
votes
1answer
71 views

T-SQL insert with output expression [closed]

insert Table1 OUTPUT inserted.ID, s.[Id] INTO @Inserted select s.[Field1], s.[Field2], from @TableVariable s why is s.[Id] marked by red line ? Is it non-correct syntax to save value in @...
1
vote
1answer
276 views

UPLOCK to avoid multiple reads

Given the following table structure... Orders ====== OrderId int PK OrderEvent =========== OrderEventId int PK OrderId int FK EventTypeId int FK I've got multiple concurrent processes SELECT-ing ...
6
votes
1answer
178 views

Create a function to generate a column value in a specific format

I am new to databases or, in fact, programming. I want to create a function or maybe a stored procedure (if it works) to insert/create default values for a column of the users table. In this table, ...
2
votes
1answer
74 views

Disable auto stats in SQL Server

I have couple questions about auto stats in SQL Server: I know I can use the following statement to check if the database auto stats is disabled, but is there a statement to check if a specific ...
2
votes
2answers
180 views

SQL server is there recursive reserve word?

Is there any reserve word 'RECURSIVE' while using CTE query. For example, WITH RECURSIVE node_rec AS ( (SELECT 1 AS depth, ARRAY[node] AS path, * FROM nodes WHERE parent IS NULL ...
3
votes
2answers
62 views

howto get the current line number from an executing stored procedure

Is there a function or keyword that will allow me to get the current line number in a stored procedure? I know there's an undocumented LineNo function that allows you to set the line number, and ...
4
votes
2answers
233 views

Sort a varchar type column

In my table VARCHAR data type column called CartonNo, that contain either numbers or number with letter it depends on. I want it to be sorted numerically, this would be an acceptable ascending sort ...
7
votes
3answers
314 views

Should I add an auto-increment / IDENTITY field to a cross-reference table just for PK purposes?

I'm adding the following cross-reference table to my SQL Server-hosted DB: company_id bigint not null (FK) org_path nvarchar (2048) not null The company_id field refers to the id field in another ...