Problem with SQL Server2000 SQL in Access Pass Through Query

[Kay]

Hi
I use SQLServer2000 and MS Access2000
I have the below SQL query which will not work as a Access PassThrough
Query, I get the error message "Pass-Through query with ReturnsRecords
property set to True did not return any records

I have tried replacing the @COB with a date string but the pas through
query
hangs forever.
Can anyone direct me the right way?
Thanks
Kay

DECLARE @cob smalldatetime
SELECT @cob = '20060616'


SELECT field1,
field2,
field3,
@cob as report_date
FROM table1
WHERE business_date = @cob
AND field1 in (
SELECT field1
FROM table1
WHERE business_date = @cob
AND field9 = 'N'
AND field10 <> -1
GROUP BY field1)
GROUP BY field1,field2,field3

 

[Rick Brandt]

Hi
I use SQLServer2000 and MS Access2000
I have the below SQL query which will not work as a Access PassThrough
Query, I get the error message "Pass-Through query with ReturnsRecords
property set to True did not return any records

I have tried replacing the @COB with a date string but the pas through
query
hangs forever.
Can anyone direct me the right way?
Thanks
Kay

DECLARE @cob smalldatetime
SELECT @cob = '20060616'


SELECT field1,
field2,
field3,
@cob as report_date
FROM table1
WHERE business_date = @cob
AND field1 in (
SELECT field1
FROM table1
WHERE business_date = @cob
AND field9 = 'N'
AND field10 <> -1
GROUP BY field1)
GROUP BY field1,field2,field3

In a passthrough you cannot have multiple statements like you have with your
variable declaration and assignment. You get one statement that returns at most
one result set.

If you need multiple statements you need to create a Stored Procedure on the
server and just use a passthrough query to call it.