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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Matt Smith - 29 Apr 2005 16:01 GMT

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?

Reply to this Message

Alex White MCDBA MCSE - 29 Apr 2005 16:14 GMT

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.

Signature

Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

>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
[quoted text clipped - 15 lines]
> for the table creating SELECT statement, or is there some way to get the
> .ADP to return the final rowset?

Reply to this Message

Sylvain Lafontaine - 29 Apr 2005 16:34 GMT

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.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC

Reply to this Message

Matt Smith - 29 Apr 2005 18:59 GMT

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.

> You must use global temporary tables, with two #, not local temporary
> tables. See http://support.microsoft.com/kb/q232379/ for exemple. Of
[quoted text clipped - 24 lines]
> > for the table creating SELECT statement, or is there some way to get the
> > .ADP to return the final rowset?

Reply to this Message

Vadim Rapp - 30 Apr 2005 04:38 GMT

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

Reply to this Message