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
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
33 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 ...
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. ...
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 ...
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
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 ...
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] >= ...
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 ...
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 ...
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
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 ...
-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 ...
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 ...
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. ...
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 ...
-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 ...
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 ...
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
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
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 ...
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 ...
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, ...
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?
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 ...
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, ...
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 ...
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
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
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'') ...
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 ...
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 ...
-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) ...
0
votes
1answer
21 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 ...
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 ...
2
votes
2answers
110 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 ...
3
votes
1answer
68 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
44 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
49 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
61 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
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 ...
8
votes
1answer
208 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
51 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 ...
4
votes
2answers
347 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
50 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 ...