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
63 views

How to transform this result set?

I have the result set above that I stuffing into a table variable @ApptTemp, and trying to transform it into something similar to the one below. Note that of the 4 rows that have a description of ...
2
votes
3answers
59 views

Query databases on different servers

How can I create a query that will find all the databases on different servers? I have four servers, PRDSVR, UATSVR, DEVSVR, TRNSVR; with either copies of the same database on each server or ...
0
votes
0answers
84 views

SQL Server and Entity Framework

We are currently experiencing a problem with Entity Framework which generates a sp_executesql statement. This statement is below. The statement runs fine in SQL Server however through the front end ...
4
votes
2answers
171 views

Update Statement takes forever to complete [closed]

I have table around 1250000 records, and 80 columns Table Structure CREATE TABLE [dbo].[Table] ( [Column1] [int] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL, [Column2] [int] NULL, [Column3] [int] ...
0
votes
1answer
28 views

Result set to eventvwr

I'm looking for a solution to the following case: I have a trace(only captures queries above 5000ms duration, which is about 20 per hour) running in MSSQL which I can query by using: SELECT ...
3
votes
1answer
134 views

Timestamp on SSIS update on tsql

I have a couple of SSIS packages that copy the content of several tables to another db. One of the columns updated in the process is a timestamp datatype. From what I read this datatype can't be ...
3
votes
1answer
104 views

SQL Server 2008 R2: query RECENT locks, NOT current locks

The database had average lock wait time of 30 seconds for a period of about a minute. I didn't run my lock query in time to see what was happening. I can't seem to find anything that shows recent ...
0
votes
1answer
58 views

How to retrieve whether database has any tables?

Knowing to use SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' to query for a list of tables, I'm currently finding no way to retrieve a list of non-system tables only. I.e. ...
3
votes
1answer
117 views

Frequent Delete & Insert, impact on performance

In my application, we have very few updates. Most of the data gets deleted and inserted. My question is, how this will impact the performance? We are using SQL Server 2012. Table statistics (will ...
0
votes
2answers
164 views

why stored procedure performance is very high compared to plain select query

I have a table with more than 2 million rows, with needed indexes. When I run a simple query it takes so much time so I decide to stop executing it; while when I wrap the query in a stored procedure, ...
0
votes
0answers
64 views

incorrect value from SUM Function

Has anyone seen this before? When doing a simple sum on a table: select sum(column) from table where column is not null I'm receiving the data 1558.39999999999 However, investigation of the ...
2
votes
2answers
300 views

Reduce DB size - SQL Server, SSMS

One of my databases was growing in size a few gigs every day. That was very unusual since it is still in development phase. After small research i discovered that the Even Log table was almost the ...
1
vote
2answers
4k views

how to calculate difference of first row and last row fields value in each group

I have table with structure like this: +-------+------------------+ | Value | Date | +-------+------------------+ | 10 | 10/10/2010 10:00 | | 11 | 10/10/2010 10:15 | | 15 | ...
0
votes
2answers
55 views

Which one is better way to insert data? [closed]

I have two cases case create proc createEmployee @firstName varchar(255), @lastName varchar(255) = null as insert into Employee_Tbl values (@firstName, @lastName) case create proc ...
6
votes
3answers
3k views

Restrict update on certain columns. Only allow stored procedure to update those columns

I have sensitive price columns that I would like to have updated only through a stored procedure. I would like all code or manual attempts to alter values in these price columns to fail if it is not ...
2
votes
1answer
89 views

Avoiding Deadlocks

We currently have a trigger-based system in place that does the following: When records in certain tables are modified, a trigger fires that makes a field NULL in a varying number of records in ...
1
vote
1answer
126 views

Performance of dynamic SQL Server reporting queries?

I have to build an application that allows the end user to select what columns they want in their report. For this I build a dynamic T-SQL stored procedure. However, there are up to 60 columns ...
0
votes
2answers
297 views

Store output of sp_who2 in a table

I am looking for something like this, where I could store the output of sp_who2 in a table, without having to first create the table: create table test as exec sp_who2
2
votes
2answers
153 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 ...
1
vote
1answer
179 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
149 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
105 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
73 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
35 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
195 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
130 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
305 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
358 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
416 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
21k 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 ...
3
votes
1answer
1k 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
36 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
176 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
73 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
151 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
149 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
75 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
2k 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
201 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
489 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
4k 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
277 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
66 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
152 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
56 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
186 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
69 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
186 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 ...
4
votes
4answers
10k 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 ...