Excel returning a record set from a stored procedure.

[INTP56]

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 = ADODB_Parameters("@SomeStringOUT").Value
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

 

[Robin Hammond]

Hard to see where the differences lie, but you could try this, which works
for me.

'back in SQL DO THE FOLLOWING
'CREATE PROC spTemp(@Table1 nvarchar(50))
'as
'-- example of a dynamic SQL sp returning multiple recordsets
'SET NOCOUNT ON
'EXEC('SELECT * FROM ' + @Table1)
'SET NOCOUNT OFF
'GO

Sub Test2()
Dim vParams As Variant
Dim vValues As Variant
Dim rsReturn As ADODB.Recordset
vParams = Array("Table1")
vValues = Array("TableName1")
'change DBNAME to whatever DB you created the above proc in
ReturnRSFromSP "spTemp", vParams, vValues, "DBNAME"
End Sub

Public Sub ReturnRSFromSP(strSP As String, _
vParams As Variant, _
vValues As Variant, _
strCatalog As String)

Dim cnSP As ADODB.Connection
Dim cmdSP As ADODB.Command
Dim lCounter As Long
Dim strItem As String
Dim lIndex As Long
Dim rsReturn As ADODB.Recordset

Set cnSP = New ADODB.Connection

cnSP.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;Initial Catalog=" & strCatalog & _
";Data Source=" & FILLTHISIN 'add your data source here

cnSP.Open

'create the command object
Set cmdSP = New ADODB.Command
cmdSP.ActiveConnection = cnSP
cmdSP.CommandText = strSP
cmdSP.CommandType = adCmdStoredProc
cmdSP.Parameters.Refresh

'this is set up for multiple parameters which may not come in the order
expected
lCounter = 0

For lCounter = 1 To cmdSP.Parameters.Count - 1

strItem = cmdSP.Parameters(lCounter).Name

For lIndex = 0 To UBound(vParams)

If "@" & vParams(lIndex) = strItem Then

cmdSP.Parameters(lCounter).Value = vValues(lIndex)
Exit For

End If

Next

Next

'create the recordset object
Set rsReturn = New ADODB.Recordset

With rsReturn

.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic

'execute the SP returning the result into a recordset
.Open cmdSP

End With

'this is set up to allow multiple recordsets coming back from the sp
Do Until rsReturn Is Nothing

If rsReturn.State = adStateOpen Then

DumpRecordset rsReturn

End If

Set rsReturn = rsReturn.NextRecordset

Loop

Set cmdSP = Nothing

If cnSP.State = adStateOpen Then cnSP.Close
Set cnSP = Nothing
Set rsReturn = Nothing
End Sub

Sub DumpRecordset(rsName As ADODB.Recordset, Optional lstartpos As Long)
Dim W As Workbook
Dim nField As Integer
Dim lRowPos As Long

Set W = ActiveWorkbook

Workbooks.Add

With rsName

For nField = 1 To .Fields.Count

Cells(1, nField).Value = .Fields(nField - 1).Name

Next nField

If .RecordCount = 0 Then Exit Sub
.MoveFirst

If Not IsEmpty(lstartpos) Then .Move lstartpos

End With

Cells(2, 1).CopyFromRecordset rsName
End Sub

 

[INTP56]

Robin,

Thanks for your response. I hate to admit this, but in my haste to create
test procedure I forgot a basic item. When I saw your SQL proc, it reminded
me of the important item, which is the following line at the top of the PROC

SET NOCOUNT ON;

Apparently leaving this off screws up ADO with the recordsets. I added that
line to my procedure (Which I know is supposed to be there all the time) and
it worked.

One other note, when I showed my colleague, it wouldn't work for him because
the parameters was always empty. I needed to grant him EXECUTE (and I
included VIEW) permissions in SQL Server for it to work for him. All of us
use integrated security here.

I've attached the final code.

Thanks again, Bob

Sub TestCallSQLSeverPROCwithSET()

' MAKE SURE SQL SERVER PROC HAS SET NOCOUNT ON;
' AND THAT USER HAS EXECUTE/VIEW PERMISSIONS ON PROC

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, j As Integer, k As Integer
Dim RSVariant As Variant

With ADODB_Connection
.ConnectionString = "FILE NAME=C:\UDLFile.udl"
.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

RSVariant = ADODB_RecordSet.GetRows

i = UBound(RSVariant, 1) - LBound(RSVariant, 1) + 1
j = UBound(RSVariant, 2) - LBound(RSVariant, 2) + 1

With ThisWorkbook.Worksheets(1)
For k = 0 To ADODB_RecordSet.Fields.Count - 1
.Cells(1, k + 1).Value = ADODB_RecordSet.Fields(k).Name
Next k
.Range(.Cells(2, 1), .Cells(j + 1, i)).Value =
WorksheetFunction.Transpose(RSVariant)
End With

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

End Sub

 

[Robin Hammond]

I'm glad it worked. It took me ages to figure out the nocount thing the
first time I ran up against it.