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

learn more… | top users | synonyms (1)

1
vote
3answers
49 views

T-SQL for Query that can be done in SQL. What do they want?

We are given tables Vendors(VendorId, VendorName, ..) , Invoices(InvoiceId, VendorId...) I am going over a book exercise that asks for a T-SQL statement to find all vendors with at least one invoice. ...
0
votes
0answers
7 views

How to Create a SubQueries to Distinct a value in two tables. Please look at details

I am a Junior Developer, I have been asked to create subQuery to solve this problem. if Question is not clear please let me know so I can provide more details. I have two tables Article Table and ...
8
votes
7answers
566 views

T-SQL : Is string a Palindrome?

I am a beginner in T-SQL. I want to decided whether an input string is a palindrome, with output =0 if it is not , output =1 if it is. I am still figuring out the syntax. I am not even getting an ...
11
votes
3answers
201 views
+250

Database stored procedure with a “preview mode”

A fairly common pattern in the database application I work with is a need to create a stored procedure for a report or utility that has a "preview mode". When such a procedure does updates, this ...
7
votes
3answers
9k views

How to changes or update local server connection in Maintenance plan job

Two days back our client changed one of our Dev Server name After Server Renamed, All my maintenance jobs and other jobs are failing because server name mismatch. We are using sql server 2012 ...
1
vote
1answer
27 views

TSQL Solution to backup ALL databases on ALL instances of a server?

I'm pretty sure what I'm wanting with my current implementation is impossible, the sys.databases catalog view which is scoped at the service level to be aware of other SQL Server instances (lol). But ...
0
votes
3answers
43 views

Minimum value within a set (or window) within a three column table

I only have SQL Server 2008R2, therefore I do not have all the window functions available since SQL Server 2012. I have a table that looks like this: I would like to retrieve a result set like ...
4
votes
1answer
43 views

How can a database parameter be used on `sp_addextendedproperty`?

I have found the MSDN definition and code examples of 'sp_addextendedproperty'. In Example A on that page, I see an example of how to add an extended property to a database. USE AdventureWorks2012; ...
7
votes
1answer
121 views

Should we still be using QUOTENAME to protect from injection attacks?

I was looking at an old stored procedure today and noticed it was using quotename on the input parameters. After doing some digging to figure out what that does exactly I came across this site. I now ...
0
votes
1answer
32 views

List of queries run

I'm looking for a DMV query for SQL Server 2008 R2 which shows me all queries (full history) run by a specific user. I have this code for now: SELECT * FROM sys.dm_exec_query_stats qs CROSS APPLY ...
5
votes
2answers
838 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 ...
0
votes
2answers
34 views

How to separate values from one column into two columns in SQL Server [on hold]

I have this dataset as input: Name Contact No ------- ---------- Deepika 75865558 Deepika 207586558 Rnveer 746655558 Rnveer 946655558 vaibhav 2356841 vaibhav 6655558 I want to get ...
0
votes
1answer
28 views

Returning only the row with maximum value from related rows

In the example below how do I get the rows where the swap id is the maximum value in relation to the reservationId? I am currently joining multiple tables to get customer, reservation, room etc ...
2
votes
1answer
24 views

msdb.dbo.sysjobs and last run date and time

I know that the last_run_date and last_run_time are stored as Int, but when coverted over to datetime (of which I have examples of), is the time GMT, or what? When converted it doesn't match the same ...
0
votes
0answers
22 views

Any table type that persists across connections

Probably not possible,but I'm going to ask this anyway: I don't have OPENROWSET privileges on server B. I would like to import data while connected to server A (where I do have that privilege) and ...
0
votes
1answer
26 views

Script to Delete Tables With A Prefix

I am running SQL Serve 2008 and we have a few test tables that were obviously used for testing. The prefix of the tables are Test_, my question is, will this script delete all tables with the prefix ...
2
votes
3answers
101 views

Return the uniqueidentifier generated by a default on insert

