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

learn more… | top users | synonyms

3
votes
2answers
267 views

Defrag a HEAP by creating clustered index and immediately dropping it

I'm writing a script which intends to defrag a HEAP based table by creating a dummy col with a clustered index and then immediately dropping it. (It's someone else's app and I don't want to make any ...
2
votes
2answers
59 views

concatenating columns from multiple unrelated 1-row resultsets

Assume I have these two queries in SQL Server 2014, both of which return one row from unrelated tables: SELECT SUM(A) A, SUM(B) B FROM X SELECT SUM(C) C, SUM(D) D FROM Y I'd like to combine these ...
0
votes
0answers
22 views

sql query group by data of certain range in continuous date

I want to Write a sql to query group by data within a certain range in continuous date.I'm usig T-SQL. Table: Date | MeterNo| Data | ---------|---------|---------| 01-06-15 | 12345 | ...
2
votes
1answer
35 views

Can I use a script to restore multiple SQL databases from .bak files?

I'm migrating 100 plus SQL databases over to a new server in a new location. I have the maintenance plan in place to take full back ups and then a script to copy over the .bak files to a temp ...
4
votes
2answers
53 views

Strange looking where clause in sql server

I've found fa strange looking code in T-SQL. Here is the strange looking part: WHERE @term1 IS NULL OR (@term1 IS NOT NULL AND (businessLogicCondition)) The query works well, but what is the point ...
0
votes
1answer
23 views

how to set default value of column 0 when NULL is inseerted

I have a table with a column which has default value to 0. However when null is inserted to this table, I want it to set it to default. As the insert query is generic and used by other databases too, ...
-1
votes
2answers
1k 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 ...
7
votes
3answers
506 views

Use 'default value' in table when NULL

I am building a config table and want one row to be the default value. For the example, I have a list table of all my databases and a msg table of my return values. In the msg table, I have one row ...
2
votes
1answer
56 views

How to Retrieve value from a User-Defined Table Type (UDTT)

Goal: I want to reach and display the value of @data inside of [wwtest]. Problem: The UDTT is from the database [test] and I receive an error message: The type name 'test.dbo.tvf_id' contains ...
1
vote
1answer
34 views

Statistics needs to be captured for query analysis

What statistics are required to theoretically prove that X query work better than Y query. As per my knowledge I need to look for: Query elapsed time Logical read Logical write Physical read ...
3
votes
1answer
28 views

A Customized Table with Chronological order

Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id. Problem: When I ...
1
vote
1answer
30 views

How do I change name format in TSQL Query Result?

I have a simple tsql select query that returns a user's name in this format: e.g. Doe, John How do I update the output so that it is in the format John Doe? I have tried using a split string ...
3
votes
1answer
2k views

error in Execute SQL Task with parameter

I have an Execute SQL Task object in the Error handlers section of a SSIS package. What it does is inserting a record to a table using OLE DB connection. There is a value in the insert statement that ...
4
votes
3answers
159 views

Compare 4 or more columns values

I have a table like: ID A0 A1 A2 A3 A4 14 A B A C A 15 A A A A A I need to compare all 5 columns and check if they have same value and retrive the only one that have all same ...
3
votes
2answers
44 views

Is there a less manual way of changing database properties for all databases in an instance?

I've recently audited a SharePoint server and found configurations that are against best practice. I'm hoping there is better and quicker way than right-clicking and changing these settings manually ...
-3
votes
1answer
7k views

What is the Difference between SQL Server 2008R2 and SQL Server 2014?

I want to take my project from SQL Server 2008 R2 to SQL Server 2014. But I want to learn what is T-SQL the difference between 2008 R2 and 2014 for Database Engine? I mean is there any end of life ...
0
votes
1answer
33 views

SQLcmd error 'Failed to initialize sqlcmd library with error number -2147467259'

I'm trying to update some maintenance plans to make use of the Ola Hallengrens IndexOptimize, but also mail the result of the operation using sp_send_dbmail. Old T-SQL: declare @indexQuery ...
1
vote
1answer
85 views

Sql Server Server Group query

Scenario I have 4 front-end sql server. All of them have the same db instances. Data are replicated from back-end databases. Issue In SQL Management Studio I have registered the servers and I have ...
0
votes
2answers
27 views

'Use <database> ' statement not executing T-SQL

Okay, this seems funny for a simple script: but I really need this small piece to execute well. DECLARE @dbName NVARCHAR(50) SET @dbName = 'rx_lab' DECLARE @SQL02 NVARCHAR(MAX) SET @SQL02 = 'USE ['+ ...
1
vote
0answers
15 views

Publishing Database Changes With SSDT that include Adding a New Column [closed]

Similar to Publishing database changes with SSDT that include altering data types of columns But I have the issue when I'm adding a new column to my database. I tried publishing incrementally by ...
1
vote
1answer
78 views

Joining multiple tables results in duplicate rows

I am running SQL 2005. I am getting more rows then I expect returned by my query. I believe it has something to do with my join statements. Here is my query: There are multiple tables that have ...
1
vote
1answer
404 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 ...
3
votes
3answers
128 views

T-SQL - How to split (char separated) string into rows and columns

What is the best way to split a char separated string into rows and columns? Here is my input string: !1;100;10;200;0;500;2;1000;30!2;100;3;500;1;2000;5 And here is my desired output table: Id ...
2
votes
1answer
60 views

Wrong results from joining two CTEs

I've had some help to create a query to solve a problem I had and the solution seemed to be to create two CTE's and then join and query them. If I run the two CTE's as single queries each table ...
2
votes
1answer
15 views

