CODEWITHSUNDEEP
sql-serverms-access
Brennan Vincent
Brennan Vincent

Reputation: 10665

Dynamic SQL not returning results in Access pass-through query

Consider the following code.

DECLARE @sql VARCHAR(MAX)
SELECT @sql = 'SELECT * FROM caseinformation'
EXEC(@sql)

When I run this in SSMS, it displays the same results as it would had I run SELECT * FROM caseinformation. But when I run this as an Access pass-through query, I get the following error message:

Pass-through query with ReturnsRecords property set to True did not return any records.

What gives?

Upvotes: 13

Views: 12367

Answers (2)

Mark Burns
Mark Burns

Reputation: 131

This explanation is incorrect:

The statement that Access was complaining about was actually your SELECT @sql = , which looks like a select statement but does not return a record set. When you say SET NOCOUNT ON, this turns off the ReturnsRecords property that was causing Access to fail the query.

...as that is not what happens.

A closer explanation is that SQL Server can return multiple resultsets from a query or stored procedure call. These result sets are not all recordsets, and can be a mix of scalar values and rowsets. When Set NoCount is OFF, SQL Server returns, in sequence, the rowset, and then the count of records BOTH to the calling code. Since VBA is not looking for this complex mix of scalar and recordset return values, the original error results (because the scalar value is actually returned at the TOP of the result sets, i.e. FIRST).

When the Set NoCount ON is executed on SQL Server, this tells SQL Server simply not to return the count as part of the resultset. This results in Access/VBA/DAO being able to recognize the resultset as a recordset (even if it is actually a multiple recordset), and then everything just works as expected.

Upvotes: 13

Narveson
Narveson

Reputation: 1111

Add the following at the beginning of your dynamic pass-through query:

SET NoCount ON

and you will be able to use the query from MS Access.

Why this works, I'll have to leave for others to explain, but I know it does work.

Upvotes: 28

Related Questions