[ODBC SQL Server Wire Protocol driver] Invalid cursor state.

Using Stored Procedure as a aource of data can be a litlle bit tricky. I've created a simple mapping with Stored Procedure invoked in Sql Query of Source Qualifier:

I've created a worflow with 5 sessions - each invoking one of below stored procedures:

------> SCENARIO 1 <------

CREATE PROCEDURE sp_test_mg

AS

BEGIN

DECLARE @result TABLE (batchFile varchar(255), ClientName varchar(255))

INSERT INTO @result

SELECT TOP 10 Batchfile, ClientName FROM test_table

SELECT * FROM @result

END

GO

------> SCENARIO 2 <------

CREATE PROCEDURE sp_test_mg_cur

AS

BEGIN

DECLARE @result TABLE (batchFile varchar(255), ClientName varchar(255))

DECLARE @batchFile varchar(255), @ClientName varchar(255)

INSERT INTO @result

EXEC sp_test_mg

DECLARE test_cursor CURSOR STATIC FOR

SELECT * FROM @result;

OPEN test_cursor

FETCH NEXT FROM test_cursor INTO @batchFile, @ClientName

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @batchFile, @ClientName

FETCH NEXT FROM test_cursor INTO @batchFile, @ClientName

END

CLOSE test_cursor

DEALLOCATE test_cursor

END

GO

------> SCENARIO 3 <------

CREATE PROCEDURE sp_test_mg_tab

AS

BEGIN

SELECT TOP 10 Batchfile, ClientName FROM test_table

END

GO

------> SCENARIO 4 <------

CREATE PROCEDURE sp_test_mg_tab_var

AS

BEGIN

DECLARE @result TABLE (batchFile varchar(255), ClientName varchar(255))

INSERT INTO @result

EXEC sp_test_mg

SELECT * FROM @result

END

GO

------> SCENARIO 5 <------

CREATE PROCEDURE sp_test_mg_temp_tab

AS

BEGIN

CREATE TABLE #result (batchFile varchar(255), ClientName varchar(255))

INSERT INTO #result

EXEC sp_test_mg

SELECT * FROM #result

END

GO

Now all of executions except for solution number 3 fail with an error message: [ODBC SQL Server Wire Protocol driver] Invalid cursor state. What is the issue?

My first thought was that for some reason PowerCenter can't read from temporary structures. The 3rd scenario is the only one using real table. But the reason - and the solution is totally different: SET NOCOUNT ON!

Adding SET NOCOUNT ON to each stored proc makes everything work fine, with one little remark: in Scenario 2 (with the coursor) only one row is read.