Run-time error '-2147217900 (80040e14)': Syntax error or access violation

[kristianb63]

Hello

I have a macro that I'm using to call a SQL stored procedure that contains variables that are passed from Excel. I have removed the values that identify the server / db name and sp name

When executing the sp from with in SQL the report works. When i try in Excel, using the same values for the parameters, it returns the Run-time error '-2147217900 (80040e14)': Syntax error or access violation

The debug yellows on the Set rs = cmd.Execute() line of the macro

Could anybody help please?









Sub CIS_REFRESH()


Application.ScreenUpdating = False

Sheet3.Range("A2:Z1000000").ClearContents

''2- Declare Variables

Dim osheet As String
Dim orange As String
osheet = "CIS_REPORT"
orange = "A2"

Dim bu_name As String
Dim INVOICEFROM As String
Dim INVOICETO As String


Dim Conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

bu_name = UCase(Sheet13.Cells(6, 3))
INVOICEFROM = (Sheet13.Cells(7, 4))
INVOICETO = (Sheet13.Cells(8, 4))


'Function ConnectSQLServerStoredProc(osheet As String, orange As String, Server As String, Jurisdiction As String)

'SQL Connection
Set Conn = New ADODB.Connection
Set rs = New ADODB.Recordset

Conn.ConnectionString = "Provider=SQLOLEDB;;Server=REMOVED;Database=REMOVED;Trusted_connection=yes;"
Conn.Open
Set cmd = New ADODB.Command


cmd.ActiveConnection = Conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "REMOVED FOR CONFIDENTIALITY"
cmd.Parameters.Append cmd.CreateParameter("@bu_name", adVarChar, adParamInput, 60, bu_name)
cmd.Parameters.Append cmd.CreateParameter("@INVOICEFROM", adVarChar, adParamInput, 60, INVOICEFROM)
cmd.Parameters.Append cmd.CreateParameter("@INVOICETO", adVarChar, adParamInput, 60, INVOICETO)


Set rs = cmd.Execute()

If Not rs.EOF Then
Sheets(osheet).Range(orange).CopyFromRecordset rs
rs.Close
Else: MsgBox "No Records", vbCritical
End If

If CBool(Conn.State And asStateOpen) Then Conn.Close
Set Conn = Nothing
Set rs = Nothing
Set cmd = Nothing




End Sub