Goal Retrieve the latest guid value in real time after you have inserted the value in the table Problem Don't know how to do it Info The code should only specify new values for address and ...
10
votes
3answers
876 views

What does OPTION FAST in SELECT statement do?

I have done some digging on what the OPTION (FAST XXX) query hint does inside a SELECT statement and am still confused on it. According to MSDN: Specifies that the query is optimized for fast ...
0
votes
1answer
200 views

Breaking up long running query into batches runs fine for a while and then produces fatal error

I'm using a variation of this post which details using "careful batching" to delete records in batches from a table that contains 38MM records. The script below runs for a while and shows results ...
0
votes
0answers
15 views

Passing Table Variable Column Values to a TVF [duplicate]

I am trying to pass values in a table variable to a Table Valued Function. However, I get the error "The multi-part identifier 'Accounts.AccountNumber' could not be bound". Why is this? SELECT ...
11
votes
1answer
301 views

CASE expression returns wrong value when using CEILING

I've run into an issue where a CASE expression does not return what I expect. As a test, I added a decimal variable and ran the same CASE expression against it and it works fine, returning the ...
1
vote
3answers
44 views

How to replace column if not null in select statement?

I have a Contact (ID, FirstName, LastName, MiddleName). For example: | ID | FirstName | LastName | MiddleName | |====|===========|==========|============| | 1 | Mark |Jordan | E | ...
1
vote
1answer
48 views

OFFSET FETCH in SQL Server 2008

I have this query to paginate the results and it was working fine on SQL Server 2012. However i had to move my database to SQL Server 2008 and now my store procedure is not working. I did some ...
2
votes
1answer
52 views

Predicate re-write in where clause

How can I rewrite where col % 5 = 4 in SQL Server. The reason to rewrite this is because I am selecting some columns from the table based on this condition. Because of % used in where it is causing ...
0
votes
1answer
41 views

Scalar UDF causing performance issues in SQL Server

I have a scalar UDF doing some string manipulations. When I use it in select clause it causes performance issues. I read that if we do not query tables or views in scalar functions it won't affect ...
1
vote
3answers
75 views

Remove duplicates in SQL Server Group By

I have 2 tables, Product and Type, the tables looks something like this: Product -Id -Name -TypeId Type -Id -Name I want to be able to write a SQL Server query which outputs the products grouped ...
7
votes
4answers
1k views

'Id' with the format: YYYYNNNNNN with the NNNNNN part restarting each year

I have a a business requirement that each record in the Invoice table has an id which looks like YYYYNNNNNN. The NNNNNN part needs to restart at the beginning of each year. So the first row entered ...
0
votes
0answers
29 views

NEXT VALUE FOR SEQUENCE inside a CASE Statement

I want to use a new SEQUENCE each year (See here: 'Id' with the format: YYYYNNNNNN with the NNNNNN part restarting each year ) However you can not use NEXT VALUE FOR inside a CASE statement. ...
2
votes
2answers
668 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
3answers
79 views

Select distinct value from row if condition exists

I am new to SQL and I am having trouble constructing a SELECT statement. What I would like to do is select distinct parts that have a location of A1 if it exists. If a part has no locations of A1 I ...
0
votes
1answer
38 views

Is there anyway to tell when the last query was made against a database? [duplicate]

Is there anyway to tell when the last query was made against a SQL 2008 R2 database without downloading a third-party tool ?
1
vote
1answer
217 views

Get the old version of stored procedure code in sql server

We have modified one of our stored procedures code and accidentally committed it in production DB also. Now we need the older version of the code. Is there any way to get the old version of a stored ...
4
votes
1answer
127 views

Cross Apply Fails When DateTime Added

In trying to achieve a Pivot-ed goal for a two row data table into one row, my first thought was to use a Cross Apply. By using the Cross Apply one can identify/generate each of the rows with a ...
1
vote
1answer
37 views

Grouping by more than 1 column using Partion By or any other method - Sql Server

