Excel: read data from stored procedure, with great difficulty

Working with data (and information), I use Excel extensively. In my current project I use it to show results from tests. Having automated the tests I thought that reading the results from a stored procedure that actually performed the test was a good idea. It was not a good idea. My stored procedure was suddenly not usable from excel when data began to grow. I started

• Restarting Excel
  
• Restarting my computer
  
• Reducing my environment
  
• Reducing my code
  
  • This finally gave me a positive result
    

I found out that my memory table (table-valued parameter) worked fine until I started filling it with data. Stored procedures has more outputs than the actual rowsets. This creates a problem when a "read" operation reports that it has written rows (side effect). Even if the side-effect is in a "memory table" (which obviously is automatically trashed afterwards). The fix was easy:

Use "SET NOCOUNT ON" in my Interface procedure (it calls other procedures, but these does not need NOCOUNT.

Here is a simple (working standalone) sample for SQL Server 2008 and Excel 2007/2010:

<tsql>
/* Create some temp db */

CREATE
DATABASE tvfTest

GO

USE tvfTest

GO

CREATE
TYPE TTable AS
TABLE (SomeKey INT, SomeValue SYSNAME)

GO
--*/



/* Create "Internal" proc that takes tvp */

CREATE
PROC Internal @t TTABLE READONLY
AS

SET
NOCOUNT
ON
-- Not imnportant here, but removes extra noice.

SELECT SomeKey=0,SomeValue='Zero'
UNION
ALL
SELECT
*
FROM @t

GO
--*/



/* Create "Interface" proc that uses tvp and return result set */

CREATE
PROC Interface @test INT=0 AS

DECLARE @t TTable

IF @test=1

BEGIN

INSERT @t VALUES(1,'One')
-- Fails (because rowcount is returned and it indicates that write operation performed?)

END

ELSE
IF @test=2

BEGIN
-- Same as above, but "SET NOCOUNT ON" helps avoiding error!

SET
NOCOUNT
ON

INSERT @t VALUES(1,'One')

END

EXEC Internal
@t

GO
--*/



/* Call with tvp and test */

EXEC Interface
0 -- 0,Zero -- Command(s) completed successfully.

EXEC Interface
1 -- 0,Zero;1,One -- (1 row(s) affected)

EXEC Interface
2 -- 0,Zero;1,One -- Command(s) completed successfully.

GO
--*/



/* Map up in Excel */

</tsql>

<excel>

Without insert
SomeKey	SomeValue
0	Zero
With insert without NOCOUNT
SomeKey	SomeValue
With insert with NOCOUNT
SomeKey	SomeValue
0	Zero
1	One

</excel>

<excel_description>
Add three external TSQL connections with command type SQL and commands:

- EXEC Interface 0

- EXEC Interface 1

- EXEC Interface 2


</excel_description>

The middle one (Interface 1) should not work because it returns a non-empty "rowcount"!

I do not know if the bug in Excel is by design to lower the danger of SQL Injection attacks or because of a suboptimal data retrieval function.

Have a nice day
/G