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

learn more… | top users | synonyms

1
vote
2answers
1k views

semaphore in SQL Server

I wrote a stored procedure in sql to take a number from table. it has lots on records which has only column. whenever a call it , is should take the first record and return it after that delete the ...
1
vote
0answers
319 views

SQL command in stored procedure continue on error

To perform Checkdb on all database of server i am using a stored procedure which loops over a view which contains list of databases in server and the stored procedure performs checkdb on all databases ...
0
votes
1answer
131 views

SQL Case Sensitive Query

I have two databases which return different results based on the case of the query. On the first database I run the query FROM [Temp].[Team].[CLASS] WHERE [TABLE_NAME] like '%Link%' and on ...
1
vote
1answer
37 views

Case when missing line logic

I have a case when statement in a extract query which doesn't seem to be returning what i'm expecting DECLARE @Val1 int, @Val2 int,@Val3 int, @Val4 int, @Val5 int SELECT @Val1 = 5 , @Val2= 2,@Val3= ...
5
votes
1answer
556 views

How to avoid a race condition with cross db proc calls? and other concerns

I am not even sure this question is necessary but I am curious to know everyone's thoughts. I have two databases on the same server, dbFoo, dbBar. dbFoo has the following table please note this is a ...
3
votes
2answers
340 views

Multicolumn statistics on large table yielding poor estimations

I have a rather large table (~200 million rows), and although the statistics are up to date using WITH FULLSCAN, is it possible that my histogram (limited to 200 steps) is simply too broad for the ...
4
votes
1answer
3k views

Mapping of multiple server logins to one database user

This seems to be a stupid question but despite some research I was unable to find any information regarding this only (possibly due to using the wrong terminology). Is it possible to log multiple ...
1
vote
2answers
2k views

Expose temp tables to SSRS in stored procedure

Problem: Compare data of a given date range to the same data of up to 3 previous years, and display the tabular data and a graph in an SSRS report. However currently, my stored procedure that I have ...
2
votes
1answer
45 views

What is the difference between node types and atomic types in XML

Im Studying TSQL and I came to the chapter regarding XML, and here a question that I cannot find an answer: "What is the difference between node types and atomic types"? Thank you!
5
votes
1answer
727 views

Use of FROM keyword on DELETE statement

The BOL and many other sources point out: FROM Is an optional keyword that can be used between the DELETE keyword and the target table_or_view_name, or rowset_function_limited. I'm used to ...
2
votes
2answers
797 views

View scripts ran by a specific user?

I read in a stackoverflow post that you can restore scripts from the past 24 hours by using this query: Use <database> SELECT execquery.last_execution_time AS [Date Time], execsql.text AS ...
4
votes
2answers
580 views

T-SQL - Enforce Atomic Operations x Locks

I have a database model that works similar to a banking account (one table for operations, and a trigger to update the balance). I'm currently using SQL Server 2008 R2. Table OPERATIONS: VL_CREDIT ...
1
vote
1answer
88 views

TSQL Query - Efficient?

I want to output data that will display various results depending on Type and/or Quantity values. Each query calls the same table and returns the same columns. I was thinking of creating a UNION on ...
3
votes
1answer
775 views

How to replace “set rowcount”

Currently on SQL Server 2008/R2. I just read here that "set rowcount" is being deprecated in some post 2012 release: http://technet.microsoft.com/en-us/library/ms188774.aspx We have some legacy code ...
2
votes
1answer
363 views

Getting each status change in a table

I have a table that holds status changes per item. The item can go in and out of a status multiple times. ItemTable (ItemId int, Status varchar(50), DateOfChange date) I need to pull the date of ...
2
votes
1answer
395 views

SQL Server TDE stuck encryption state 4

I'm trying to create a robust script that runs backups, backs up current certificate, creates a new certificate, backs up new certificate and regenerates database encryption keys with the new ...
2
votes
1answer
964 views

View with parameters in Sybase SQL Anywhere

A view with Sybase engine cannot take parameters. Is there a way to do this?
3
votes
1answer
585 views

Get records not updated in last 30 minutes

