topic Re: Close SQL Connections in Talend Studio

Close SQL Connections

Anonymous — Sat, 16 Nov 2024 08:07:09 GMT

Hi!

The component tMSSqlConnection creates a new connection to a database. I had been using this component without closing it with the component tMSSQlCommit and I eventually had an error from the sql azure database:

12:58:49;xt8Pui;xt8Pui;xt8Pui;LOCAL_PROJECT;IPayment_CommitTransaction;Default;6;Java Exception;tMSSqlInput_2;java.sql.SQLException:Resource ID : 1. The request limit for the database is 120 and has been reached.

I checked this error and it was generated because I had a lot of sessions at the same time(This is what I think). I don't want this to happen, so I have to resolve this problem.

To fix the problem I don't know if I should delete the component that creates

that create the sessions and use components like tMSSqlInput or tMSSqlOutput. Because with those components you are still creating sessions and closing them after the input, right?

Or the best option is to close the connection with tMSSqlCommit. I have to say that I am woking with a webservice which is receiving a lot of requests all the time. Thanks.

Re: Close SQL Connections

David_Beaty — Tue, 12 Jun 2018 13:17:10 GMT

Hi,

There is a tMSSQLClose Component which you can use to close connections you've opened with tMSSQLConnection.

I'suggest doing this over closing a connection on commit.

Thanks

David

Re: Close SQL Connections

iamabhishek — Wed, 13 Jun 2018 13:39:56 GMT

Hi @dferre - the error message means that you have reached a limit of concurrent logins allowed for that database on that service tier. In your case 120 is the number and I am guessing you are on Standard/S2 (Service Tier) which supports 120 Max Concurrent Requests.
When you are using tMSSqlCommit you have the option to "Close connection" which is selected by default and would allow you to close the database connection once the commit is done. Otherwise you need tMssqlClose to close the transaction committed in the connected DB.
Related to the components like tMSSqlInput or tMSSqlOutput, yes they closes the respective connection they are using once the task is completed but the catch here is tMSSqlOutput wouldn't be able to commit the data that is being written to database (though as per documentations there is an option for "Advanced settings -- Commit every" which would be committing batches of rows together into the DB but sadly not able to verify the same when using TOS -DI community edition), for which we need tMSSqlCommit.