MSSQL connection problem

[MattCasters]

(Posted by Anonymous, set to Plain, not wiki!)

Hi Matt,

I have a problem using MSSQL native connection in spoon,

Error occured while trying to connect to the database
Error connecting to database: (using class net.sourceforge.jtds.jdbc.Driver)
Network error IOException: Connection refused: connect
be.ibridge.kettle.core.exception.KettleDatabaseException:
Error occured while trying to connect to the database
Error connecting to database: (using class net.sourceforge.jtds.jdbc.Driver)
Network error IOException: Connection refused: connect

i had the same problem with another application and i found a tip in JTDS site, it was add the instance to the connection URL, it means :

jdbc:jtds:sqlserver://127.0.0.1/<instance>;instance=<instance>;user=<username>;password=<password>;

i tried it using Generic Database connection:
URL: jdbc:jtds:sqlserver://127.0.0.1/<instance>;instance=<instance>;user=<username>;password=<password>;
Driver Class: net.sourceforge.jtds.jdbc.Driver

and It works perfectly !!!!

Then I did my transfortmation and i tested it using "Transformation Preview" option and all worked OK.

But when i tried to run it with "Transformation Run" option it didn&#39;t work. and it gave the following errors.

2006/04/05 18:36:28 - Spoon - Transformation opened.
2006/04/05 18:36:28 - Spoon - Excecuting transformation [null]...
2006/04/05 18:36:28 - be.ibridge.kettle.trans.Trans - Dispatching started for filename [F:\Kettle-2.2.2\transformations\table2xml.ktr]
2006/04/05 18:36:28 - be.ibridge.kettle.trans.Trans - Nr of arguments detected: 10
2006/04/05 18:36:29 - TFICHE_ABSENCE.0 - ERROR : An error occurred, processing will be stopped:
2006/04/05 18:36:29 - TFICHE_ABSENCE.0 - ERROR : Error occured while trying to connect to the database
2006/04/05 18:36:29 - TFICHE_ABSENCE.0 - ERROR :
2006/04/05 18:36:29 - TFICHE_ABSENCE.0 - ERROR : Error connecting to database: (using class sun.jdbc.odbc.JdbcOdbcDriver)
2006/04/05 18:36:29 - TFICHE_ABSENCE.0 - ERROR : [Microsoft][Pilote ODBC Paradox] &#39;(Inconnu)&#39; n&#39;est pas un chemin d&#39;acc￨s valide. Assurez-vous que le nom du chemin d&#39;acc￨s est correct et qu&#39;une connexion est ￩tablie avec le serveur sur lequel r￩side le fichier.
2006/04/05 18:36:29 - be.ibridge.kettle.trans.Trans - ERROR : Error initializing step [TFICHE_ABSENCE]
2006/04/05 18:36:29 - XML Output.0 - Opening output stream in encoding: UTF-8
2006/04/05 18:36:29 - be.ibridge.kettle.trans.Trans - ERROR : We failed to initialize at least one step. Execution can not begin!
2006/04/05 18:36:29 - Spoon - Started.
2006/04/05 18:36:29 - Spoon - The transformation has finished!!


The connection to the database was failed using a driver that i didnt setup .


Could you give some tips to solve it ?,

Thanks in advance

Jaime

[MattCasters]

OK, I think I know this one. ;-)

There was a problem with the generic database connection and the use of the JDBC driver.
Can you try the latest dev version and see if the problem is solved for you? Otherwise we open a new Tracker and hunt it down ;-)

Thanks for the feedback!

Matt

[jeyex]

Hi Matt,

I tried the devel version 2.3.0 and the problem persist :

Error message:

2006/04/06 09:59:26 - Spoon - Transformation opened.
2006/04/06 09:59:26 - Spoon - Excecuting transformation [null]...
2006/04/06 09:59:26 - be.ibridge.kettle.trans.Trans - Dispatching started for filename [F:\Kettle-2.3.0\transformations\table2xml.ktr]
2006/04/06 09:59:26 - be.ibridge.kettle.trans.Trans - Nr of arguments detected: 10
2006/04/06 09:59:26 - be.ibridge.kettle.trans.Trans - This is not a replay transformation
2006/04/06 09:59:26 - be.ibridge.kettle.trans.Trans - This transformation can be replayed with replay date: 2006/04/06 09:59:26
2006/04/06 09:59:26 - Table input.0 - ERROR : An error occurred, processing will be stopped:
Error occured while trying to connect to the database

Error connecting to database: (using class sun.jdbc.odbc.JdbcOdbcDriver)
[Microsoft][Gestionnaire de pilotes ODBC] Source de donnï¿©es introuvable et nom de pilote non spï¿©cifiï¿©


2006/04/06 09:59:26 - be.ibridge.kettle.trans.Trans - ERROR : Error initializing step [Table input]
2006/04/06 09:59:26 - XML Output.0 - Opening output stream in encoding: UTF-8
2006/04/06 09:59:26 - be.ibridge.kettle.trans.Trans - ERROR : We failed to initialize at least one step. Execution can not begin!
2006/04/06 09:59:26 - Spoon - Started.
2006/04/06 09:59:26 - Spoon - The transformation has finished!!


Also , this problem appear after to close and restart spoon; My database connection is failed, due to the method of connection was reloaded as ODBC (jdbcodbc).


Thanks in advance for your help

regards


Jaime

[jeyex]

Hi Matt,

I found a workaround for MSSQL to add instance parameter to connection url:

i put the following string in Database Name parameter :)

<instance>;instance=<instance>;

With this when kettle genere the connection url it will have the instance parameter

regards

Jaime


Note: this solve my problem using MSSQL connection type, but the problem with Generic Database connection is still there.

[MattCasters]

The weird thing about this is that I installed a SQL2K database for no other reason then to fix a similar problem.
I guess it&#39;s not that easy to go around the "I want to store any length of String in the database" problem.

I&#39;ll investigate this over the weekend.

Thanks for the feedback, I&#39;m sure we&#39;ll find a solution soon.

All the best,
Matt