im_odbc - incorrect syntax and ERROR id column not found or its type is unsupported

Post a different question
2
responses

Hello,

I hope someone can help me. I need to pick application logs from a few MSSQL database tables. 3 of 4 works perfect -there are no issues getting the data, but one table is causing issues.

Below you can see input I am using. As you can see, there is one column name with the Danish character, however, I am not sure if only this is causing trouble. The error I receive is:

ERROR SQLExecDirect failed, 42000:2:102:[Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'Ą'.; 42000:3:319:[Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a s (odbc error code: -1)

However, If I remove this column from SELECT, another error occurs:
ERROR id column not found or its type is unsupported.

I received a similar error than I left some additional spaces in the query, but after that, I used notepad++ to make sure I don't leave any hidden characters.

=========================================================
</Input>

<Input odbc_besked>
Module im_odbc
ConnectionString Driver={SQL Server Native Client 11.0};Server=SERVERNAME\mssql2014,port;Database=database_name;Trusted_Connection=yes;
SQL SELECT BeskedType, \
Oprettet, \
InternBeskedId, \
SvarPåInternBeskedId, \
Retning, \
BeskedArt, \
SoapBeskedId, \
BeskedId, \
HttpStatusCode, \
Fra, \
Til, \
Failover, \
EnvelopeStartTag, \
SoapHeaderElement, \
IndeholderSoapFault, \
Stack \
FROM table_name WITH (NOLOCK) WHERE InternBeskedId > ?

</Input>

AskedApril 9, 2019 - 11:11am

Answer (1)

I suspect that WITH (NOLOCK) is causing the first issue , can you try to remove this?
Also there needs to be a column named id in the result set so you probably want to add an alias: SELECT ... InternBeskedId as id ...

AnsweredApril 9, 2019 - 1:47pm

Comments (1)

  • Lauxna's picture

    Thank you for looking at my issue :)

    Unfortunately, other queries with 'WITH (NOLOCK)' worked (adding example of input which worked). The same with the incremental key, but I will definitely try adding an alias.

    Another query which worked for me (same user, access level, database, driver):

    Module im_odbc
    ConnectionString Driver={SQL Server Native Client 11.0};Server=SERVERNAME\mssql2014,port;Database=Database_name;Trusted_Connection=yes;
    SQL SELECT AccessControlLogId, \
    CreationTime, \
    CreationUser, \
    AuthenticatedSID, \
    AuthenticatedUserId, \
    RightId, \
    Succeeded, \
    CallingSystem, \
    SessionId \
    FROM tablename WITH (NOLOCK) WHERE AccessControlLogId > ?

    April 9, 2019 - 3:02pm