Question

Error with SQL Executor - query executes correctly in Microsoft SQL Server Management Studio.


Hi - I'm trying to run a SQL query on a MS SQL Server database using a SQL Executor in an FME Desktop workbench. The SQL query executes successfully in SQL Server Management Studio but fails in workbench. I'd be grateful for any advice on this.

 

The SQL Executor returns the following error:

 

Provider error `(-2147467259) Unknown token received from SQL Server'

 

The query is quite long so I wouldn't expect anyone to try and identify the exact problem but would be grateful for any ideas on where to start looking and an explanation of what the error basically means (i.e. what is an unknown token?).

 

Thanks for any advice you can offer.

 


4 replies

Badge +1

An unknown token could mean SQL Server is confused by your SQL script, or, it could be something much more complicated.

 

Some things to try are:

  1. Use a simple SQL statement to make sure that the connection is working, and then start adding the more complicated bits.
  2. Check the SQL script for semi-colons and similar which are part of SQL standards, but, SQL Server will often work without (e.g. https://community.safe.com/s/question/0D54Q000080hPne/sql-executor).
Userlevel 4

Are you using the SQL Server Native client that is installed with FME, or the optional MS SQL ODBC client? I would recommend installing the ODBC client, FME should pick it ut and use it automatically.

https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15

An unknown token could mean SQL Server is confused by your SQL script, or, it could be something much more complicated.

 

Some things to try are:

  1. Use a simple SQL statement to make sure that the connection is working, and then start adding the more complicated bits.
  2. Check the SQL script for semi-colons and similar which are part of SQL standards, but, SQL Server will often work without (e.g. https://community.safe.com/s/question/0D54Q000080hPne/sql-executor).

Thanks @philipwhitten​ - I think the script itself is OK as it runs in SQL Server Management Studio - but I'll check through and remove anything that looks non-essential and will try it again.

 

The connection used in this workbench has been used successfully multiple times in other workbenches but definitely worth checking in this specific workbench with something simple.

 

Thanks for these suggestions - I really appreciate you taking the time to reply.

 

Are you using the SQL Server Native client that is installed with FME, or the optional MS SQL ODBC client? I would recommend installing the ODBC client, FME should pick it ut and use it automatically.

https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15

Hi @david_r​  and @philipwhitten​ - thanks so much to both of you for your input.

 

I think I've resolved this now. The query in question is quite complex and based on a dataset of approx 60 million records and takes approx 60 seconds to run. I think this was causing the SQL Executor to time out. I've changed the Command Timeout (Seconds) parameter to zero seconds and this is now working successfully.

 

I really should have considered this before reaching out so apologies for taking your time up.

 

Thanks again and all the best.

 

 

 

Reply