Skip to main content
  • EN

Hi,

I'am creating a workbench which uses a MSSQL database writer to delete certain records from a database. In most cases this works fine but sometimes I get the following error/Warning message:

MS SQL Server Writer: Failed to write a feature of type <name of the table> to the database. Provider error `(-2147217871) Query timeout expired'. SQL Command `DELETE FROM <name of the table> WHERE <field within the table> = 6339404'

Every time this error occurs there are exactly 50 seconds between opening and closing the database connection. In case the workbench runs without error the connections lasts shorter and is executed without any problem.

The following changes did not solve the problem:

- Change Command Timeout from 30s to 600s and even to 9000s (when executed directly in SQL server management studio the query takes about 1 minutes and 30 seconds to execute, so 600s should be more than enough).

- Checked the time out settings of SQL server. They are set at default which is about 10minutes.

Is someone able to tell me how to solve this problem? Or give me any advice on how to solve it?

Some aditional information:

 

FME version: 2015.1.0.3

 

Error occurs at different environments, not only at my own laptop but also when in case I run the workbench at another machine.

 

 

It seems the Command Timeout setting does not work. When I set it to 1s it still lasts 50s before a time out to occur. I am using FME 2015.1.0.3.

 

 

How about using a SQLExecutor and not the MSSQL writer?

I suspect the field used by the WHERE clause isn't indexed. I'd strongly recommend putting an index on the field if this is a regular occurrence, it should make the delete an order of magnitude faster. This way you won't have to worry about the timeout, and the database will spend less time doing full table scans.

How about using a SQLExecutor and not the MSSQL writer?

@itay: Thanks for your answer. I tried it out but now I am getting the following message.

 

 

MS SQL Server Reader: Query failed, possibly due to a malformed statement. Query Text `Delete FROM <table name>

 


WHERE <table name>.id=6'. Provider error `(-2147217871) Query timeout expired'

 


A fatal error has occurred. Check the logfile above for details.

 

 

The syntax below is correct syntax for an SQLExecuter right?

 

DELETE FROM Customers

 

WHERE CustomerName='Alfreds Futterkiste'

 

 

In case it is I don't know what I am doing wrong.

 

 

 

How about using a SQLExecutor and not the MSSQL writer?

@itay: And I am wondering: Why do you think the SQLExecuter to be a better option? Just curious.

 

Thanks for answering my question. I'am afraid I don't have a lot of control over the database. But I will find a way to work around this timeout. These are nested records I want to delete. I can always just let FME do (part of) the work instead. Just do two calls to the database instead of one.

But still: It suprised me to have a time out warning after 50s while the commandTimeout was set to 1s. Isn't this strange behaviour?

@itay: And I am wondering: Why do you think the SQLExecuter to be a better option? Just curious.

 

It could possibly act differently, but it is more of a guess

 

 

Reply


Community Stats

30,092
Posts
114,247
Replies
37,720
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.