Stored Procedure results differ between SSMS and VBA

Viewed 61

I have a stored procedure that returns a record set with 4 rows in SSMS.

Originally the - values were null, and I thought that might be the source of the error so I changed them to - hoping that would solve the problem. It didn't...

Result in SSMS:

SP Result in SSMS

When I execute that same stored procedure in Excel, the record set only returns 2 of the 4 rows:

SP result in Excel

Can someone please let me know what I'm missing here?

I use the technique frequently to return results from stored procedures in VBA, but I've never experienced this issue before, and I've tried everything I can think of.

VBA code calling the stored procedure:

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim x As Variant
Dim FieldsToSelect(5) As Variant

FieldsToSelect(0) = "NC_ADD_TFC"
FieldsToSelect(1) = "NC_ADD_INDEX"
FieldsToSelect(2) = "OC_ADD_TFC"
FieldsToSelect(3) = "OC_ADD_INDEX"
FieldsToSelect(4) = "OC_LIKE_ADDTFC"
FieldsToSelect(5) = "OC_LIKE_ADDINDEX"

Set cn = New ADODB.Connection

cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=ID;Password = PASSWORD ;Data Source=Source;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False"

cn.Open

Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = cn
    .CommandType = adCmdStoredProc
    .CommandText = "SP NAME"
    .Parameters.Append .CreateParameter("@Model", adVarChar, adParamInput, 200, Model)
    .Parameters.Append .CreateParameter("@Att", adVarChar, adParamInput, 200, Att)
    .Parameters.Append .CreateParameter("@MastType", adVarChar, adParamInput, 200, MastType)
    .Parameters.Append .CreateParameter("@TFC", adVarChar, adParamInput, 200, TFC)
    .Parameters.Append .CreateParameter("@LineOnDate", adVarChar, adParamInput, 200, LineOnDate)
    .Parameters.Append .CreateParameter("@SalesCode", adVarChar, adParamInput, 200, SalesCode)
    .Parameters.Append .CreateParameter("@OrderOptions", adVarChar, adParamInput, 200, OrderOptions)
    .Parameters.Append .CreateParameter("@OrderNumber", adVarChar, adParamInput, 200, OrderNumber)

End With

Set rs = cmd.Execute
ThisWorkbook.Sheets("ConfigComparison").Range("A3").CopyFromRecordset rs
x = rs.GetRows(Fields:=FieldsToSelect)

GetConfigsComp = x

cn.Close 
0 Answers
Related