Hi, I don't normally do Excel macros, I work more on the database side.
However, I was helping a colleague and came across this problem.

I have two stored procedures on the server, listed at the end of this post.
If I run them in my normal environment, they run as expected.

I wrote the following in Excel 2003, which includes a reference to ADO

Option Explicit

Sub TestCallSQLSeverPROCwithOUTPUT()

Dim ADODB_Connection As New ADODB.Connection
Dim ADODB_Command As New ADODB.Command
Dim ADODB_Parameters As ADODB.Parameters
Dim ReturnString As String

With ADODB_Connection
.ConnectionString = "" 'The proper string, removed
.Open
End With

With ADODB_Command
.ActiveConnection = ADODB_Connection
.CommandType = adCmdStoredProc
.CommandText = "USP_TESTProcOUT"
Set ADODB_Parameters = .Parameters
ADODB_Parameters.Refresh
ADODB_Parameters("@SomeString") = "aBcDeFg"
.Execute Options:=adExecuteNoRecords
ReturnString =
End With

Set ADODB_Command = Nothing
ADODB_Connection.Close
Set ADODB_Connection = Nothing

MsgBox ReturnString

End Sub

OK, the above works as expected. However, I wanted to get back a record set,
so I wrote the following:


Sub TestCallSQLSeverPROCwithSET()
Dim ADODB_Connection As New ADODB.Connection
Dim ADODB_Command As New ADODB.Command
Dim ADODB_Parameters As ADODB.Parameters
Dim ADODB_RecordSet As ADODB.Recordset
Dim i As Integer

With ADODB_Connection
.ConnectionString = "" 'The proper string, removed
.Open
End With

With ADODB_Command
.ActiveConnection = ADODB_Connection
.CommandType = adCmdStoredProc
.CommandText = "USP_TESTProcSET"
Set ADODB_Parameters = .Parameters
End With

ADODB_Parameters.Refresh
ADODB_Parameters("@SomeString") = "aBcDeFg"
Set ADODB_RecordSet = ADODB_Command.Execute

i = 0

Set ADODB_Command = Nothing
Set ADODB_RecordSet = Nothing
ADODB_Connection.Close
Set ADODB_Connection = Nothing

End Sub

This runs without errors, or a record set. If I put a breakpoint at i=0, and
put a watch on ADODB_RecordSet, many items have the following note:

<Operation is not allowed when the object is closed.

Can somebody help me out with how I can get that recordset assigned?

Thanks in advance,

Bob

SQL Server 2005 Procedures

CREATE PROCEDURE dbo.USP_TESTProcOUT
(
@SomeString VARCHAR(36)
,@SomeStringOUT VARCHAR(36) OUTPUT
) AS
BEGIN
SET @SomeStringOUT = REVERSE(@SomeString);
END;
GO

CREATE PROCEDURE dbo.USP_TESTProcSET
(
@SomeString VARCHAR(36)
) AS
BEGIN
DECLARE @ReturnTable TABLE
(
RowNum INT IDENTITY(1,1)
,Letter CHAR(1)
);
DECLARE @Counter INT;
SET @Counter = 0;
WHILE @Counter < LEN(@SomeString)
BEGIN
SET @Counter = @Counter + 1;
INSERT INTO @ReturnTable(Letter)
VALUES (SUBSTRING(@SomeString,@Counter,1));
END;
SELECT Rownum,Letter FROM @ReturnTable ORDER BY RowNum;
END;
GO