Retreive a Customized List with input data

I have a database that has about 223 tables and I need to locate the name of the constraint. My request is to add a parameter that is the name of the constraint and you retrieve a list that shows the ...
2
votes
3answers
214 views

How to use “BACKUP DATABASE”, but the database is in a different server.? [closed]

We have 3 servers ( SQL SERVER 2008 ). Is there a way to backup a database, connected in a different server? Example: i'm on SQL1. I want to do something like this: BACKUP DATABASE ...
0
votes
1answer
41 views

how to extract current year and combine with specific month and date as a date

I have to get the current year with June the 16th as a date. For example, if I run the query now I want to get 2015-06-16. I tried the following: CONVERT(DATETIME,cast( (cast(YEAR(GetDate()) AS ...
2
votes
1answer
121 views

Arithmetic overflow error varchar to numeric

I'm getting an arithmetic overflow error and I can't seem to find out the case. I've recently had to adjust some stored procedures, to allow for dates in the future to be put in. The following part ...
9
votes
2answers
5k views

How to optimise T-SQL query using Execution Plan

I have a SQL query that I have spent the past two days trying to optimise using trial-and-error and the execution plan, but to no avail. Please forgive me for doing this but I will post the entire ...
3
votes
1answer
62 views

Decode and aggregate a BitMask in t-sql

I have a table that contains a bitmask field that stores permissions, where each bit denotes whether a particular permission is granted or not. Here's a simplified example: DECLARE @T TABLE (id ...
-2
votes
2answers
46 views

SQL Server failed stored procedures log [closed]

How to find errors that was occurred in stored procedures in some timespan in Sql server management studio 2008 ?
0
votes
1answer
55 views

Some records are missing while selecting from table

I am trying to match the records having MAX(WORKITEM_ID) but some PROCESS_INSTANCE_ID are not coming though they are present in WORKITEM table. select WI.WORKITEM_ID as WORKITEMID, ...
0
votes
4answers
40 views

The multi-part identifier > “a.surveyid” could not be bound [closed]

I am getting the error : Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "a.surveyid" could not be bound. Below is the query ,what could be the issue? SELECT a.surveyid,* ...
1
vote
0answers
49 views

SQL Server - Project based on the TDS protocol [closed]

From what I understand, TDS is an application level request/response protocol used for communication between a client and SQL server. Now, I'm in the process of finding a master thesis (computer ...
2
votes
1answer
523 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 ...
0
votes
1answer
55 views

How to modify maintenance plan using T-SQL script in SQL Server 2008

How can I modify a maintenance plan in SQL Server 2008 using a T-SQL script? I want to change the schedule of a maintenance plan and change the path of the .bak file . All these changes should be ...
0
votes
1answer
50 views

Which columns (and order) to choose for a clustered index to maximize query performance

In our database we have a table that looks more or less like the example below. In the past we always created the clustered index on the ID column on the table. CREATE TABLE [Measurement] ( [ID] ...
1
vote
1answer
26 views

DBCC DBREINDEX in MS SQL Server 2008

We have done DBCC DBREINDEX for our production database which size is about 300 gb, it was running for 12+ hours, but i found that DBCC DBREINDEX is obsolete and recommended approach is using ALTER ...
1
vote
0answers
550 views

time interval period in sql [closed]

How to display the time period of the most widely in use in ms sql server I have purchase data, such as: hours the number of purchases 10:00 2 12:00 4 13:00 5 14:00 1 ...
4
votes
1answer
135 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?
1
vote
0answers
57 views

How to speed up for huge tables in SQL select query?

I have the following huge tables. Table_1 with 500000 (0.5m) rows Table_8 with 20000000 (20m) rows Table_13 with 4000000 (4m) rows Table_6 with 500000 (0.5m) rows Table_15 with 200000 (0.2m) rows ...
2
votes
1answer
301 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 ...
1
vote
2answers
54 views

How can I Insert when not in table and when max timestamp T-SQL

I am trying to insert rows from one table to another that are not in the one I am moving them to. I also want to only move the ones that have the highest datestamp. (I want to only insert rows that ...
0
votes
1answer
52 views

How to store a version sequence per product

I need to create a table like the following one: +----+---------+---------+ | Id | Product | Version | +----+---------+---------+ | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 2 ...
0
votes
1answer
36 views

Script works but Im not sure this is the best format

I have a survey table which contains mostly integer. I want to make it readable for end user so I use select statement as below: SELECT CASE s.Country WHEN 1 THEN 'One' WHEN 2 THEN 'Two' ...
1
vote
0answers
27 views

Certification Test 70-461 [closed]

I am a student at University of Houston-Downtown and looking to take the Database Administrator certification test sometime in October. I am hoping that someone has been recently been certified or ...
3
votes
1answer
569 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 ...
5
votes
2answers
454 views

Symmetric difference operation in Transact-SQL?

I have always known about the UNION operator in SQL, but only recently discovered that there were other set operators, INTERSECT and EXCEPT. I haven't been able to find an operator that does the ...
11
votes
2answers
455 views

Help with PIVOT query

I have a table with below structure : CREATE TABLE [dbo].[AUDIT_SCHEMA_VERSION]( [SCHEMA_VER_MAJOR] [int] NOT NULL, [SCHEMA_VER_MINOR] [int] NOT NULL, [SCHEMA_VER_SUB] [int] NOT NULL, ...
1
vote
3answers
75 views

Can you Schedule a job inside another job?

I have many jobs that need to run but they cant run at the same time. the first step of each checks to see if any others are running if yes it waits a little bit and checks again. This can lead to a ...