[resolved] SQLException:Network error IOException: Address already in use

Anonymous — Wed, 16 Sep 2015 10:38:18 GMT

Hi,
I have a set of Jobs for a migration activity. I execute these Jobs with a single .bat file that contains one call for each of these Jobs.
These Jobs have a MS Access DB as data source and MS SQL Server as destination.
A subset of these Jobs use a MS SQL Server table (that I call Counter Table) in order to get an integer value in it contained, assign it as primary key for a destination table and update this integer value so that it can be used at the next iteration. The text below explain this logic:
tDBInput_1->row1(main)->tFlowToIterate->tDBInput_2->row2(main)->tMap->out(main)->tDBOutput_1->OnComponentOk->tDBRow_1

tDBInput1 is the table where I read the data to migrate in the tDBOuput1 table;
tDBInput2 is the counter table;
In the tMap component I set the primary key of tDBOuput1 through row2.integer_value and the other column through (String)globalMap.get("row1.name");
tDBRow_1 is used to update the integer_value on tDBInput_2 table;
When I run this set of jobs on a machine with MS SQL Server 2014 I have no problems and all works fine.
When I run the same set of jobs on a machine with MS SQL Server 2012, in correspondence of one of this job that use the "Counter Table", I get the following exception on the component responsible of update the integer value of the "Counter Table":

Exception in component tMSSqlRow_1
java.sql.SQLException: Network error IOException: Address already in use: connec t
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java :410)
at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java :50)
at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at migration.job2_0_1.Job2.tMSSqlRow_1Process( Job2.java:2868)
at migration.job2_0_1.Job2.tJDBCInput_1Process( Job2.java:2656)
at migration.job2_0_1.Job2.tWarn_1Process( Job2.java:1222)
at migration.job2_0_1.Job2.runJobInTOS( Job2.java:4695)
at migration.job2_0_1.Job2.main(Job2.j ava:4524)
Caused by: java.net.BindException: Address already in use: connect
at java.net.DualStackPlainSocketImpl.connect0(Native Method)
at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
at java.net.AbstractPlainSocketImpl.connect(Unknown Source)
at java.net.PlainSocketImpl.connect(Unknown Source)
at java.net.SocksSocketImpl.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at net.sourceforge.jtds.jdbc.SharedSocket.createSocketForJDBC3(SharedSoc ket.java:307)
at net.sourceforge.jtds.jdbc.SharedSocket.<init>(SharedSocket.java:257)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java :311)
... 9 more

In particular, I get this exception on a Job that operates with the same MS Access source table and the same MS SQL Server destination table used by a Job that is executed just before of it.
Now, there are two things that make me very confused:
1) Why I get this exception only on one of the Jobs that use the "Counter Table" (and therefore that carry out the logic explained above) and not on all jobs of this subset?
2) Why I get this exception only when Jobs are executed on the machine with MS SQL Server 2012? Is it related to this version of DBMS?
Thanks in advance.

Re: [resolved] SQLException:Network error IOException: Address already in use

Anonymous — Thu, 17 Sep 2015 10:07:45 GMT

Hi
It creates so many DB connection to server with this job design, refer to the KB article on Microsoft website, it exceeds the max number of connections. To resolve this error, you need to modify the these two DB parameters: MaxUserPort and TcpTimedWaitDelay. I think these two parameters have different default value in MS SQL Server 2012 and MS SQL Server 2014.
Another solution is to create only one DB connection, and commit the change in every row. Design the job as below:
tDBconnection
|
onsubjobok
|
tDBInput_1->row1(main)->tFlowToIterate->tDBInput_2->row2(main)->tMap->out(main)->tDBOutput_1->OnComponentOk->tDBRow_1--OncomponentOK--tDBCommint_1
|
onsubjobok
|
tDBClose
on tDBRow_1: check the 'use an existing DB connectin' box.
on tDBCommit_1: uncheck the 'Close connection' box.
Best regards
Shong

Re: [resolved] SQLException:Network error IOException: Address already in use

Anonymous — Thu, 17 Sep 2015 10:46:30 GMT

Thanks a lot Shong! I have designed Job as you showed, with only one DB Connection, and now it works!
Just one little question.. I have to check the 'use an existing DB connectin' box for every MSSqlServer component (i.e. tDBInput_2, tDBOutput_1 and tDBRow_1 ) or just for the component that updates the SQL Server table (i.e. tDBRow_1)?

Thanks a lot again!
Giuseppe

Re: [resolved] SQLException:Network error IOException: Address already in use

Anonymous — Thu, 17 Sep 2015 10:54:35 GMT

If they are connecting to the same DB, you can check 'use ....'box on all the components.

topic Re: [resolved] SQLException:Network error IOException: Address already in use in Talend Studio

[resolved] SQLException:Network error IOException: Address already in use

Re: [resolved] SQLException:Network error IOException: Address already in use

Re: [resolved] SQLException:Network error IOException: Address already in use

Re: [resolved] SQLException:Network error IOException: Address already in use