I am trying to write a script that will get records that have not updated the table in more than 30 minutes. Example records: DNS_NAME LAST_PERF_TIME esxnu01 2013-12-24 12:10:00.000 esxnu02 ...
3
votes
1answer
514 views

Get identity value of all rows inserted

I can use Scope_Identity() to get the last identity value inserted into an identity column. But how can i get identity values of all rows which are inserted in a insert statement? Example insert ...
7
votes
1answer
5k 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 ...
4
votes
2answers
800 views

Why can't I view the SQL Server database created by a web application?

I have an ASP.NET MVC web application deployed to IIS on a Windows Server 2012 machine. When the application first starts, it will create the database and tables it needs if necessary through Entity ...
0
votes
1answer
104 views

Identity Column Behaviour After Replication

Say we have two Servers: Server A and Server B. Data is being replicated (vanilla Transactional Replication) from A to B. For one of the Tables, we have data as follows: Server A - Server B ...
2
votes
1answer
883 views

Performance issue with TOP (1) on a nullable column

I need help to identify the reason for a performance issue. Actually following query sits in a scalar-valued-function but that is not the reason for the issue since it needs the same time as query: ...
2
votes
1answer
179 views

Getting Information on active Restore in TSQL / SQL Server Management Studio

On my server a database is currently restoring according to the SQL Server Management Studio In the history SELECT * FROM MSDB..RestoreHistory WITH (nolock) WHERE destination_database_name = 'myDB' ...
1
vote
1answer
907 views

Finding rows where a column contains [ and ] characters

I need to get results where the column contains a string that includes [ ]. When I use LIKE, it takes the [] as a wildcard sequence, and doesn't work as I need. For example, I want to search through ...
2
votes
1answer
492 views

SQL Server Full-Text Search: combining proximity term and thesaurus

Scenario I am developing a full-text search over a products table. The full-text indexed field of th table contains a brief description of the product, the name of the category and the product code. ...
5
votes
2answers
225 views

In the SQL Server RESTORE statement, in what time zone is the STOPAT argument?

SQL Server can perform point-in-time restores using the STOPAT argument. In what time zone is that date and time value? Is it UTC? Is it local for the server the backup was taken? Is it local for ...
0
votes
2answers
97 views

Can I improve this query?

