tsql

Using Multiple T-SQL Statements in ADO.NET

Scripting in management studio with T-SQL works very well, but I am having a question about ADO.NET. I am able to code a simple select statement in ado.net. How to use the set statement in ado.net ? e.g. SET @polygon = (SELECT GEOM FROM Polygons WHERE NAME = 'Area1'); SELECT NAME FROM Points WHERE @polygon.STDistance(GEOM) <= .4; ...

One T-SQL query output to multiple record sets

Don't ask for what, but i need two tables from one SQL query. Like this... Select Abc, Dgf from A and result are two tables abc 1 1 1 dgf 2 2 2 More details? Ok lets try. Now i have sp like this: SELECT a.* from ActivityView as a with (nolock) where a.WorkplaceGuid = @WorkplaceGuid SELECT b.* from ActivityView as a wi...

sql server unpivoting table

Hi, I'm having trouble trying to unpivot the following table. | operation | ...other columns... | A1 | B1 | C1 | A2 | B2 | C2 | A3 | B3 | C3 | ... | into something like this... | operation | ...other columns... | AValue | BValue | CValue | Right now im doing it like this SELECT operation , ...other columns , Avalue, BValue , CVa...

UPDATE row when matching row exists in another table

I need to update a field on a table to be true only if a matching row exists in another table, for all the rows where the column is currently null in the main table. This is a description of what I want to achieve: UPDATE [LenqReloaded].[dbo].[Enquiry] A SET [ResponseLetterSent] = 1 WHERE [ResponseLetterSent] IS NULL AND EXISTS ...

Can we use "if else" in "Check" constraint in sql server

Can i use if else under a check constraint. Can i use check constraint using a variable need xplanation with eg. ...

SQL search multiple fields, return one

I have a query that needs to compare a value against multiple columns, and only return results from one. I keep getting a conversion error when it tries to convert a string argument into an integer, but I haven't been able to find a good way to circumvent this. SELECT DISTINCT CASE WHEN table_one.integer_col = CAST('argument%' AS...

Database last updated?

I'm working with SQL 2000 and I need to determine which of these databases are actually being used. Is there a SQL script I can used to tell me the last time a database was updated? Read? Etc? I Googled it, but came up empty. ...

Union and order by

Consider a table like tbl_ranks -------------------------------- family_id | item_id | view_count -------------------------------- 1 10 101 1 11 112 1 13 109 2 21 101 2 22 112 2 23 109 3 30 101 3 31 ...

Why OPENXML only returns one element

Can somebody explain me why this T-SQL code only returns one row with the value "1"? I was expecting to get two rows ("1" and "2"). Am I missing something here? DECLARE @XMLDoc2 XML SELECT @XMLDoc2 = '<ids><id>1</id><id>2</id></ids>' DECLARE @handle2 INT EXEC sp_xml_preparedocument @handle2 OUTPUT, @XMLDoc2 SELECT * FROM OPENXML (@ha...

Why doesn’t Client-initiated Ado.Net transaction allow…?

Hi If we’re using client-initiated Ado.Net transactions, then when we try to execute a command that is not a part of current transaction while the transaction is underway, we’ll receive an error. Why is that? thanx ...

In TSQL, what is the best way to iterate through 1..* child nodes of XML data and retreive values?

I have a simple XML structure: <Receipt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" ReceiptID="0" UserID="0" UserCardID="0" PaymentMethodID="1" MerchantID="0" MerchantTaxID="MERCHANT_TAX_ID" MerchantReceiptID="MERCHANT_RECEIPT_ID" MerchantReceiptReferenceID="MERCHANT_RECEIPT_REF_I...

TSQL in SQL 2005: Query

Hi there I have 3 tables: Customer, CustomerTypes, CustomerCustomerTypes. CustomerCustomerTypes is basically is a bridge table between the Customer and CustomerTypes. Table structure: Customers: CustomerID CustomerName CustomerTypes: CustomerTypeID CusctomerTypeName CustomerCustomerTypeID CustomerID CustomerTypeID Sample Data: Custo...

String padding in tsql

I print out a bunch of DDL statements that are dynamically created and want to align the output in a specific way. PRINT 'ALTER TABLE ' + @TableName + ' WITH NOCHECK ADD CONSTRAINT CK_' + @TableName + '_' + @ColumnName + '_MinimumLength CHECK (LEN(' + @ColumnName + ') > 0)' Output: ALTER TABLE SignType ADD CONSTRAINT CK_SignType_Desc...

XPath to fetch SQL XML value

I am looking for a good intro (not a book!) to XPath with SQL Server 2005. Anyone has a link for me? P.S. I don't really want to learn it (yet), I just want to fix my problem now :) So something with a bunch of examples would be useful. Thanks. Edit: Ok. Here is my problem: From the following XML that is within a column, I want to kn...

SQL query to return top X sequential descending rows, by group, for a particular value..

I need a query to return, by group, a true or false if the most recent x number of sequential rows, in descending date order, have a column with a false value where x can be different for each group. For example, a Configuration table would have the number of records that have to match sequentially by companyId and serviceId: CompanyI...

Query to get the duration and details from a table

Hi: I have a scenario and not quite sure how to query it. As a sample, I have following table structure and want to get the history of the action for bus: ID-----TIME---------BUSID----OPID----MOVING----STOPPED----PARKED----COUNT 1------10:10:10-----101------1101-----1---------0----------0---------15 2------10:10:11-----102------1102--...

SQL Server WHERE Clause using Temporary Columns

Hi all, I have the following query, which uses a CASE statement. Is there anyway to add into the where clause WHERE IsBusinessDayFinal = 0? without using a temporary table? thanks so much! SELECT ac.DateTimeValue, CASE WHEN pc.IsBusinessDay IS NOT NULL THEN pc.IsBusinessDay ELSE ac.IsBusinessDay END AS IsB...

SQL query to list all dependant entities.

A SQL table has 100s of tables, SPs and functions. I am trying to put together a sql query that will return all the dependencies of a given set of tables. Is there a way to accomplish this using SSMS without writing queries? Updated: Simplified the question to the point. ...

Need help with a SQL query.

SELECT @Tax = SUM(QuoteItem.SalesPrice) * TOP (1) Tax.Amount FROM Tax INNER JOIN Job ON Tax.TaxId = Job.TaxId INNER JOIN Quote ON Job.JobId = Quote.JobId INNER JOIN QuoteItem INNER JOIN Room ON QuoteItem.RoomId = Room.RoomId ON Quote.QuoteId = Room.QuoteId WHERE (Room.QuoteId = @QuoteId) AND (QuoteItem.UnitId = @...

Query to get Max/Min row details for multiple fields

I have a table structure similar to the following example: DateTime V1 V2 V3 V4 10/10/10 12:10:00 71 24 33 40 10/10/10 12:00:00 75 22 44 12 10/10/10 12:30:00 44 21 44 33 10/10/10 12:20:00 80 11 88 12 With DateTime field being the unqiue and key field, I want...