Skip to main content
  • EN

Hello,

I'm getting a very confusing error from SQL Executor:

Microsoft SQL Server Spatial Reader: Query failed,
possibly due to a malformed statement.
Query Text `SELECT * INTO #fme_tempAdoDataTypeTable_spatialReader FROM (
SELECT * FROM sde.[GRID_UK_1KM] WHERE TAG = 'TF2824' ) AS customQuery WHERE
1=0'. Provider error `(-2147217871) Query timeout expired'

I have attached the settings of my SQL Executor. Timeout set to 0 which should mean no timeout.

Any idea what could be wrong?

Thanks for any help

What happens if you set it to, let's say 120 seconds? Does it time out after two minutes?

What happens if you set it to, let's say 120 seconds? Does it time out after two minutes?

Hi David, thanks for your reply.

 

 

I can try. I'm running this workspace within a workspace runner - 5 at time. So I don't know what timeout would be sensible?

 

 

I'd say that the 30 second default timeout is pretty sensible 🙂 If it takes longer, then there is clearly something that would merit investigating, e.g. missing indexes etc that could explain the horrible performance.

Personally I'd expect my SQLExecutors to return a value in less than 2 seconds, otherwise I'd be starting to expect there to be something wrong.

What happens if you set it to, let's say 120 seconds? Does it time out after two minutes?

Hi @honkovam, possibly you don't have a correct understanding the Query Timeout parameter. Please check the help on the Microsoft SQL Server Reader/Writer

 

"Query Timeout (Seconds): The time, in seconds, after which to terminate a query to the database if it has not yet returned a result. If set to 0, there is no timeout. The default is 30.

 

Note: If this value is not set high enough, then the query will return the error Provider Error - Timeout Expired."

I'd say that the 30 second default timeout is pretty sensible 🙂 If it takes longer, then there is clearly something that would merit investigating, e.g. missing indexes etc that could explain the horrible performance.

Personally I'd expect my SQLExecutors to return a value in less than 2 seconds, otherwise I'd be starting to expect there to be something wrong.

Hi @david_r,

 

I will give it a go. I might set it up a bit higher - sometimes I have fairly large input geometries which I use for spatially selecting from fairly large (although indexed) table.

 

 

Thank you

 

I'd say that the 30 second default timeout is pretty sensible 🙂 If it takes longer, then there is clearly something that would merit investigating, e.g. missing indexes etc that could explain the horrible performance.

Personally I'd expect my SQLExecutors to return a value in less than 2 seconds, otherwise I'd be starting to expect there to be something wrong.

Hi @david_r, @takashi

 

I'm quite confused here - why I can't set it to 0 so it never times out? If I run queries against bigger tables, it feels like a gamble trying to determine what timeout to use.

 

In this particular case, the table has 500,000 records, indexed on the field I query and the query takes no time if run in MS SQL Studio...

 

 

Thanks for any further explanation.
Hi @david_r, @takashi

 

I'm quite confused here - why I can't set it to 0 so it never times out? If I run queries against bigger tables, it feels like a gamble trying to determine what timeout to use.

 

In this particular case, the table has 500,000 records, indexed on the field I query and the query takes no time if run in MS SQL Studio...

 

 

Thanks for any further explanation.
Have you tried just leaving the timeout at its default value? Did you get an error, if so, what was the message? What is the thought behind setting an infinite timeout for a query that "takes not time to run"?

 

Reply


Community Stats

30,126
Posts
114,375
Replies
37,768
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.