One Star

Error in JDBC Stored procedure component when using with Azure Sql

Hi
I have a job where i am reading data from a stage table - validate the data using a schema compliance check component and then insert it into the final production table.
If there is an error on the schema compliance component the error path is connected to a stored procedure component which updates the staging table status column to FAIL with an appropriate error message.
However i keep getting the following error at the stored procedure
Exception in component tJDBCSP_1
com.microsoft.sqlserver.jdbc.SQLServerException: Read timed out
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1668)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1655)
at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:1789)
at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:4838)
at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:6150)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:402)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1716)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:332)
disconnected
at biopharm.prod_hcp_address_1_0.prod_hcp_address.tJDBCInput_1Process(prod_hcp_address.java:4274)
at biopharm.prod_hcp_address_1_0.prod_hcp_address.runJobInTOS(prod_hcp_address.java:6672)
at biopharm.prod_hcp_address_1_0.prod_hcp_address.main(prod_hcp_address.java:6531)
I have attached the screen shot below. Any clues?
Thanks
5 REPLIES
Moderator

Re: Error in JDBC Stored procedure component when using with Azure Sql

Hi,
Have you already checked if your Azure Sql table is locked or blocked? 
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Error in JDBC Stored procedure component when using with Azure Sql

Hi Sabrina
Checked the event logs - there are no deadlocks. But what i do see is the following message "Connection has been idle for longer than system defined threshold."
Is it possible that the error is because of the above? If so then what is the mechanism in Talend to retry and connect? What can be done to avoid this error?
Regards
Sid
One Star

Re: Error in JDBC Stored procedure component when using with Azure Sql

You may find this link on azure retry useful.  Unfortunately the examples are all .NET
I am considering using Talend with Azure Sql and interested in your results. 
One Star

Re: Error in JDBC Stored procedure component when using with Azure Sql

Sabrina
The lost connection seems to be the reason for failure. Surely talend must have some retry functionality for lost database connection. If not what are your recommendations?
Sid
One Star

Re: Error in JDBC Stored procedure component when using with Azure Sql

Any updates/workarounds in Talend open studio to retry connections?