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

Forbid user to query internal SQL server information

I'm trying to forbid database user to get SQL server internal information. Script that creates DB user: CREATE USER [testuser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo] DENY SELECT, INSERT, UPDATE, ...
5
votes
2answers
39 views

SQL Server 2008R2 - MAX Result and Hour per Day

I have a table full of network interface statistics. InterfaceID TimeStamp RCV_bps XMT_bps ----------- ------------------------------ ------------- ------------- 275 ...
0
votes
0answers
37 views

How do you use Extended Events to find port numbers?

I'm migrating some TSQL code from one server to another. The server I'm moving it from is production, so I'm not easily able to adjust settings for obvious reasons. The code runs fine on the ...
0
votes
1answer
31 views

Finding keywords in SQL script

I have a list of 50 columns in a database that are likely to change or be removed within the next few months. I have a task to identify any SQL scripts that reference these tables. Up until now, I ...
2
votes
5answers
256 views

Order By DateTime With 00:00:00 Last

order by datetimecolumn orders a datetime column like this: datetimecolumn 2014-09-04 00:00:00 2014-09-04 00:00:00 2014-09-04 01:00:00 2014-09-05 00:00:00 2014-09-05 01:00:00 2014-09-05 02:00:00 ...
1
vote
3answers
138 views

Need to get identify all tables and columns in a SQL Query

I have a massive task to go through hundreds of SQL scripts and identify the Table and Columns in each query as we are updating our system and some of the columns and tables may be renamed or ...
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 , ...
1
vote
1answer
68 views

SQL Server database has corruption, but displays no errors

We have an extensive database of tables. I moved over a table from our dev db to our production db using a program called SQL Compare. I just copy paste the SQL it produces into Management Studio and ...
1
vote
3answers
94 views

Copy table structure excluding nullability into local temporary table

How can I copy the structure of a table into a temp table (or table variable) excluding the nullability of each column? Mainly I need to copy the column names and data types, but not nullability, ...
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 ...
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
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
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
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
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 ...
-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 ...
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
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 ...
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 ...
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 ...
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
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 ...
-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 ...
-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 ...
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 ...
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
54 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 ...
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 ...
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
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
228 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 ...
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 ...
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 ...
1
vote
1answer
114 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
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) ...
3
votes
1answer
385 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 ...
1
vote
2answers
122 views

Format column length in output - SSMS

SQL Server 2012. Sample query at bottom. I'm trying to create a simple report for when a given database was last backed up. When executing the query at bottom with output to text in SSMS, the ...
12
votes
3answers
264 views

Create hierarchy of multiple levels where each node has a random number of children

I need to create some test data that involves a hierarchy. I could make it easy and do a couple of CROSS JOINs, but that would give me a structure that is completely uniform / without any variation. ...