SELECT * FROM SameLogTable WHERE ID_Table IN (SELECT ID_Table-1 FROM SameLogTable WHERE <SameCondition>) OR ID_Table IN (SELECT ID_Table ...
3
votes
4answers
732 views

How to rollback changes made to the database by a specific user?

In mssql, is it possible to rollback changes made by a specific user to the database? For example if that user ran an update query and did not write begin transaction before, is it possible to revert ...
2
votes
1answer
367 views

Should I run a delete first before I execute index rebuild to a large table about 15 Million records

I need help for a better approach on how to approach this. Currently we have a table that is huge about 15 million records. We plan to create an index on this table however due to large size, we ...
0
votes
2answers
196 views

Using IF and other conditions in T-SQL (trying to import data from Oracle using SQL Server SSIS)

I am using SQL Server 2012 SSIS package to fetch data from Oracle and insert it into SQL Server 2012. I am doing it for the first time and every thing is fine i.e. all data from a particular table ...
0
votes
1answer
8k views

Cannot find either column “dbo” or the user-defined function or aggregate “dbo.fnDiscountPrice”, or the name is ambiguous

i have created fnDiscountPrice() that references the item id so that it will find the discount price for one item now I'm needing to find the total price of all the products with that ID. so i have a ...
1
vote
2answers
847 views

Metric to standard unit conversion in SQL Server

I am giving simplified version of my two table which are as follow. tblRecords ID unit val --- --- --- 1 KV 3 1 BAR 4 2 KV 7 2 KG 5 3 C 37 tblUnit unit ...
1
vote
1answer
275 views

To use a cursor or something else in my while loop?

From reading around the net it seems the use of cursors is frown upon but no one ever seemed to give an alternative to them. In my case I need to set row by row though the results of a select ...
5
votes
1answer
2k views

Why does SQL Server 2008 R2 insert an invalid datetime when using bulk insert?

I want to insert rows with an optional datetime value (per row) using T-SQL BULK INSERT on a SQL Server 2008 R2. A table could look like this: CREATE TABLE [dbo].[tbl_bulk_insert_datetime_issue] ( ...
1
vote
2answers
416 views

SP or query to dump “password policy” checkbox for all logins

sp_helplogins doesn't show a column for this. I need a listing of all logins, with the status of their "Enforce password policy" checkbox. This is for an auditor, and the standard practice is for ...
1
vote
2answers
1k views

Should I load the data into Temp table or keep calling it from the normal table? [closed]

I am writing a procedure and find myself using the same select statement with the same where clause a lot. Currently the table holds over 55000 rows and grows about 100 or so rows each day. The two ...
2
votes
1answer
293 views

Should you use the schema name in WHERE, ON, and other clauses?

I know it is good practice, and a performance boost, in SQL Server to prefix the table name with the schema (dbo.TableName) in the FROM clause of a SELECT statement but should you also use it on other ...
0
votes
1answer
420 views

Multibyte replace not working in SQL Server

Why does one work and the other doesn't? SELECT REPLACE(N'IAșI' ,N'ș','x') -- doesn't match (returns 'IAșI') SELECT REPLACE('IAșI' ,'ș','x') -- works (returns 'IAxI') My guess would be that in the ...
5
votes
1answer
497 views

Passing a literal ? to sp_MSforeachdb

I am trying to search for the ? character in a string, but across all dbs on a server. Since the ? mark is a placeholder for the database name, how may I format the query so it knows I mean to search ...
0
votes
2answers
463 views

SQL Server compact- Query that return 2 constant columns even if query didn't return data

Please look at the following simple query: Select CustomerID From Customers c Where c.CustomerId = "1234" I want to write similar query that does the following: Always return 2 columns with ...
1
vote
1answer
146 views

How to write this query in SQL Server Compact in more elegant way?

The query is : Select Case AStatus When 1 then 'Success' When 0 then 'Faliure' END, Case AStatus When 1 then 'AStatus' ...
3
votes
1answer
4k views

Nonclustered Index still doing index scan

I have this simple query. SELECT a.RecordID SubmissionID, a.[DateSubmitted], a.[FinalAction], a.[FinalActionReason], a.[FinalActionDate], b.GradeSubmissionListID, b.[Action], ...
0
votes
1answer
455 views

Not sure why code is failing when CONCAT_NULL_YIELDS_NULL is set to OFF

In my question over at Pull every other row, starting a 0, and concatenate them together user @AaronBertrand provided me with a chunk of code that worked fine in test but as soon as I put the ...
2
votes
2answers
637 views

Pull every other row, starting at 0, and concatenate them together

I have a temp table that will contain two columns ID and Text. The temp table will contain an unknown amount of rows but there will always be an even amount. What I need to do is select every other ...
4
votes
1answer
15k views

Split/explode comma delimited string field into SQL query

I have field id_list='1234,23,56,576,1231,567,122,87876,57553,1216' and I want to use it to search IN this field: SELECT * FROM table1 WHERE id IN (id_list) id is integer id_list is varchar/text ...
3
votes
3answers
260 views

Inserting two rows from single row

I have to import users from the usersToImport table into the userContact table. usersToImport contains telephone and e-mail information in a single row for each user, but userContact stores one kind ...
0
votes
1answer
283 views

Can this query be optimized further?

I have 100K records in my Documents table. This is my query which gives a page of 50 records in roughly 750 ms. I would like to further bring it down if possible. I am using Row_Number function to ...
3
votes
1answer
894 views

Selecting the highest value in a column?

I am dealing with a table that has 3 columns and I need a way to select the record(s) that matches a certain PrntP_PstnGrpID(s) and is the highest ChldLevel ( The PrntP_PstnGrpID could have multiple ...
3
votes
1answer
1k views

How to remove diacritics in computed persisted columns? COLLATE is non-deterministic and cannot be used

I have a function that includes: SELECT @pString = CAST(@pString AS VARCHAR(255)) COLLATE SQL_Latin1_General_Cp1251_CS_AS This is useful, for example, to remove accents in French; for example: ...