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
1answer
77 views

How to dynamically generate rows based on value in a column

I'm attempting to write a query to denormalize some data for integrating to our data warehouse, specifically around generating some time slots. In my source system, I have table that has a start time ...
0
votes
1answer
66 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
49 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
62 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
34 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
148 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
107 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 ...
1
vote
2answers
138 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 ...
3
votes
1answer
78 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 ...
5
votes
1answer
399 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 ...
5
votes
4answers
16k views

Writing select result to a csv file

We need to write the SELECT query results to a csv file. How can it be done using T-SQL in SQL Server 2008 r2? I know that it can be done in SSIS, but for some reasons, we don't have this option. I ...
2
votes
1answer
612 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 ...
2
votes
1answer
30 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!
3
votes
2answers
148 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
72 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 ...
2
votes
1answer
117 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 ...
3
votes
1answer
82 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
52 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 ...
0
votes
1answer
898 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 ...
0
votes
1answer
185 views

Tips for using DML Statements [closed]

I've recently began learning T-SQL and have found that there are some rules to using single table queries. For instance: GROUP BY If aggregate and non-aggregate expressions are used together in a ...
1
vote
1answer
317 views

How can I create view with parameters in Sybase SQL Anywhere?

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

How to 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
1answer
194 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 ...
2
votes
1answer
60 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 ...
1
vote
2answers
1k views

Options for keeping two SQL Server Compact databases in sync?

Developer here. I have a system where there is a front machine creating rows in a moderately complex database, and a back machine that must sync with the front machine to run reports. Both run SQL ...
4
votes
2answers
100 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
48 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
113 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
55 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
100 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 ...
3
votes
4answers
8k views

Add a new Column and define its position in a table

I have Table A with 5 columns: TableA -- Name Tel Email Address I want to add a new column (mobile) in between Tel & Email: TableA -- Name Tel Mobile Email Address If I use ALTER TABLE ...
3
votes
4answers
190 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 ...
5
votes
2answers
113 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
88 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 ...
2
votes
1answer
107 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
89 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 ...
1
vote
2answers
183 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
2answers
289 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 ...
5
votes
1answer
716 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
1answer
167 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 ...
2
votes
1answer
112 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 ...
1
vote
2answers
68 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 ...
0
votes
1answer
128 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
196 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 ...
3
votes
1answer
161 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 ...
0
votes
2answers
126 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 coloumns with ...
1
vote
1answer
92 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' ...
2
votes
1answer
317 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], ...
2
votes
2answers
163 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 ...
3
votes
0answers
168 views

Mathematical method for spotting irregularity in numbers flow

I am trying to build an engine which is going to predict orders for items inside shop. Three main variables are: Time-dates (x-axe etc 1.1.2013,1.2.2013 etc I do calculation one per day) Available ...