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

learn more… | top users | synonyms

0
votes
0answers
4 views

SQL “Standard” guide?

I am currently learning TSQL and am learning that were possible, I should use "standard" SQL dialect. Where can I find out which commands are "standard" and which are TSQL?
0
votes
0answers
13 views

How can I get a list all Databases in a Database Instance

I am getting an error with SQL Copy Database Wizard. It is finding a database that was dropped over a year ago and fails because it cannot be found. I cannot find any reference to the database and ...
3
votes
2answers
153 views

sql querying for a pattern

I was hoping someone could steer me in the right direction with this one. I have a table that has student grades. Create Table StudentGrades ( StudentID references Student(StudentID) int not null, ...
0
votes
1answer
521 views

Selecting from an excel spreadsheet into SQL Server table

This question has been asked before but I tried giving full admin rights to the SQL Server user on C:\temp\ folder location (I am using Windows authentication into SQL Server Express). So for the ...
2
votes
1answer
23 views

How to group list of start times by a category?

I have a list people and when they started occupied various rooms in the building. Like so: IF OBJECT_ID('tempdb..#RoomAssignments') IS NOT NULL DROP TABLE #RoomAssignments; CREATE TABLE ...
1
vote
2answers
30 views

Applying Quotes Across Multiple Lines

In SQL Server Management Studio you can use the shortcut CTRL + K > C or CTRL + K > U to comment/uncomment lines in bulk. Is there a similar keyboard function that applies single quotes across ...
5
votes
1answer
2k views

How to detach corrupt database in SQL Server

I have Sql server data files saved on external storage. I want to detach specific database. If external storage is not connected I want to drop database. Is this safe way to do that: DECLARE ...
1
vote
2answers
20 views

How can I map a login to a database using T-SQL (not SSMS)

I'm writing a program that requires me to assign all permissions and everything in code. I have gotten stuck in this part: I just want to do the equivalent of clicking the little box under "Map" ...
1
vote
3answers
38 views

Script to drop all databases except one not working

I'm trying to drop all databases but one on my test server. For this, I'm using the below script but it gives error: EXEC sp_MSforeachdb ' USE [?] IF (DB_ID(''?'') > 4 AND DB_NAME()!=''ABC'') ...
1
vote
1answer
26 views

Restore Database SET SINGLE_USER

I am in the process of writing a restore database script. I want to be able to connect as sa and run the restore script. When I run SET SINGLE_USER does this mean that my login is the only user ...
1
vote
1answer
274 views

sp_startpublication_snapshot Parameter(s)

I am creating a stored procedure that: Restores a DB from a .bak giving the .mdf and .ldf a new name (so we have have several copies of the same DB up (If specified in the SP's parameter) Creates ...
2
votes
1answer
24 views

Implications of setting READ_Write

I am looking to update several users on READ_ONLY databases. As the databases are used 24/7 globally, and maintenance windows have just past, I would like to do this during a low-activity moment (to ...
0
votes
1answer
27 views

What is the most efficient way to concat strings in declared table

I have this code: DECLARE @MyTableAS TABLE ( [Month] INT, Salary INT ); INSERT INTO @MyTable VALUES (1,2000), (1,3100); SELECT [Month], Salary FROM @MyTable; Output: I want to concat the ...
1
vote
3answers
64 views

Excluding particular words from a string on a WHERE clause

I have a table that stores invoices and I'm trying to get stats out of it, unfortunately the table is poorly build and some crucial information is all mixed up on a nvarchar field, information such as ...
2
votes
1answer
488 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 ...
3
votes
1answer
451 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
49 views

No value to update but still an TABLE UPDATE Operator

I see this type of code a lot in my databases IF EXISTS(SELECT 1 FROM sample WHERE ID=@id) BEGIN UPDATE sample SET name='cat' WHERE id=@id; END So from my primary analysis it does 2 scan in ...
1
vote
1answer
54 views

SQL Server : Job not reconnecting to restarted Linked Server

The problem To keep two tables across two servers in sync, we have a trigger that adds a change to a MergeTable. Every so often, a merging job runs on SQL Server to push the changes to the Progress ...
-3
votes
1answer
32 views

SQL Server 2012 Express Database Backup Script [closed]

Just a quick one. I want to do a full weekly backup of my SQL Server 2012 Express database. I have created a SQL script and batch file to automatically do a full backup of my database each week and ...
0
votes
1answer
25 views

Enforce a column as unique based on another column value

I want to make a column unique, but only if a different column is a specific value. Consider the following table: CREATE TABLE [SampleTable] ( [Id] INTEGER NOT NULL IDENTITY(1,1) ...
2
votes
2answers
103 views

Updating indexes and stats script

I am having the following errors with the script below Msg 102, Level 15, State 1, Line 44 Incorrect syntax near '?'. Msg 319, Level 15, State 1, Line 47 Incorrect syntax near the keyword ...
0
votes
1answer
18 views

DateDiff Returning Dates and Not Integer Values

I've added a [DAYS_FROM_TODAY] column to my table to calculate the number of days between today's date and the date a check was written for each record in that table. I used the following code to ...
3
votes
1answer
62 views

How to check the timezone for a given datetime?

A database / table I'm working it saves all its date values as datetime (without a timezone). How can I find out which timezone that date was in? For instance, the Central European Summer Time (+02) ...
0
votes
1answer
37 views

Converting date string to SQL DATETIME - hopefully without a udf

I am taking an Excel file that has dates in different formats but one of these formats is giving me some grief when I try converting it or casting it to a SQL DATETIME. For example: Thu Oct 16 ...
1
vote
1answer
39 views

Read transaction log records

I am reading Remus Rusanu article on tlog.In that article he demoed on how to read log payload by using dbcc page option .My question is ,is there a way to read logrecord output of fn_dblog(null,null) ...
1
vote
1answer
53 views

A query for merging date intervals

I have a simple table with 3 columns: (cid, from, to) -> (int, datetime, datetime) The difference between dateFrom and dateTo should be >= 1 month. And unfortunately i have some corrupt data ...
1
vote
1answer
155 views

T-SQL View — How to 'pre-fetch' schema using scalar function, then populate using table query

I have an application that uses T-SQL views. A number of these views are quite complex, joining data from many tables. To avoid column-name collisions, the views often employ table-scalar functions to ...
225
votes
4answers
78k views

What's the difference between a temp table and table variable in SQL Server?

This seems to be an area with quite a few myths and conflicting views. So what is the difference between a table variable and a local temporary table in SQL Server?
1
vote
2answers
65 views

Query for orders that contain a product without using sub-query

Order ----- OrderId OrderDetail ----------- OrderDetailId OrderId ProductId How do I write a query that returns all orders that contain a specific product without using a sub-query? I don't want ...
1
vote
1answer
155 views

Count rows in dataset SSRS2012

I want to count the number of rows in my result DataSet, I put in my DataSet a calculated field with this expression : =CountRows("DataSet1") But I have the following error message: "The expression ...
8
votes
1answer
204 views

statistics are up to date, but estimate is incorrect

When I do dbcc show_statistics ('Reports_Documents', PK_Reports_Documents) I get the following result for Report ID 18698: For this query: SELECT * FROM Reports_Documents WHERE ReportID = 18698 ...
0
votes
0answers
40 views

Using loops inside Openrowset

I'm trying to execute a query across around 200 servers using openrowset function. This function needs to execute in a loop - the query is sucessfull in SQL Server 2012 environments, but failing in ...
2
votes
1answer
79 views

SET CONTEXT_INFO for tracking specific SQL requests

I've got a problem where I'm seeing some very strange lock waits, with the process at the top of the lock wait tree in the 'sleeping' 'AWAITING COMMAND' state. I can see the machine name and ...
4
votes
2answers
313 views

Truncating table without using Alter Table permission

So my DBA don't want developers to give Alter table permission. Now one of the procedure that we created requires to Truncate tables (since apparently delete only removes records, but truncate frees ...
0
votes
0answers
49 views

TSQL LAG equivalent

I am trying to understand how lag works, by converting the following code back to using self-join sql code without using lag. https://msdn.microsoft.com/en-us/library/hh231256.aspx USE ...
6
votes
1answer
192 views

Selecting 'edge' records in a sequence of data

I have a table of items which run sequentially by date with both a start and end for each entry: ID | Start | End ---------------------------------------- 1 | 2012-08-20 00:00 | ...
3
votes
3answers
82 views

I would like to get all values greater than 80, rank, then group in order to get a count of threshold breaches

I have the following basic dataset in MS SQL Server 2008R2: create table #temptable ( Source varchar(255), PollTime datetime, Value numeric(20,2) ) insert into #temptable( Source, ...
2
votes
1answer
155 views

T-SQL CTE Compare previous non-NULL and current row to filter out by difference value

My sample database (in fact a CTE statement data) looks like this: eventdate val 2012-03-23 3965 2012-03-26 3979 2012-03-27 3974 2012-03-28 3965 2012-03-29 3967 2012-03-30 3959 2012-04-02 ...
-1
votes
2answers
875 views

Remove a character from a records enclosed between double quotes

I have a table in which every row have data like: 0150566115,"HEALTH 401K","IC,ON","ICON HEALTH 401K",,,1,08/21/2014 What I want is to remove every comma (,) that is enclosed between double quotes ...
1
vote
3answers
318 views

How to create a SQL trigger to execute a stored procedure when a table is updated

i need some help with creating a trigger to run a qf.stored_procedure when a qf.table is created. AT the moment, i have a job t run the stored procedure every 15 minutes but changing that is not an ...
2
votes
1answer
84 views

Optimise select distinct from large table

I have a table that contains 320,071,712 number of records, the report that I am currently working on can filter the records by date, after which the number of records come down to 145,878,852. The ...
-4
votes
1answer
46 views

Who is depends on specific column? [closed]

I want to alter column table from varchar X to Y. And for that I need to know who is depend on this column . Any suggestion?
4
votes
1answer
50 views

Null Values in a CASE statement

I am playing around with some stuff in SSMS to learn a little more as I study for my 70-461 exam and I came across a little hangup. I am trying to create a table to play around with so I don't have to ...
1
vote
1answer
40 views

Select all most recent records

I have this two tables (and their main fields): Target: Message: Id IdMessage Id IdMain IdReply DatePosted ================ ...
0
votes
3answers
126 views

A more efficient way to write multiple CASE statements [closed]

SELECT CASE WHEN ( Table.COLUMN1) IS NOT NULL THEN 1 ELSE 0 END, CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2) IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( ...
1
vote
2answers
75 views

SQL Server query

All machines should have three groups(grp1,grp2,grp3) but some have two groups and some might have all three and some might have 1 or zero. I have data like this, I have two columns: MachineName ...
0
votes
2answers
36 views

How can I move backups location?

I have a seemingly easy question. How can I move a xxx.bak file to another location with T-sql? I have a job that takes a copy from database a to c:mycopy/copy1/a.bak and I want to create a step that ...
2
votes
1answer
73 views

T-SQL query to find backup folder for specific maintenance plan

I want to check check if backup plans are set up correctly and need to query different servers, mostly SQL Server 2008, but also 2012. Is there any way to query the maintenance plans for databases, ...
-1
votes
1answer
123 views

use variable as column name in where clause in sql

I want to use a variable as column name in my SQL query. Problem: I can not use dynamic SQL as the SQL query that I have is inside of a user defined inline table function. What are other possible ...
0
votes
0answers
23 views

Adding lot of new role assignment on different folders

On our local SSRS we are currently adding a big quantity of different users on different folders. The usual steps are like the ones detailed here SQL Server Reporting Services 2012 Permissions. Is ...