Execute sp from Access adp don´t return any values? 1

[balor]

If I try to execute a stored procedure including an insert into a temptable the sp don’t return any values. The problem only occurs when I am executing the sp from a Access project. The sp works fine in Query Analyzer.

It works on one of the servers I’m working against but I don’t know why and I can’t get it working on the others.

Any clue?


Try this to make the error occur:

1. Create the Stored Procedure that includes an insert to a temptable.
Example:

CREATE PROCEDURE dbo.TestTempInsert
AS
--Creates the temptable
CREATE TABLE
#TestTable
(
Column1 int PRIMARY KEY,
Column2 varchar(10)
)
--Insert into the temptable
INSERT INTO
#TestTable
(
Column1,
Column2
)
VALUES
(
1,
'Test'
)
--Returns the values in the temptable
SELECT
*
FROM
#TestTable

2. Try to execute it from Query Analyzer to be sure that it works.

3. Create a Access development project (.adp) and connect it to the SQL-server database with the test sp.

4. Try to execute the sp.

5. Do you get the same error as I do? The sp don’t return any values.

 

[tlbroadbent]

Add SET NOCOUNT ON to the beginning of the stored procedure. The Insert statement generates a row of output which will be returned as a separate record set to Access. Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[balor]

Thank you Terry for solving my problem!

Do you have any thoughts about why it works on one of the servers without SET NOCOUNT ON?

 

[tlbroadbent]

It works in Query Analyzer because Query Analyzer is able to handle the outputs of all of the statements. In fact, it works in Access if your code handles multiple record sets returned from the same query. I beleive there are some articles on Microsoft.com about how to do that. Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[balor]

Ok, but it works with the exact same code from the exact same computer. If I connect to one of the servers it works, but if I connects to an other server it don´t work.

 

[tlbroadbent]

I'm not sure why it works on one machine and not the other.

Microsoft has a knowedgebase article that addresses this very issue. The article states, "If a stored procedure returns multiple result sets in its output, an Access project returns only the first result set to Datasheet view. The user interface in an Access project is not designed to return multiple result sets." (see

http://support.microsoft.com/support/kb/articles/Q232/5/62.asp)

I've been searching for possible reason - version differences, O/S differences, etc. So far, I've found nothing to explain why it runs on one PC and fails on another. All indications are it should fail on all PC's if you don't include "SET NOCOUNT ON." Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot