tsql

SQL:Placing Column values in variables using single query

In SQL server,How can i place the value of more than one column in variables using one query Ex : My query is : SELECT ET.ID,ET.Description,ET.DefaultTemplateText FROM TBL_EMAILTEMPLATE ET WHERE ET.NAME='OneWeekReminder' I want to place the Column values in variables. Thanks in advance for the help ...

Correct escaping of delimited identifers in SQL Server without using QUOTENAME

Is there anything else that the code must do to sanitize identifiers (table, view, column) other than to wrap them in double quotation marks and "double up" double quotation marks present in the identifier name? References would be appreciated. I have inherited a code base that has a custom object-relational mapping (ORM) system. SQL ...

DBCC SHRINKFILE 1 sproc for multiple databases

I have a need to execute DBCC SHRINKFILE for multiple db's within the same sproc. I could create multiple sprocs so it runs within the given context, but I was curious if there were alternatives? ...

How to put foreign key constraints on a computed fields in sql server?

Table A has a computed field called Computed1. It's persisted and not null. Also, it always computes to an expression which is char(50). It's also unique and has a unique key constraint on it. Table B has a field RefersToComputed1, which should refer to a valid Computed1 value. Trying to create a foreign key constraint on B's RefersT...

Impact of ordering of correlated subqueries within a projection

I'm noticing something a bit unexpected with how SQL Server (SQL Server 2008 in this case) treats correlated subqueries within a select statement. My assumption was that a query plan should not be affected by the mere order in which subqueries (or columns, for that matter) are written within the projection clause of the select statement...

SQL IN Statement using like syntax?

I would like to do something like this i.e., use wild card characters in the in clause: SELECT * FROM mytable WHERE keywords IN ('%test%', '%testing%') This is not supported in SQL Server.... Is there some other way to achieve it... Looking for something other than: SELECT * FROM mytable WHERE keywords like '%test%' or keywords like...

How to reference a sql server with a backslash (\) in its name?

Givens: One SQL Server is named: DevServerA Another is named: DevServerB\2K5 Problem: From DevServerA, how can I write a query that references DevServerB\2K5? I tried a sample, dummy query (running it from DevServerA): SELECT TOP 1 * FROM DevServerB\2K5.master.sys.tables And I get the error: Msg 102, Level 15, State 1, Line...

SQL query optimization

I have a problem with my SQL query that take time to get all records from database. Any body help me. Below is a sample of database: order(order_id, order_nm) customer(customer_id, customer_nm) orderDetail(orderDetail_id, order_id, orderDate, customer_id, Comment) I want to get latest customer and order detail information. Here is m...

T-sql Common expression query as subquery

I have the following query: WITH Orders(Id) AS ( SELECT DISTINCT anfrageid FROM MPHotlineAnfrageAnhang ) SELECT Id, ( SELECT CONVERT(VARCHAR(255),anfragetext) + ' | ' FROM MPHotlineAnfrageAnhang WHERE anfrageid = Id ORDER BY anfrageid, erstelltam FOR XML PATH('') ) AS Descriptions FROM Orders Its concatenates varchar values of dif...

Dynamic SQL to generate column names?

I have a query where I'm trying pivot row values into column names and currently I'm using SUM(Case...) As 'ColumnName' statements, like so: SELECT SKU1, SUM(Case When Sku2=157 Then Quantity Else 0 End) As '157', SUM(Case When Sku2=158 Then Quantity Else 0 End) As '158', SUM(Case When Sku2=167 Then Quantity Else 0 End) As '167' FROM Ord...

What is happening in this T-SQL code? (Concatenting the results of a SELECT statement)

I'm just starting to learn T-SQL and could use some help in understanding what's going on in a particular block of code. I modified some code in an answer I received in a previous question, and here is the code in question: DECLARE @column_list AS varchar(max) SELECT @column_list = COALESCE(@column_list, ',') + 'SUM(Case When Sku...

How can I do a left outer join where both tables have a where clause?

Here's the scenario: I have 2 tables: CREATE TABLE dbo.API_User ( id int NOT NULL, name nvarchar(255) NOT NULL, authorization_key varchar(255) NOT NULL, is_active bit NOT NULL ) ON [PRIMARY] CREATE TABLE dbo.Single_Sign_On_User ( id int NOT NULL IDENTITY (1, 1), API_User_id int NOT NULL, extern...

SQL Server 2008 Dead lock issue

I got into a deadlock issue where I am struggling find the root-cause...The Deadlock graph suggests that an UPDATE statement became the victim over a SELECT statement... What puzzles me is that the UPDATE statement is trying to acquire an index on some other table that is never referred in update statement... This is how my UPDATE state...

T-SQL String Functions: difference between using Left/Right and Substring and strange behaviour

I am using SQL Server 2008 & 2005 (Express). I'm trying to extract part of an alpha numeric string from a varchar field. RIGHT(str_field, 3) yields null values but SUBSTRING(str_field, LEN(str_field)-2, LEN(str_field)) gives the right value. LEFT(str_field, 7) gives the expected values. What gives? I would have thought that RIGHT(st...

T-SQL selecting values that match ISNUMERIC and also are within a specified range. (plus Linq-to-sql)

I am trying to select rows from a table where one of the (NVARCHAR) columns is within a numeric range. SELECT ID, Value FROM Data WHERE ISNUMERIC(Value) = 1 AND CONVERT(FLOAT, Value) < 66.6 Unfortunately as part of the SQL spec the AND clauses don't have to short circuit (and don't on MSSQL Server EE 2008). More info: http://stackover...

sql query to get the sql server 2005

i have table with filmname and actors column in sql server 2005 i want the sql query to get all the actors of the film starred by both "bradpitt" and "rusellcrowe" the table design is as follows CREATE TABLE [dbo].[mytable]( [actors ] [nchar](10) NULL, [filmname] [nchar](10) NULL, ) ON [PRIMARY] ...

set difference in SQL query

I'm trying to select records with a statement SELECT * FROM A WHERE LEFT(B, 5) IN (SELECT * FROM (SELECT LEFT(A.B,5), COUNT(DISTINCT A.C) c_count FROM A GROUP BY LEFT(B,5) ) p1 WHERE p1.c_count = 1 ) AND C IN (SELECT * FROM (SELECT A.C , COUNT(DISTINCT L...

Ordering of month/year pairs in T-SQL query

I am writing a stored procedure for displaying month and year. It is working, but it is not returning the rows in the desired order. ALTER procedure [dbo].[audioblog_getarchivedates] as begin select DateName(Month,a.createddate) + ' ' + DateName(Year,a.createddate) as ArchiveDate from audio_blog a group by DateName(Month,a.creat...

How to insert into a table with just one IDENTITY column (SQL Server CE)

Hello I am trying to insert a value in a one IDENTITY column Table in SQL Server CE 3.5. I Tried the following: INSERT Target DEFAULT VALUES INSERT Target (ID) VALUES (DEFAULT) INSERT Target (ID) VALUES () But none of them worked. This is the SQL command I used to create the table (Using SQL Server Management Studio): CREATE TABLE T...

SQL-query task, decision?

There is a table of currencies rates in MS SQL Server 2005: ID | CURR | RATE | DATE 1   | USD   | 30      | 01.10.2010 3   | GBP   | 45      | 07.10.2010 5   | USD   | 31      | 08.10.2010 7   | GBP   | 46      | 09.10.2010 9   | USD   | 32      | 12.10.2010 11 | GBP   | 48      | 03.10.2010 Rate are updated in real time and there are ...