Skip to main content
Solved

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

oliver.morris
Contributor
Forum|alt.badge.img+12

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.)

View original
2 Attachments
Did this help you find an answer to your question?

3 replies

oliver.morris
Contributor
Forum|alt.badge.img+12
  • 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+12
  • oliver.morrisContributor
  • Author
  • Contributor
  • April 27, 2018
ravenkopelman wrote:

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

 

Reply


Helpful Members This Week

Community Stats

31,224
Posts
118,407
Replies
38,827
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.