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;
...
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...
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...
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 i use if else under a check constraint.
Can i use check constraint using a variable
need xplanation with eg.
...
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...
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.
...
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 ...
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...
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
...
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...
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...
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...
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...
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...
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--...
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...
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.
...
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 = @...
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...