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

learn more… | top users | synonyms (1)

2
votes
1answer
144 views

Powershell - SQL Server - connectionstring in loop for multiple Instances

Thisis part of a bigger script which finds DatabaseFiles on a SQL Server machine (multiple instances). Following should just return all files for 3 instances. The server is called V3000801 and ...
1
vote
1answer
76 views

Dynamic SQL in a pivot table

I was watching a video on pivot tables and got to thinking, why can't we just push in a sub query into the IN clause, instead of declaring the expected columns statically? Well, turns out you cant ...
0
votes
0answers
71 views

SYNTAX ERROR: delete from multiple tables with inner join

Sorry, but I need syntax help :( This needs to run on SQL Server 2005 + DELETE SO, ASO, ASOV, AOV FROM SYS_OPTION SO INNER JOIN ADM_SYS_OPTION ASO on SO.OPTION_ID = ASO.OPTION_ID INNER JOIN ...
3
votes
3answers
927 views

No advantage of using Cross Apply or CTE over inline sub-query

I came across a query like this: SELECT (SELECT COUNT(1) FROM Orders o WHERE i.ItemId = o.ItemId) [C] FROM Items i I changed it to following ;WITH cte_count AS ( SELECT COUNT(1) c, OrderId ...
0
votes
3answers
386 views

how to set “result to text” with a tsql statement in sp?

is there any way to set "result to text" within of SP while this execute a process? a tsql stament? Thanks.
3
votes
1answer
44 views

Execution plan for COMPUTE BY

I understand that the COMPUTE BY feature is deprecated. Out of curiosity I would like to know how the query processor executes a COMPUTE BY query. The execution plan does not contain any hint: How ...
2
votes
1answer
237 views

Update one column based on count of records of grouped value in another column

Given a table CREATE TABLE TABLE_ITEMS ( serial_num int null, item_group int null ); INSERT INTO TABLE_ITEMS VALUES (0,1), (1,1), (2,1), (3,1), (4,1), (0,2), (1,2), (2,2), (3,2), (4,2), ...
3
votes
2answers
362 views

What is the difference between Used and Allocated space?

I am learning Database operations and I have found a couple of resources online that have given me a loose understanding, but I still couldn't explain the difference myself. Explain like I am five ...
8
votes
1answer
161 views

Overcome MERGE JOIN(INDEX SCAN) with explicit single KEY value on a FOREIGN KEY

Added 7/11 The issue is deadlocks occur because of index scan during MERGE JOIN. In this case a transaction trying to get S lock on the whole index at the FK parent table, but previously another ...
0
votes
0answers
23 views

How to Backup a Database with Specific Date Range in SQL Server 2008? [duplicate]

I have a database around 160 GB size. I want to backup the database with specific date range. Example the database contains from 2013 to until today. I want to backup database from '2015-01-01 ...
3
votes
2answers
557 views

How to separate name string by the spaces in the name in T-SQL

I have a list of names, such as: Doe John James, Doe Jane and Doe Henry Albert Sr I need to split those out into the four component parts of the name (if the name has four parts) to put each of them ...
1
vote
1answer
379 views

How to do a SELECT inside of a PIVOT [duplicate]

I am trying to SELECT values from a column in my database in order to perform a PIVOT on those values without hard-coding them. SELECT * FROM (SELECT ItemType, [Status] FROM ...
1
vote
0answers
69 views

Using :Connect I keep getting msg 911 - database [dbname] does not exist

I have a two part T-SQL script, the first part runs against a database on a different server, the second part is supposed to run on the local PC. I'm using SQLCMD mode and the :Connect keyword but ...
1
vote
1answer
185 views

SET ANSI_NULLS at the database level versus setting it at the table level

I have generated schema-only script for my sql server 2008 R2, here is part of it:- USE [master] GO /****** Object: Database [****] Script Date: 03/25/2015 12:17:09 ******/ CREATE DATABASE [****] ...
5
votes
2answers
428 views

Optimize a SQL query

I have to select 2 values that were less that the given id and greater than the given id. I have tried this query but is there any better way to do it Fiddle Sql Begin declare @rootValue int ...
0
votes
1answer
30 views

Do i need “CHECK CONSTRAINT” when i am using “WITH CHECK ADD CONSTRAINT”

I am working on SQL Server 2008 R2, where I generated a schema-only database script using SQL Server Management Studio. The generated script is as follows: ALTER TABLE [dbo].[ConsoleServer] WITH ...
1
vote
2answers
292 views

how to create trigger in SQL server using two tables to find stock quantity

I have two table sales and purchase. I want to create a trigger which stores final quantity in stock table by subtracting sales table sold quantity from purchase table. There is kikkat as a name n ...
2
votes
1answer
35 views

How to do housekeep the database in sql server 2008 by specific date range?

I have database with around 160 GB size. The database contains the data is from 2013 to until today, there are 180 tables, I want to remove the records in specific date range like 2013 to 2014 in all ...
2
votes
2answers
576 views

Creating a stored procedure and adding date manually to filename

I am new to SQL administration and have been tasked with creating some nightly jobs that send an email with certain details contained within a spreadsheet and so far I have the following: EXEC ...
3
votes
0answers
77 views

Trigger/Event that fires when a Transaction finishes

I have a query that pulls from about 15 different tables. I am looking to materialize this into a table that stores it in xml/json. (To improve performance.) The problem I have is that these tables ...
6
votes
1answer
51 views

I want to add a user/login to role in another database without switching databases

I have a role / login in a 'new' database mynewdb with a role "emailsender". I have scripted a profile for sp_send_dbmail and am now trying to script adding the the login to the DatabaseMailUserRole ...
3
votes
3answers
412 views

Insert data between 30 minute time intervals

I want to divided my to my time interval depending to the start time and end time. To explain it further, let me have this example. I have this table: User Elapsed Time Start Time ...
17
votes
6answers
3k views

Index performance on ON versus WHERE

I have two tables @T1 TABLE ( Id INT, Date DATETIME ) @T2 TABLE ( Id INT, Date DATETIME ) These tables have a non-clustered index on (Id, Date) And I join these tables SELECT * ...
2
votes
2answers
149 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 ...
4
votes
2answers
72 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 ...
2
votes
2answers
922 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 ...
0
votes
1answer
320 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, ...
7
votes
3answers
607 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 ...
1
vote
1answer
42 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 ...
2
votes
1answer
181 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 ...
3
votes
1answer
42 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
60 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 ...
6
votes
4answers
353 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
58 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 ...
0
votes
1answer
686 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 ...
0
votes
2answers
56 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
70 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 ...
2
votes
1answer
69 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
17 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
314 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 ...
4
votes
3answers
8k 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 ...
0
votes
1answer
287 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 ...
1
vote
1answer
2k 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 ...
2
votes
1answer
378 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 ...
-2
votes
2answers
868 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
4answers
86 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,* ...
3
votes
1answer
188 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 ...
1
vote
0answers
66 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 ...
1
vote
1answer
155 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 ...
0
votes
1answer
68 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, ...