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
20 views

How to search by text in SSIS packages?

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 ...
2
votes
1answer
11 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 ...
1
vote
0answers
29 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
29 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 @...
8
votes
2answers
357 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
30 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.[...
0
votes
0answers
25 views

Write SQL Server query result set to Event Viewer

Is it possible to write the output of a SQL Server query as a log entry in the Event Viewer?
1
vote
2answers
62 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 ...
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
31 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
19 views

How to display name on a column chart itself in SSRS, not on a legends

How can I display legends name on a column chart itself in SSRS?
5
votes
1answer
254 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 ...
2
votes
4answers
76 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
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 (...
0
votes
1answer
34 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
51 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
2answers
78 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
59 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
562 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 ...
0
votes
0answers
23 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 ...
4
votes
1answer
117 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 '...
2
votes
0answers
51 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 ...
3
votes
2answers
50 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
70 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 @...
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
4answers
107 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 ...
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
1answer
73 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 ...
0
votes
0answers
50 views

Running internal.cleanup_server_retention_window on SSISDB causes t-log to fill up

I have a SQL Agent job that runs internal.cleanup_server_retention_window on my SSISDB to keep it's size in check. The retention period is one day and it's scheduled to run each day. The problem is ...
0
votes
2answers
31 views

Id column based on other columns

I have a simple database with 2000 rows create table [TableName]( [variable1] [varchar](50) not null, [state] [varchar](50) not null, [date] [date] not null ) That look like this [...
0
votes
1answer
26 views

SQL Server to MySQL migration, replace '\xA0' with a regular space

I am having some trouble while migrating data from SQL Server to MySQL--it seems some data does not want to place nice. I am recieving the error: Incorrect string value: '\xA0\xA0' for column '...
1
vote
1answer
52 views

Group by other table with sum function

I have a query that pulls in a delivered count of a campaign. Each run is a new row in the runs table. To retrieve a campaign I need to join the related table in. SELECT c.Company, r.RunId, r....
6
votes
1answer
176 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, ...
1
vote
1answer
26 views

Combining multiple-row data into a single row

I have a table that has columns like following ID Number Value1 User1 Value2 User2 Value3 User3 -- ------ ------ ----- ------ ----- ------ ------ 1 123 0 Jack 1 123 ...
7
votes
3answers
312 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 ...
4
votes
1answer
68 views

Count instances of value in multiple tables

I'm attempting to write a query that returns the count of a given value across multiple tables. Right now, I'm using the following query SELECT COUNT(*) AS SL, ( SELECT COUNT(*) FROM [Sales Line ...
4
votes
1answer
124 views

How do I set a SQL Server Unicode / NVARCHAR string to an emoji or Supplementary Character?

I want to set a Unicode string variable to particular character based on its Unicode code point. I want to use a code point beyond 65535, but the SQL Server 2008 R2 database has a collation of ...
3
votes
1answer
55 views

Create a hierarchy chain (reverse path) for each node in a tree

Given a typical closure table, how would I write a query to get a list of ids back to the root? Is there a way to do that for all the [unique] ids? I'm using Microsoft SQL Server. Given: parent ...
0
votes
1answer
34 views

dropping SYNONYM is blocking in sql server?

I have synonyms which points to a TestDB1, by the next refresh synonyms point to TestDB2. while re-pointing from TestDB1 to TestDB2 it will drop all synonyms and re-creates them for TestDB2. Now my ...
-4
votes
2answers
52 views

Extract string from varchar

How I can extract the substring of a string from end to beginning? I have the following text, and I need extract the last "Modificado Por" and the "Creado Por" /* CREADO POR : Wxxwww ...
3
votes
2answers
175 views

select distinct one column with all instances of another column in a second column [duplicate]

If I have a table with the following information: ParentID | ChildID ---------+-------- 6 | 6 6 | 7 6 | 8 6 | 9 6 | 10 I need to ...
1
vote
1answer
38 views

Split databases into groups of roughly equal datasize

I am trying to split all databases from sys.databases into 7 roughly equally sized Groups based on data ROWS size. I want a table that contains every database and these databases are in 7 different ...
8
votes
2answers
277 views

Same code in multiple stored procedures

I recently joined a company, and I just noticed that many of the stored procedures have the same section of code repeated throughout. I noticed because I was tasked with changing a small section of ...
6
votes
2answers
234 views

Is there a T-SQL equivalent for punctuation as [0-9] is for numbers and [a-z] is for letters?

Is there a T-SQL equivalent of the [0-9] and [a-z] patterns that will let me pull values from a column that contains punctuation? For example: Create Table #Test ( Value VarChar(10) ) Insert Into ...
1
vote
2answers
41 views

Read TSQL variable from Temp Table

I am hacking around trying to write a script to restore a backup from a known folder location. The location will only ever contain one backup file. I have come up with this so far, but I don't know ...
0
votes
0answers
43 views

How do I code Apriori Algorithm in SQL?

Can I get an implementation of apriori Algorithm in SQL ?