One Star

Connection & schema problem withTOS DI 5.1.1 and MS SQL Server 2008 R2

Hello,
I have searched as best I can for any information on the above and have only found other users noting a similar issue and reference to TDI21056 and TDI21382 which refers to Windows XP.
My environment is: Windows 7 PCs (both real and virtual) accessing MS SQL Server 2008 R2 (on Windows Server 2008) and Oracle (on unix) databases on virtual servers. For purposes of some testing, the MS SQL Server databases may be on the local machine. Up until now the Talend versions have been 4.1.2 and 4.2.2 with no connection problems.
I have now upgraded the virtual PC for pre-production testing to 5.1.1 (as this was provided as the latest stable version) and imported all projects from the earlier versions. On trying to update one of the Db Connections to MS SQL Server, it initially stated ?Connection Successful?, then on clicking Finish the following appears:
Access to the data failure{0}
java.lang.RuntimeException: java.lang.ClassCastException: net.sourceforge.jtds.jdbc.ConnectionJDBC3 cannot be cast to net.sourceforge.jtds.jdbc.ConnectionJDBC2
Consequently, it was impossible to make any changes and save them due to this problem.
However, I created a new connection to the MS SQL database which initially tested OK and which I was able to save. Initial testing by changing the job connections was successful provided there was no tMap component to output tables because retrieval of schema does not appear to work. All that shows is the Catalog name which cannot be expanded into the separate tables.
Curiously, testing various existing sub-jobs of old projects that were imported into the new version of Talend proved successful. However, if I went in to Edit the connection to MS SQL Server, exactly the same thing happened as above (i.e. Access to the data failure{0}). I am concluding that provided there is no need to alter any jobs, a straight import into Version 5.1.1 will apparently work.
Unfortunately, I have to make changes and need to be able to retrieve the table schemas.
(An idea of importing the ?old? connection into the new project failed because it did not appear in the list of items even though it was present in the project.)
Is there likely to be a resolution to this problem in the near future at all?
Many thanks
AnneS
17 REPLIES
One Star

Re: Connection & schema problem withTOS DI 5.1.1 and MS SQL Server 2008 R2

I am having a similar problem on a Windows Server 2008 Enterprise VM running TOS DI 5.1.1, JRE1.6.0_32, and MS SQL Server 2005. I am running TOS DI 5.0.2 on Windows 7 and MSSQL 2008 R2 without that issue.
One Star

Re: Connection & schema problem withTOS DI 5.1.1 and MS SQL Server 2008 R2

I am having a similar problem on a Windows 7 running TOS DI 5.1.1, JRE1.6.0_32, and remoute MS SQL Server 2000.
If I am running TOS DI 5.0.1 on Windows 7 and remoute MSSQL 2000,MSSQL 2005, MSSQL 2008 without that issue.
One Star

Re: Connection & schema problem withTOS DI 5.1.1 and MS SQL Server 2008 R2

I've the same issue using win Vista, TOS DI 5.1.1 r84309 and a MSSQL server 2005 express Smiley Sad
One Star

Re: Connection & schema problem withTOS DI 5.1.1 and MS SQL Server 2008 R2

I've the same issue using win Vista, TOS DI 5.1.1 r84309 and a MSSQL server 2005 express Smiley Sad

I had to go back with version 5.0.2 to ""solve"" this issue... i hope it will be corrected in next version.
One Star

Re: Connection & schema problem withTOS DI 5.1.1 and MS SQL Server 2008 R2

Many thanks for all your replies. I have now downloaded version 5.0.2 and have successfully imported one project into it. The connections work and retrieval of schemas seems to work.
Unfortunately, another issue has now appeared - I am getting Invalid Object Name for some (not all) of the tables. I've been deleting and re-creating components and links and also re-retrieving the schema - so far no resolution.
One Star

Re: Connection & schema problem withTOS DI 5.1.1 and MS SQL Server 2008 R2

I'm having the same issue on Win Server 2008 R2 using TOS DI 5.1.1. Trying to hit SS 2005 and I see the schema but when I select a table I get "pending" forever.
One Star

Re: Connection & schema problem withTOS DI 5.1.1 and MS SQL Server 2008 R2

Access to the data failure{0}
java.lang.RuntimeException: java.lang.ClassCastException: net.sourceforge.jtds.jdbc.ConnectionJDBC3 cannot be cast to net.sourceforge.jtds.jdbc.ConnectionJDBC2

using win 7 64bit and TOS 5.1.1
--------------
Any idea on how to fix this?
One Star

Re: Connection & schema problem withTOS DI 5.1.1 and MS SQL Server 2008 R2

Hi
It's a bug. Please report it on BugTracker.
Regards,
Pedro
One Star

Re: Connection & schema problem withTOS DI 5.1.1 and MS SQL Server 2008 R2

Hi Pedro,
Thank you for your response. I have now reported this on bugtracker.
Regards,
Anne
One Star

Re: Connection & schema problem withTOS DI 5.1.1 and MS SQL Server 2008 R2

I've had success using the generic JDBC connection:
jdbc:sqlserver://ip-address:1433;databaseName=yourdbname
The driver jar I am using is sqljdbc4.jar.
There was another post with this suggestion but I cannot find it
One Star

Re: Connection & schema problem withTOS DI 5.1.1 and MS SQL Server 2008 R2

Hi All,
I'm new to talend mdm please help me how to update mdm version 5.0.1 to 5.1.1 in unix.If you have any document please share with me.

Thanks
Velu
One Star

Re: Connection & schema problem withTOS DI 5.1.1 and MS SQL Server 2008 R2

I've had success using the generic JDBC connection:
jdbc:sqlserver://ip-address:1433;databaseName=yourdbname
The driver jar I am using is sqljdbc4.jar.
There was another post with this suggestion but I cannot find it

Thanks for the info. I was having the same problem and using the generic jdbc connection solved it.
One Star

Re: Connection & schema problem withTOS DI 5.1.1 and MS SQL Server 2008 R2

I've the same issue using windows 8.1, TOS DI 5.5 and MSSQL server 2014
One Star

Re: Connection & schema problem withTOS DI 5.1.1 and MS SQL Server 2008 R2

tmssqlinput Invalid state, the Connection object is closed. in talend 5.5 with MSSQL server 2014
Four Stars

Re: Connection & schema problem withTOS DI 5.1.1 and MS SQL Server 2008 R2

the same to me, i was trying to connect MSSQL server 2008 with windows auth, but it doesnt work, then i tried to creating a database user but it doesnt work too! in version 5.2.2 it works fine, but in version 5.4 it doesnt.
One Star

Re: Connection & schema problem withTOS DI 5.1.1 and MS SQL Server 2008 R2

Hi,
Not exactly the same problem, but I can't retrieve the schema... my connection is working... 
Is there a specific configuration for sql server? 
(SQL Server version: 2008 R2
TOSDI version: 5.2.2)
Moderator

Re: Connection & schema problem withTOS DI 5.1.1 and MS SQL Server 2008 R2

Hi cedrickb,
Not exactly the same problem, but I can't retrieve the schema... my connection is working... 
Is there a specific configuration for sql server? 
(SQL Server version: 2008 R2
TOSDI version: 5.2.2)

Does you sql server have several schemas? Did you grant more rights to your accounts on databases / tables?
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.