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

--
Robin Hammond
www.enhanceddatasystems.com


"INTP56" wrote in message
...
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