'The stored procedure executed successfully but did not return any records'

[Matt Smith]

I am trying to use a stored procedure within an access .ADP that first
creates a temporary table and then uses it in conjunction with other tables
to generate a record set. When I run this through Query Analyzer this seems
to generate a single rowset, however when I run in through the .ADP it
generates the message:

'The stored procedure executed successfully but did not return any records'

If I add another query at the start of the stored procedure, the .ADP will
return the rowset for it, but not the final one, leading me to believe that
the reason the original sp did not work is that the ADP is trying to use a
rowset for the query that actually creates the temporary table. Is there
any way that I can either specify that there should be no rowset returned
for the table creating SELECT statement, or is there some way to get the
..ADP to return the final rowset?

 

[Alex White MCDBA MCSE]

Hi Matt,

are you just running the stored procedure or are you calling it within a
recordset.

e.g.

currentproject.connection.execute ("EXEC myStoredProcedure")

won't return any records as there is nothing to return them into

e.g.

adoTest.open "myStoredProcedure",currentproject.connection,
cursortype,locktype

will return the results of the stored procedure to the adoTest ado.Recordset

hope that makes sense.

 

[Sylvain Lafontaine]

You must use global temporary tables, with two #, not local temporary
tables. See http://support.microsoft.com/kb/q232379/ for exemple. Of
course, this could lead to serious problem if you don't take some
precautions in a multi-users environment.

Make sure that you have a proper use of the SET NOCOUNT ON statement. If
your query is returning multiple recordset, you can also access each of them
with the .NextRecordset method of the Recordset object.

 

[Matt Smith]

Thank you. The NOCOUNT did in fact fix the problem, I had seen that used
before and did not know why it was necessary, and in fact in a lot of
situations it is apparently not.

The person who I was trying to fix this for was using this as the data
source to a form, so out of curiosity would .NextRecordset would be possible
to use? I personally like the idea of using Access as a front end for SQL
Server, but I always cringe when I actually have to do it because it seems
like Access restricts behavior in the interface to the point where it is
difficult to use a lot of times.

 

[Vadim Rapp]

MS> The person who I was trying to fix this for was using this as the data
MS> source to a form, so out of curiosity would .NextRecordset would be
MS> possible to use?

No.

MS> I personally like the idea of using Access as a front end for SQL
MS> Server, but I always cringe when I actually have to do it because it
MS> seems like Access restricts behavior in the interface to the point
MS> where it is difficult to use a lot of times.

The best results with Access are achieved when you adhere to Access
behaviour. In this case, instead of trying to make the form to display the
recordset you have built, you specify record source and let Access build its
own recordset.

Vadim Rapp