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

learn more… | top users | synonyms (1)

1
vote
1answer
43 views

Procedure completes prematurely after converting comma-delimited value strings to XML

I have a procedure which executes beautifully within SSMS environment yet, when I hook it up to a web page, instead of seeing a list of packageIDs returned, the return is 'null'. I've checked ...
4
votes
1answer
65 views

Minimum distance of a location; one table of points compared to a set of points SQL 2012

I am trying to find the quickest way to find the minimum distance of one point to a table of points. The only caveat is that the table of the points I'm trying to find the minimum distance of is 150K ...
0
votes
1answer
39 views

How to pivot this unpivot?

This is the pivot I made: SELECT * FROM ( SELECT *-- codigo,ID,Grupo,convert(int,CodOpcao) as CodOpcao FROM ( SELECT * FROM ( SELECT 39 as codigo , ...
0
votes
1answer
34 views

How to exporting a databse structure (or many tables) for import

In TSQL: When right click a table you have the option to Script table as > Create which creates the table script including the constraints If you want to port the database or large portion of the ...
1
vote
0answers
25 views

Changing Action on Delete to Cascade

We have a rather big database (about 400'000'000 records) in roughly 100 tables. The foreign keys in these tables are defined, so that No Action is taken, when deleting data from a table. We now have ...
0
votes
0answers
16 views

build users availability report/Table (using sql)

I got a question from our product team to build a "Table availability of customer service representatives (user)". f For example : user_A was available to accept a call from 12:00 to 12:03, so I have ...
-1
votes
1answer
58 views

Error with Loop using Cursors - Invalid Object Name with Set = @

Working on a script to Loop through databases to query one table that each Identical Database has with cursors then return result set to table for each database. I think I have the structure for Set ...
0
votes
2answers
66 views

How to Create Insert Statement from Table Data using sp_export_data without Id?

How to Create Insert Statement from Table Data using sp_export_data without the Id? I have the following statements and want to omit the Identity. How would I accomplish this? This is how I'm ...
10
votes
1answer
992 views

Why are multiple COUNT faster than one SUM with CASE?

I wanted to know which of the following two approaches is faster: 1) Three COUNT: SELECT Approved = (SELECT COUNT(*) FROM dbo.Claims d WHERE d.Status = 'Approved'), Valid ...
1
vote
3answers
61 views

Alter DB modify log file for all DBs

I can run this command to make changes for 1 DB USE [master] GO ALTER DATABASE [DBName] MODIFY FILE ( NAME = N'Name_log', FILEGROWTH = 10000KB ) GO Is it possible to create a script which changes ...
0
votes
0answers
17 views

Execute each row using cursor [duplicate]

I have a strange situation. I am trying to change the size of the log file auto growth to 64 MB for all Dev and QA environment. I have close to 200 servers and 3000 DBs. I have run the below command ...
1
vote
1answer
55 views

Can't create database in SQL Server Express 2005

One of our clients is running Windows Small Business Server 2011, and they have a SQL 2005 Server Express instance (and several other versions also installed). Using Management Studio, running as ...
1
vote
0answers
13 views

Snapshot replication error two days in a row

We have a mature replication system to move data from OLTP to BI. It's been working for years with relatively few troubles. This week we've run into the same problem two days in a row with snapshot ...
-1
votes
1answer
21 views

Trying to get all checkins open during day at specific times

Db structure looks somewhat like this. id, transdate, opentime, closetime 1 2015-11-02 2015-11-02 17:51:00 2015-11-02 18:51:17 2 2015-11-02 2015-11-02 17:58:00 2015-11-02 19:26:52 3 ...
0
votes
2answers
38 views

Column name or number of supplied values doesn not match table definition

I have created three tables using USE UserDecomAudit GO CREATE TABLE ADUserData ( ADUserID int NOT NULL PRIMARY KEY, Name varchar(50), SamAccountName varchar(10) NOT NULL, DistinguishedName ...
3
votes
2answers
104 views

How to wait for SQL Server replication reinitialization via TSQL

We use transaction and push replication. Every night after the data import 2 replication targets are initialized with the following commands. USE [dbImport] -- Execute at the Publisher to ...
2
votes
1answer
58 views

How to pivot without fixed columns in TSQL?

I'm struggling with pivoting a quite simple table. All examples and tutorials on the web, are not what I'm looking for, so maybe you guys can help me out here (I must say that my T-SQL knowledge isn't ...
3
votes
1answer
72 views

How are precision and scale calculated when decimals are multiplied?

When decimals are divided, the precision and the scale of the result value are calculated due to this formula: max precision = (p1 - s1 + s2) + MAX(6, s1 + p2 + 1) -- up to 38 max scale = ...
0
votes
0answers
25 views

Identify Similar Groups [duplicate]

Here I have 3 Tables, 1 - Equipments 2 - Accessories 3 - EquipmentsCompatibleAccessories Equipments --------------- 1 Printer a 2 Printer b 3 Printer c 4 Printer d 5 Printer e ...
-2
votes
1answer
58 views

Need to list the Similar Sub Groups

Kindly help me to get the list of similar groups together. Please see the above illustration. Here, BaseItem 6586, 6587, 6588, 6589 are sharing similar kind of SubItems (SubSet 1, SubSet2, SubSet3 ...
0
votes
1answer
45 views

Left join on one or the other column without table-scanning

I have this huge sql with a lot of left joins. Mostly on the tables primary key, so no problem. On one table though, my key could exist in either one column or the other. Syntax-wise that is no ...
-1
votes
1answer
33 views

I would like to insert the result into a table in sql?

My query is as below : Declare @FromDate date = '2015-April-1'; With finYears(finY) as ( Select DATEADD(month, UsesrID, @FromDate) From (values(0), (1), (2), (3), (4), (5), ...
1
vote
1answer
51 views

Best practice to store and execute a list of queries to build a Fact table

Good afternoon, Junior DBA/Developer here: I have a DataMart that I have to build with a bunch of Statistical information. Each entry will be a different statistic and I have 80 different queries ...
5
votes
2answers
3k views

Update Statement takes forever to complete [closed]

I have table around 1250000 records, and 80 columns Table Structure CREATE TABLE [dbo].[Table] ( [Column1] [int] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL, [Column2] [int] NULL, [Column3] [int] ...
5
votes
1answer
110 views

Is it possible to add a certificate to a database role?

I have a certificate I am using for inter-DB access to a stored procedure in SQL Server, and am able to provide access to the stored procedure by creating a new user in the database using: CREATE ...
0
votes
1answer
55 views

Sub Query vs Table Filter

I have have been assigned to resolve Performance issue of a Stored Procedure. Select TableTwo.Column1, DatabaseTwo..TableThree.Column2, Sum(TableTwo.Column3) As Column3 --- Section 1 from ...
0
votes
1answer
53 views

How Do I Filter a Group of Rows based on one Column's Value in TSQL?

I am trying to make a SQL query in Server 2008 R2 that selects the rows only for employees who have over 40 hours in a pay week and a pay description that doesn't include "over time". So far I have ...
6
votes
1answer
179 views

Getting database sizes on an MSSQL Server named instance

I need to get a list of database sizes for a SQL server migration project. The SQL Server server we're migrating from has three installed instances, one default and and two named. It's running SQL ...
1
vote
1answer
53 views

Full outer join problems

I'm trying to create an xml with customer specific prices for a webshop I am working on atm. I have to join data from 6 tables in order to acomplish that. I ran into a problem I am unable to solve ...
0
votes
1answer
32 views

Merging data from multiple tables

I'm trying to merge data from multiple tables in different databases on the same server however the result is incorrect (i.e. the number of rows returned is more than what it should be). Here's the ...
3
votes
2answers
7k 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
2answers
273 views

How to list all past remote login information

Instead of using "Remote Desktop" for connecting to SQL Server, I and my co-workers are using SQL Server Management Studio to apply changes to the remote server (each user has their own credentials). ...
1
vote
1answer
113 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 ...
3
votes
2answers
71 views

Remove similar and successive rows

I have a table with many entries each describing the commerce state of an item. The table has the following structure: CREATE TABLE CommerceState ( Id INT NOT NULL PRIMARY KEY , ItemId INT ...
5
votes
2answers
62 views

SQL Server - Stopping a server side trace

I want to setup a server side replay trace and scripted out setting up the trace. However I want the trace to end either after 12 hours or once 10 1GB files have been created. Normally with a max file ...
2
votes
1answer
226 views

COLLATION and UNION ALL

I have the following queries: SELECT * FROM OPENQUERY([SERVERA], 'SELECT ''SERVERA'',[PhoneNum] ,[PhoneExt] ,COUNT(2) as CNT FROM [DB].[dbo].[ResourcePhone] WHERE PhoneNum like ...
4
votes
1answer
90 views

Are there any reasons to avoid using sp_rename for renaming objects?

In the documentation it is clearly said: Changing any part of an object name can break scripts and stored procedures. We recommend you do not use this statement to rename stored procedures, ...
3
votes
2answers
278 views

Way to ignore pages in memory for 1 query

Is there some way to ignore both cached query plans and any data pages in memory for a single query or batch? Something like a table hint or other option which can be turned on just for the current ...
14
votes
4answers
27k views

Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition?

Does the SQL Server (2008 or 2012, specifically) CASE statement evaluate all the WHEN conditions or does it exit once it finds a WHEN clause that evaluates to true? If it does go through the entire ...
2
votes
2answers
199 views

How to reference only affected rows in AFTER UPDATE trigger

I have this table: And I'm trying to create an update trigger which will update last_updated_on = GETDATE() and last_updated_by = SYSTEM_USER columns whenever an update is performed on step_number ...
4
votes
1answer
856 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 ...
1
vote
1answer
42 views

How to find statement executed by sp_executesql in [sys].[dm_exec_query_stats]?

I have a function which is generating dynamic T-SQL statement. The generated code is executed then in a stored procedure. I want to log some statistics about how long the execution of the generated ...
0
votes
0answers
24 views

SSRS Scrolling Issue

My report column headers are not visible when I scoll down my report in SSRS. The screenshot below shows what is happening. The Tablix properties are set to keep the row and column headers visible ...
5
votes
1answer
191 views

Incorrect syntax near 'role' with generated script

I've recently had to recreate a user several times. To facilitate the creation of the user, I would right-click the user and choose script login as, create to, New Query Editor Window. However, after ...
3
votes
1answer
384 views

The multi-part identifier “d.dept_name” could not be bound

I want to create a table with department name as column name and no. of employess for each department as its row. Here dept_name is coming from Dept table and count of emp.ID is coming from emp ...
2
votes
1answer
349 views

SQL Server Mirroring can't connect to mirror database

I am at my wits end with this. I am trying to set up mirroring between a development server and my local machine. The error that keeps coming up is: Msg 1418, Level 16, State 1, Line 1 The ...
0
votes
1answer
121 views

Numeric Latitude & Longitude values are rounded up when querying SQL Server Database

Developing an application that is using Google Maps. Latitude and longitude values are stored in the table as the correct value. However when they are pulled into a view the value is rounded to the ...
3
votes
1answer
161 views

Including null values in unpivoted column

Consider the following table: id title genre1 genre2 genre3 genre4 genre5 1 Movie1 Thriller Crime NULL NULL NULL 2 Movie2 Fantasy NULL NULL NULL NULL 3 Movie3 ...
-4
votes
1answer
80 views

How to Pass table type as Parameter in T-Sql like Postgresql [closed]

In my company like to move from PostgreSQL to SQL Server. Doing this, we need to migrate pl/Pgsql functions to T-Sql. In pgsql function we are create and pass type as parameter. Same option had in ...
1
vote
1answer
107 views

Create index clustered X NonClustered with include

I have a table like : create table x (a varchar(20), b varchar(30), c varchar(30), primary key clustered(a asc)) so I'm wondering if I create an index like: Create nonclustered index test on x(a) ...