note 44868 deleted from function.mssql-execute by tomsommer

Note Submitter: tweir at woh dot rr dot com ---- Why does each connection support only one query at a time? In MS SQL 2000, you can sometimes get around this limitation by including the following statement at the top of a multi-pass query: SET NOCOUNT ON This turns off the “(x Rows Affected)” which is passed back every time you do a SELECT, UPDATE, or DELETE. For example, if you say: DECLARE @var datetime SELECT @var = (select max(invoicedate) from INVOICE_TABLE) SELECT * from INVOICE_TABLE where invoicedate = @var Your application would actually think there are two result sets – 1 for setting the variable, and one returning the invoice records – so it would actually generate an error prior to returning the invoice records. However if you say: SET NOCOUNT ON DECLARE @var datetime SELECT @var = (select max(invoicedate) from INVOICE_TABLE) SELECT * from INVOICE_TABLE where invoicedate = @var You will only have one result set passed back to the application - the invoice records you are really after. Setting a variable with a SELECT statement really does not give a result set, but returning the row count to the application makes the application think there is one. Trevor D. Weir DBA, CareSource mailto:tweir@woh.rr.com