Can ADP return data from temp tables in an SQL Stored Proc?

[Guest]

Hi all.
I have created several stored procedures on SQL server and have an Access
ADP linked to them. I wish to have a user double click on the stored procs in
Access to run them. My problem is that some of the stored procs are not
returning records to access. I can run all the stored procs in SQL Query
Analyzer without a problem. It seems that the ones that do not return records
to Access have SELECTs on temporary tables to produce the results.
Does anyone know if this is a known problem in Access?

 

[Alex Dybenko]

Hi,
perhaps you have multiple selects in you SP, and you get only finst one in
access. i think better idea to build form(s), which runs these SP, then you
can use NextRecordset method to get necessary recordset

 

[Guest]

No mutiple selects, just create a temp table and then select from it.

 

[Alex Dybenko]

Then try to add:
set nocount on
in the beginning, and then off before last select
(check books online for correct syntax)

 

[Guest]

Thanks Alex. Another good suggestion but I have that in the proc already.
I tried running it through code as a test and got a timeout error (it takes
about 1.5 minutes to run). I increased the commandtimeout property to 300 and
it ran through the code fine (I debug.printed some value of the 1st record
and the recordcount).
Now, is there a way to increase the timeout for a query (stored proc) to run?

 

[Guest]

Answered this one myself after digging around on the internet.
To increase the amount of time before a query timeout occurs choose:
TOOLS+OPTIONS+ADVANCED and increase the OLE/DDE timeout.
Sounds a little strange but I guess refreshing the datasheet in Access is a
DDE type operation.

THANKS FOR YOUR HELP!