13

I have a CLOB field on an Oracle Table that stores a PDF file. When I try export this field to SQL Server Db on Azure I got this error:

2017/01/19 11:14:32 - ImpostoRenda 2.0 - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : Unexpected batch update error committing the database connection.
2017/01/19 11:14:32 - ImpostoRenda 2.0 - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseBatchException: 
2017/01/19 11:14:32 - ImpostoRenda 2.0 - Error updating batch
2017/01/19 11:14:32 - ImpostoRenda 2.0 - I/O Error: Connection reset by peer: socket write error
2017/01/19 11:14:32 - ImpostoRenda 2.0 - 
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at org.pentaho.di.core.database.Database.createKettleDatabaseBatchException(Database.java:1379)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1368)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:575)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at org.pentaho.di.trans.step.RunThread.run(RunThread.java:96)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at java.lang.Thread.run(Unknown Source)
2017/01/19 11:14:32 - ImpostoRenda 2.0 - Caused by: java.sql.BatchUpdateException: I/O Error: Connection reset by peer: socket write error
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:969)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1355)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    ... 3 more
2017/01/19 11:14:32 - ImpostoRenda 2.0 - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : Unexpected error rolling back the database connection.
2017/01/19 11:14:32 - ImpostoRenda 2.0 - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 
2017/01/19 11:14:32 - ImpostoRenda 2.0 - Error performing rollback on connection
2017/01/19 11:14:32 - ImpostoRenda 2.0 - Invalid state, the Connection object is closed.
2017/01/19 11:14:32 - ImpostoRenda 2.0 - 
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at org.pentaho.di.core.database.Database.rollback(Database.java:854)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at org.pentaho.di.core.database.Database.rollback(Database.java:832)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:610)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at org.pentaho.di.trans.step.RunThread.run(RunThread.java:96)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at java.lang.Thread.run(Unknown Source)
2017/01/19 11:14:32 - ImpostoRenda 2.0 - Caused by: java.sql.SQLException: Invalid state, the Connection object is closed.
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at net.sourceforge.jtds.jdbc.ConnectionJDBC2.checkOpen(ConnectionJDBC2.java:1699)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at net.sourceforge.jtds.jdbc.ConnectionJDBC2.rollback(ConnectionJDBC2.java:2100)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    at org.pentaho.di.core.database.Database.rollback(Database.java:845)
2017/01/19 11:14:32 - ImpostoRenda 2.0 -    ... 4 more

But, if I execute the same transform to local SQL Server DB, it works fine.

How do I resolve this?

UPDATE 1

I realized that just when I put "Commit Size" bigger than 15 and set true on "Use batch update for inserts" in my TableOutput Step, the problem occurs.

Migs Isip
  • 1,450
  • 3
  • 23
  • 50
Renatto Machado
  • 1,534
  • 2
  • 16
  • 33
  • 1
    OK, I'll bite... Why are you storing a PDF file in a CLOB column ("field")? CLOB is for long text strings. They are subject to text transformations (explicitly but also implicitly). Text may be stored differently in Oracle and in SQL Server, and an export routine would make the necessary changes. Is that what you want? Doubtful! (That would be consistent with SQL Server -> SQL Server causing no problems, since it's the same "system"). A PDF file is not a long text string. Did you mean BLOB? –  Jan 29 '17 at 15:54
  • 1
    Look, my problem isn't the data type. My problem is interrupted connection with Azure SQL Server DB. That occur independently of data type. – Renatto Machado Jan 30 '17 at 14:42
  • 1
    This is why I asked in a Comment and I didn't offer it as an Answer. With that said, don't be so sure it is not the attempt to manipulate a PDF file as if it was text that crashes your connection! What happens when you try the same process on a CLOB column - do you run into the same problem of an interrupted connection? –  Jan 30 '17 at 14:51
  • @mathguy look the Update 1, the problem occur without PDF too. – Renatto Machado Jan 31 '17 at 17:36

1 Answers1

1

You can try to modify TCP parameters on the client:

REG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v KeepAliveTime /t REG_DWORD /d 30000
REG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v KeepAliveInterval /t REG_DWORD /d 1000
REG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v TcpMaxDataRetransmission /t REG_DWORD /d 10
ErikEJ
  • 40,951
  • 5
  • 75
  • 115