Skip to main content
  • EN

Hi,

I have been using the sql executor to run a sql server stored procedure which populates a table, I then try to use a feature reader to read from the table that has just been populated. Alas it times out. If I do either of these components separately it works leading me to believe FME is holding the connection open to sql server and creating a lock on the table, so that when the reader tries to read it can't. I am using the SQL Server JDBC Client. I will try with the Native Client but this will mean I can't use it with fme cloud which is what it is intended for.

 

Has anyone else had this issue? Working log and failing log are attached.

Many Thanks as always

Wwith the feature reader I think it is JDBC only so I dont think I can use the native client driver in any case.

Hi @olivermorris,

Try adding a COMMIT after your stored procedure invocation.  For example, your SQLExecutor would contain something like the following:

FME_SQL_DELIMITER ;
EXEC	Ldbo].RSP_API_EXPORT_PS_SR]
		@modules = ...;
COMMIT;

Note the semi-colon after your EXEC.  The explicit commit will hopefully release any locks obtained by the stored procedure.

(In general, SQLExecutor doesn't auto-commit anything to allow manual control over transactions.)

Hi @olivermorris,

Try adding a COMMIT after your stored procedure invocation.  For example, your SQLExecutor would contain something like the following:

FME_SQL_DELIMITER ;
EXEC	Ldbo].RSP_API_EXPORT_PS_SR]
		@modules = ...;
COMMIT;

Note the semi-colon after your EXEC.  The explicit commit will hopefully release any locks obtained by the stored procedure.

(In general, SQLExecutor doesn't auto-commit anything to allow manual control over transactions.)

perfect that did the trick, thank you

 

Reply


Community Stats

30,108
Posts
114,294
Replies
37,743
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.