How to diagnose syntax error from a parameterized SQL Server query

Question

I'm using VBA adodb to write INSERT statements in a SQL Server based on Excel row contents.

I'm using parameterized queries, so my VBA code has the following form:

sqlStatement = "INSERT INTO dbo.mytable (" & Join(insertElement(0), ", ") & ") VALUES (" & Join(insertElement(2), ", ") & ")"
Set cm = New ADODB.Command

With cm
      Debug.Print (sqlStatement)
            .ActiveConnection = conn
            .CommandText = sqlStatement
            .CommandType = adCmdText
             For Each e In insertElement(1)
                Set Pm = .CreateParameter(, adVarChar, 3, 1024, e)
                .Parameters.Append Pm
             Next e
             Set rs = .Execute
        End With

where insertElement(0) is an array of field names, ...(1) is an array of values, and ...(2) is an array of placeholder ?'s to support parameterization

When I run this code, I get an error

[Microsoft][ODBC Driver 13 for SQL Server][SQL Server] Incorrect syntax near 'Output'

However, when I interrogate the sqlStatement text, there is no 'Output' anywhere in the statement. The text is of the form:

INSERT INTO dbo.mytable ([my_field_1],[my_field_2],[somefieldshaveweirdcharslike+#], ...) VALUES (?, ?, ?, ...) 

So, if I'm not providing the 'Output' command directly, and I can't directly see the statement because it's being processed on the server side, how can I diagnose the syntax?

Answer 1

I didn't have trace permissions in the SQL server so I had limited options for exploring server-side logging.

I ended up resolving this the brute force way, adding one field at a time until I received the error.

For my case, the underlying issue was related to using the adodb adNumeric parameter type for decimal values. I switched the parameter type to adDecimal and then set the NumericScale and Precision values on the individual parameter objects. The vba code structure looks something like the below

            For Each p In paramArr
                If p(1) = adVarChar Then
                    Set Pm = .CreateParameter(, p(1), 1, Len(p(2)), p(2))
                Else
                    Set Pm = .CreateParameter(, p(1), 1, , p(2))
                End If
                If p(1) = adDecimal Then
                    Pm.NumericScale = 3
                    Pm.Precision = 13
                End If
                .Parameters.Append Pm
            Next p