Pass Through Query - not returning rows

[Gary Watson]

I've written some SQL, which when run on SQL Server 2005 returns rows.
Plugged this SQL into an Access 2003 pass through query, but when run it
reports an error saying pass through query with return records set to true
did not return any records.

I've got other pass through queries in this Access database that work fine.

The SQL in this query is more complicated and creates a temp table - could
this be causing the problem?

 

[Stefan Hoffmann]

hi Gary,

The SQL in this query is more complicated and creates a temp table - could
this be causing the problem?

Yes, indeed.

My crystal ball says that you're missing the mandatory NOCOUNT for
multiple result sets, test it against any database, I used tempdb:

SET NOCOUNT ON;

DECLARE @user TABLE ( username SYSNAME ) ;

INSERT INTO @user ( username )
VALUES ( SUSER_SNAME() ) ;

SELECT *
FROM @user ;


mfG
--> stefan <--