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
53 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
12 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
101 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
56 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
57 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
43 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
54 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
50 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
178 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
261 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
101 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
70 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
213 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
89 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
189 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
850 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
184 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
374 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
345 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
119 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
154 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
79 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
106 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) ...
3
votes
1answer
61 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 ...
4
votes
2answers
149 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 ...
1
vote
1answer
255 views

Performance of checking for data in related tables

NB: this question is purely academic / to help improve my understanding of SQL Server performance. Given a master table which relates to one or more other tables, how would you determine the best ...
16
votes
1answer
8k views

What exactly does “No Join Predicate” mean in SQL Server?

MSDN "Missing Join Predicate Event Class" says it "indicates that a query is being executed that has no join predicate". But unfortunately it does not seem to be as easy as that. For example, very ...
1
vote
2answers
59 views

Find highest sold item name for each date

I have three tables table 1: items (int,itemName) table 2: orders(order id,oDate) table 3: orderDetails(orderId, itemId, itemQnt) Here is the complete schema with some demo values What is the ...
0
votes
1answer
57 views

Simply inserting to temp table and failing

Can anyone tell me why this won't work for me? CREATE TABLE #UndistributedCmds ( pendingcmdcount int, estimatedprocesstime INT ) INSERT INTO #UndistributedCmds EXEC ...
1
vote
1answer
66 views

Aligned index column order

Situation: There is one SQL Server (2014) database that has multiple partitioned tables, that all share the same partitioning strategy. Tables have a partition per unique [PartitionKey]. Every [Id] is ...
0
votes
1answer
2k views

LEFT vs CHARINDEX for matching the start of a string in T-SQL

I want to match the beginning of a string in a WHERE clause. While I recognise that so often database-performance is dependant on the underlying data structure, is there a best-practice for doing ...
1
vote
0answers
15 views

Opponent Winrate Over another opponent sql

I am trying to find the most efficient way to build a counter warehouse. shortened the tables just to needed info for question Match Table MatchId, version, type MatchParticipants matchId, ...
1
vote
1answer
112 views

LEFT OUTER JOIN versus WHERE IS NULL

For some irrelevant reasons it's hard for me to get LEFT OUTER JOINS working with multiple ON clauses. Since simply moving these clauses to the WHERE statement results in different result sets, I add ...
1
vote
1answer
118 views

How do you copy a TDE-encrypted SQL Server database using T-SQL programatically?

I need to copy a TDE-encrypted SQL Server database to a separate, non-encrypted, SQL Server instance using T-SQL, but only by reading the schema of the source database. This is because I'm going to ...