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

learn more… | top users | synonyms (1)

0
votes
2answers
39 views

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

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
29 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
37 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
1answer
30 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
180 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 ...
12
votes
3answers
1k 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
0answers
16 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
312 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
62 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
61 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
54 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
56 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
79 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 ...
0
votes
0answers
37 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. ...
0
votes
3answers
95 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
40 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 ?
4
votes
1answer
135 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
41 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
78 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
34 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
42 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
52 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
72 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
32 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
28 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
428 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
39 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
141 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
58 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
816 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
241 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
110 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
41 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 ...
1
vote
2answers
38 views

Excluding Null Values

In my syntax why does the NOT IN statement not exclude NULL values? To me, if NULL does not exist in #GoodAnswers it should return the dv Below is syntax, can someone help explain this to me? ...
2
votes
1answer
124 views

Stored procedure suddenly very slow, updating statistics not helping

Like the title says, I have a stored procedure that suddenly started running very slowly. Previously it took about 5 seconds to execute, now it is taking seven minutes or more. This seems to have ...
2
votes
2answers
38 views

Trying to drop database only if it does not exist by using the db_id function fails

When the database foo is not yet created and I I run this IF db_id(N'foo') IS NOT NULL BEGIN ALTER DATABASE [foo] SET OFFLINE GO DROP DATABASE [foo] GO END I get a message Cannot drop ...
2
votes
2answers
92 views

DBCC CHECKDB LastKnownGood

I created the following query to list the DBCC CHECKDB LastKnownGood timestamp for all Databases on a Server. Is there a better way to do this? IF OBJECT_ID(N'tempdb..#Results') IS NOT NULL BEGIN ...
0
votes
1answer
22 views

Accepted Values Stored in Variable

I am trying to write a re-usable query and this is pseudo-code of what I am after. Is this possible to do in Sql Server 2008 or do I just need to write a different stored procedure for each ...
7
votes
4answers
472 views

How to get response from stored procedure before it finishes?

I need to return partial result (as simple select) from a Stored procedure before it is finished. Is it possible to do that? If yes, how to do that? If not, any workaround? EDIT: I have several ...
1
vote
2answers
363 views

Re-Usable Query

I have 3 tables with the exact same DDL (I know that is a horrid set-up), but each one has a different values for a field. I am hoping to be able to have a way to write ONE query for all 3 selects as ...
2
votes
1answer
146 views

How to remove the WITH ENCRYPTION from the code of the procedure - via T-SQL [duplicate]

I have many procedures that I use the option WITH ENCRYPTION when I create them. You can see this on the picture below, as an example: the problem with this is that when I want to save my previous ...
1
vote
2answers
106 views

SQL Query Optimization

My SQL Server version is Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53 and the query below just runs without producing an error or updating. The table [dbo].[...
3
votes
1answer
127 views

Dissecting Joins

Recently I asked a question in dba.stackexchange the question was about data hierarchy in sql and a guy named chris allen answered the question and I really loved it but when I try to understand that ...
1
vote
2answers
113 views

Must Declare Scalar Variable Error

My syntax keeps giving me the below error, which is blowing my mind as I think (and please kindly correct me if I am incorrect), I have declared and set this variable above. Msg 137, Level 15, ...
1
vote
2answers
30 views

Getting the error log location in an efficient way

I am using this sql to get my error log location: EXEC xp_readerrorlog 0, 1, N'Logging SQL Server messages in file' However, sometimes I have very large log file (about 1gb, and I do not have ...
12
votes
2answers
1k views

Hyphen in my computer's name causing T-SQL error

I’m a developer, not a DBA (which is showing, I’m afraid). I am trying to run Report Builder 3.0 with SQL Server 2014 Express on my home computer (named John-PC) and cannot run my reports. I ...
1
vote
2answers
71 views

Why does formatting a time with AM/PM specifier return null?

I have a query where a formatted expression is returning null: declare @FormatString nvarchar(max) = N'hh:mm tt' select M_STARTTIME as [Memo Time 1] , convert(time,M_STARTTIME) as [Memo Time 2] ...
1
vote
1answer
74 views

How to find statement executed by sp_executesql in [sys].[dm_exec_query_stats]?

I have a function which is generating dynamic T-SQL statement. The generated code is executed then in a stored procedure. I want to log some statistics about how long the execution of the generated ...
1
vote
1answer
21 views

How does SQL Server determine the expression type when mixing Unicode and non-Unicode strings?

When you say a + b in T-SQL and a and b are of a string type (e.g. varchar(...) or nvarchar(...)): How is the resulting type determined? I found no clear rules for this when experimenting. It seems ...