Cannot show a resultset produced by a stored procedure

[Yarik]

Hello,

Let's say there is a table named "Test" and the following stored
procedure:

ALTER PROC dbo.ShowTest
AS
SELECT * FROM Test

When I do the following code in Access

Call DoCmd.OpenStoredProcedure("dbo.ShowTest", acViewNormal,
acReadOnly)

everything works as advertised: Access opens the form displaying
whatever records are there in the table Test.

Now, let's say the stored procedure needs to re-create the table from
scratch; for example:

ALTER PROC dbo.ShowTest
AS
DROP TABLE Test
SELECT 'SomeValue' AS SomeField INTO Test
SELECT * FROM Test

In this case, MS Access says that the stored procedure "...did not
return any records..."

Is there any way to make OpenStoredProcedure() able to show the
recordset returned by the last SELECT statement in this stored
procedure?

(Actually, in my very first experiment, the stored procedure declared
a TABLE variable, filled it with records, and at the very end called
SELECT * FROM @TableVariable. In that case, the call to
OpenStoredProcedure produced the same result: no records returned.
Ideally, I would like to find a workaround for that case too, but
something tells me that it's not going to work...)

Any feedback would be greatly appreciated!

Thanks,
Yarik.

 

[Sylvain Lafontaine]

Add the SET NOCOUNT ON statement at the beginning of the SP. By the way,
DoCmd.OpenStoredProcedure is some old and strange stuff, so don't expect any
miracle from using it.

 

[Yarik]

Add the SET NOCOUNT ON statement at the beginning of the SP.

Wow, works like a charm! Even with a tabular variable!!

This was the quickest and most to-the-point answer I've received in
news in a year! Thank you a lot, Sylvain!

By the way,
DoCmd.OpenStoredProcedure is some old and strange stuff, so don't expect any
miracle from using it.

Sure. Thanks for the advice!

Actually, we don't like to use neither of DoCmd's Open...() methods.
Even OpenForm() . As for OpenStoredProcedure, we use it only to
implement cheap-and-dirty reports, which are often requested by the
users on a whim and do not "survive" for too long (either get dumped
after some time or get replaced by more human-friendly, more complex
interactive reports).