I have the following tables in my SQL Server database: Product -Id -Name -Price -TypeId (Foreign Key) -SubTypeId (Foreign Key) Type -Id -Name SubType -Id -Name -TypeId (Foreign Key) I currently ...
2
votes
3answers
68 views

TSQL for “Ignore databases were the state is not online”

I have a few custom jobs on a mirrored SQL Server 2005 and they fail from time to time because of automatic failover. Is there some T-SQL code that I can write into my automated jobs or do I just ...
1
vote
1answer
31 views

Monitoring decimal values on Perfmon using User Settables

I am currently using sp_user_counter1 to store database information on Perfmon, which can be recovered by AWS CloudWatch, but the values are Decimal, and the sp_user_counter1 parameter supplied must ...
1
vote
1answer
40 views

How to synonymize a database name?

Within one database instance, I need to restore a set databases into new names. The new names will be suffixed based on the development phases, like: _DEV and _QA. Where: DBApp_DEV shall access ...
0
votes
2answers
45 views

Need help with this backup audit SQL Server script

I am using below backup script taken from http://www.ludovicocaldara.net/dba/sql-server-last-database-backup/ link to audit my servers for backups. This works great except when I have spilt backups. ...
4
votes
1answer
69 views

Get a listing of snapshot data files

I need to programmatically drop a database with all of its snapshots and then recreate the database and create a new snapshot. I'm having trouble locating where the snapshot files are located from ...
0
votes
0answers
31 views

Run Select Statement Based Off Data In Field

Below is sample DDL of what I am trying to accomplish. What I need to happen is if region = 1 only pull data for PP or if region = 2 then only pull data for CC How would I set that up in my query? ...
1
vote
0answers
23 views

Matching table (with sub-tables) to another table(with sub-tables) efficiently

I have a problem in an ETL process. I need to integrate data into a data warehouse. The data is associated with a "variant". A variant is defined as having a specific set of parameters (identified by ...
5
votes
2answers
423 views

Custom ID Numbering - fast way on insert?

As start: this is for an ETL stored procedure that is serialized, so paralellism is not of concern. I need to assign custom ID numbers for markers during loads. I can not use an identity field, ...
4
votes
2answers
1k 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 | ... | ...
0
votes
1answer
27 views

TSQL LDAP query via Linked Server

I have strange problem, I have 3 SQL Server 2012 with Sp1 instances, and on all of them, the versions are identical. I am trying to query Active Directory services, on 2 of them this is working ...
2
votes
2answers
86 views

Using DateTime Variable In Where Clause

I am trying to pull all data where a sale date is >= to a certain datetime. When I execute my query I get an error of: Msg 241, Level 16, State 1, Line 15 Conversion failed when converting date ...
-5
votes
1answer
53 views

can anyone please tell me what's wrong in this query [closed]

update ch1380.ComplexMiscLogic set logic = 'UNIDDisposition IN ('1','2','5')' where misclogic = 11 An error is showing incorrect syntax on line one of the above update statement. Please can ...
5
votes
1answer
718 views

Get minimal logging when loading data into temporary tables

Even after reading The Data Loading Performance Guide , I am still unsure if its necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index in order to get ...
2
votes
2answers
218 views

Incorrect syntax generating XML

I am trying to run below code in SQL server 2000 SP4 but getting error. Declare @Body varchar(8000); Declare @TableHead varchar(8000); Declare @TableTail varchar(8000); Set NoCount On; Set ...
3
votes
2answers
95 views

Transaction in a stored procedure

I need to perform an UPDATE and an INSERT in a single transaction. That code works fine on its own, but I'd like to be able to call it easily and pass in the required parameters. When I try to nest ...
1
vote
1answer
39 views

Return Number Not String In Dynamic SQL

This is my SQL syntax and (this is only a snippet unfortunately I am stuck running dynamic SQL in this instance) - but my issue is at the very end of my statement when I do the Select @numreturned ...