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

learn more… | top users | synonyms

2
votes
1answer
94 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 ...
0
votes
0answers
7 views

How to Extract suffix from the table column names [on hold]

I have 2 tables , 1 contains answers and columns are A1, A2, A3 so 3 in A3 means this is answer to the question 3. tblAnswers Personid A1 A2 A3 =============================== ...
2
votes
1answer
34 views

Can't seem to understand why STUFF isn't working

I've got two tables that store questions and possible answers. The questions table can have multiple possible answers to it. I want to display the possible answers it can have per question.. For ...
1
vote
1answer
51 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
21 views

How to optimize STDistance execution?

I am creating temporary table during stored procedure execution with the following structure: [ID] BIGINT [Point] GEOGRAPHY the ID is not unique - there are about 200 records for each ID. ...
0
votes
1answer
57 views

SQL Server table and view show different values

I have a view that selects data from a table that is showing different data. The table is dbo.user, the view is dbo.users (don't judge, I inherited this system). The view is simply select * from ...
2
votes
1answer
43 views

Set based query to group data ordered data

I have a table of user action data and I would like to try and group it into 'sessions', so I could figure out how many times a user has logged on, how long they stay online, and how many actions they ...
0
votes
0answers
45 views

Query Performance - Why is a query slower when there are two criteria on the same column?

I have these two queries against a view, where I've added WHERE clauses against a date column (Indexed, Unique, Non-Clustered, Ascending): SELECT ColA FROM MyView a WHERE a.[Date] >= ...
0
votes
0answers
46 views

Rounding Casted Converting Error

The database I am working with has ntext datatypes which according to my understanding is evil. I used CAST to varchar to fix the issues in the below trimmed SQL Server query. Anyway, my question is ...
4
votes
1answer
60 views

How to move a fairly large tsql table from one server to another server?

I have been working on thinking of ways to move a 40gb table from one SQL Server box to another SQL Server box. They are at separate companies without an intranet linkage and obviously without linked ...
4
votes
2answers
743 views

How do I delete unique rows from a table with duplicate keys?

I have the following data: road | ID | other column | other column | ------------------+------+---------------+--------------+ MAIN ST | 1 | ... | ...
-2
votes
0answers
38 views

Baffled by duplicates despite selecting distinct values [closed]

I am getting the below error when trying to select distinct rows from a table into a temp table. : Violation of UNIQUE KEY constraint 'UQ__#Context__________5427A9BF'. Cannot insert duplicate ...
1
vote
1answer
33 views

Converting VARBINARY HEX to VARCHAR

I have a fun problem, I have data stored in SQL Server 2008 VARDATA column as Hex. I need to see what this translated to string is. I have tried CONVERT(VarChar(MAX), VARDATA, 2) and it returns a ...
-2
votes
2answers
35 views

UPDATE() Function (Insert and Update Triggers)

Is there a way to have the column name dynamic in this function? For example, passing a parameter to a variable named @FldName = 'Name'. e.g. UPDATE(@FldName). Basically, what I want to do is to have ...
-6
votes
1answer
63 views

SQL Server Query Question

I get the following errors when I execute the below query Error messages: Msg 207, Level 16, State 1, Line 10 Invalid column name 'Name'. Msg 207, Level 16, State 1, Line 10 Invalid ...
2
votes
1answer
31 views

How to find the most common phrases in a varchar field

I have a table which contains support tickets. Each ticket has a title and many of them have common phrases in them (XXX cannot connect to YYY, XXX requires a new YYY, XXX requires upgrading, etc. ...
12
votes
2answers
2k views

Why does SELECT 1/2 return 0?

As the title says. I even tried SELECT CONVERT(NUMERIC, 1/2) which also returned 0. I am using SQL Server 2008.
0
votes
2answers
33 views

How do I extract a repeating string pattern from multiple rows?

I have the following data: | ID | BadCol | +------+-----------------------------------------------+ | 1 | All of R489804 R489805 R489806 R489807 ...
0
votes
0answers
55 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 ...
1
vote
0answers
20 views

Can I Parse and/or include “And” in my SQL Statement for a full text index?

So I have a query that looks at a full text index like this: SELECT * FROM Table1 q INNER JOIN CONTAINSTABLE(Table1,TableField, @Keywords) search ON q.Key = search.[KEY] Now @Keywords ...
0
votes
1answer
22 views

detect what columns to select depending on a column value of the same table

I am trying to build a query which returns different columns of the table depending on a column of the same table. Say, a column represents a type of item. This is my temporary solution, in which I ...
1
vote
1answer
74 views

Shorthand for on primary key?

Is there a shorthand for merging rows based on the primary key? For example: MERGE INTO [MyDatabase].[MySchema].[MyTable] AS target USING [MyLinkedServer].[MyDatabase].[MySchema].[MyTable] AS source ...
0
votes
1answer
56 views

Automate SQL Server restore from Prod to Dev

I would like to setup an automated restore job which takes the latest timestamp .bak file and restores it into the DEV instance. I currently have a job on the DEV box that uses the UNC path to the ...
1
vote
1answer
174 views

Is someone trying to hack into my SQL Server Database?

I was asked by a friend, who isn't technical at all, to help her with her application. I thought I would just being doing a little C# development and some SQL queries. Turns out it has been a lot ...
2
votes
2answers
2k views

Merge two records in the same table, keeping foreign key relationships intact for both

I have a table, called SITES, that has three columns, lets say it looks like this: ID Name Path 1 Google http://www.google.com/ 2 Microsoft http://www.microsoft.com/ I also have ...
1
vote
2answers
103 views

How to re-declare T-SQL variables if it already exist?

Background: I'm building a SQL command to be executed on the server which involves a lot of accessing store procedure and declaring variables which are used as input to other stored procedures. This ...
1
vote
1answer
202 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 ...
1
vote
1answer
41 views

Get time elapsed between log entries

At a company, fluids are prepared and held in tanks, following a preparation/recipe. The preparations move from tank to tank depending on needs. The tasks are logged in sequence into a table. What I ...
0
votes
1answer
24 views

GRANT permissions to all tables, views, procedures in SQL Server 2000

I was wondering if there was a fairly efficient way to use T-SQL with SQL Server 2000 syntax to GRANT SELECT, INSERT, UPDATE, DELETE, on all TABLES and VIEWS for a particular database while excluding ...
6
votes
2answers
147 views

Hindrance in TSQL SVF Call

I have a simply Products data table with 100k records (just generates some random data): set nocount on create table dbo.temp_Products ( [ProductID] int, [Type] tinyint, [Price] float, ...
1
vote
0answers
22 views

Passing Table Values into a TVF

I have a TVF that takes a userID parameter. I have another table that contains a list of these userIDs. I want to pass all these userID values into the TVF. I have tried this code... FROM ...
4
votes
1answer
114 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?
3
votes
2answers
173 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
524 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
31 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
1answer
39 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
23 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
48 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
34 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
280 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
28 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
29 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
70 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
489 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
479 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
52 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
62 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
42 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
30 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) ...