Skip to main content
Solved

SQL Server Execute SP, then reader from output table is locked

  • April 26, 2018
  • 3 replies
  • 94 views
oliver.morris
Contributor
Forum|alt.badge.img+14

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

Best answer by ravenkopelman

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	[dbo].[SP_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.)

2 Attachments
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

3 replies

oliver.morris
Contributor
Forum|alt.badge.img+14
  • oliver.morrisContributor
  • Author
  • Contributor
  • April 26, 2018

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

ravenkopelman
Safer
Forum|alt.badge.img+1
  • ravenkopelmanSafer
  • Safer
  • Best Answer
  • April 26, 2018

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	[dbo].[SP_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.)

oliver.morris
Contributor
Forum|alt.badge.img+14
  • oliver.morrisContributor
  • Author
  • Contributor
  • April 27, 2018

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	[dbo].[SP_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

 

Community Stats

32,207
Posts
122,303
Replies
39,891
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.