Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Spoon cannot explore MSSQL 2008 - Error getting views from schema [null]

  1. #1

    Question Spoon cannot explore MSSQL 2008 - Error getting views from schema [null]

    I'm using Spoon pdi-ce-6.0.0.0-353 to migrate data from MSSQL to Mysql.

    I'm using MS SQL 2008.
    jdbc driver: sqljdbc42 (copied to /lib)
    Java: jdk-1.8.0
    I tried some Spoon options:
    1) Connection type: MS SQL server(Native)
    Access: Native (JDBC)
    2) Connection type: MS SQL server
    Access: JNDI

    To configure JNDI option: add these line into simple-jndi/jdbc.properties:
    SQLServer/type=javax.sql.DataSource
    SQLServer/driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
    SQLServer/url=jdbc:sqlserver://192.168.2.14:1433;DatabaseName=test
    SQLServer/user=sa
    SQLServer/password=test123

    With both options, I could connect to my MSSQL [Test is successful] but when I clicked to explore MSSQL database.
    I got this error:

    java.lang.reflect.InvocationTargetException: Problem encountered getting information from the database: org.pentaho.di.core.exception.KettleDatabaseException:
    Unable to retrieve database information because of an error


    Error getting views from schema [null]
    at org.pentaho.di.ui.core.database.dialog.GetDatabaseInfoProgressDialog$1.run(GetDatabaseInfoProgressDialog.java:71)
    at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:113)
    Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    Unable to retrieve database information because of an error

    The first, I think this error because of my Spoon to MSSQL connection is not encrypted. So I try to add parameter ssl=required to Spoon. But It's still error.

    Second, I can connect to MSSQL and show tables by some sql client tools as heidisql, SQL Server Management Studio...
    I tried use other data migration tool as Mysql Workbench to migrate my data. And It was success.

    I think this error is not MSSQL server configuration.
    I've tried to google a lot, but I've not solved this problem yet.

    Can anyone help me?

    Thank you in advance
    Steven Nguyen
    Last edited by minhduongmda; 11-18-2015 at 04:15 AM.

  2. #2
    Join Date
    Dec 2009
    Posts
    332

    Default

    This sounds like a permission problem on the user account. Are the other tools you are utilizing to browse the tables using the same jdbc driver: sqljdbc42 ?

    I am using PDI6 to explore a few SQL Server databases using jtds-1.2.5.jar (net.sourceforge.jtds.jdbc.Driver) which comes with PDI6. Have you attempted using this class?

  3. #3

    Default

    I don't use other tools to try to browse tables with sqljdbc42. But I tried with other PC: jdk1.6, sqljdbc4 and Spoon 4.4. Then I could browse my tables.
    I made a test, I could migrate data from mssql server to XML file. But when I tried to migrate data from mssql to mysql server.
    I met other problem with mysql connection (I think this problem relates to mysql connector driver- I used com.mysql.jdbc_5.1.5.jar, or connector/J5.1.38).

    At other PC: Jdk1.6
    1. Create connection to mssql and mysql server
    2. Test connection and explore them: well done
    3. Make a transformation: a mssql table to a mysql table
    4. Result: communications link failure

    This is log:

    org.pentaho.di.core.exception.KettleException:
    Error batch inserting rows into table [S_LOG_DETECT_PT_U].
    Errors encountered (first 10):
    Communications link failure


    Error updating batch
    Communications link failure




    at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:404)
    at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:128)
    at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
    at java.lang.Thread.run(Unknown Source)
    Caused by: org.pentaho.di.core.exception.KettleDatabaseBatchException:
    Error updating batch
    Communications link failure

    Unexpected error committing the database connection.
    java.lang.NullPointerException
    at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedStatement.java:623)
    at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1486)
    at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:658)
    at org.pentaho.di.trans.step.RunThread.run(RunThread.java:79)
    at java.lang.Thread.run(Unknown Source)
    java.lang.NullPointerException
    at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedStatement.java:623)
    at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1486)
    at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:658)
    at org.pentaho.di.trans.step.RunThread.run(RunThread.java:79)
    at java.lang.Thread.run(Unknown Source)
    Last edited by minhduongmda; 12-17-2015 at 08:04 PM.

  4. #4

    Default

    With PDI6 and jdk1.8, I tried to use jdts-1.2.5.jar with I met error driver when try to "Test" database connection. Even I updated to jdts-1.3.1, it has same error.

    Error Logs:

    Error connecting to database [mssql] : org.pentaho.di.core.exception.KettleDatabaseException:
    Error occurred while trying to connect to the database


    Error connecting to database: (using class net.sourceforge.jtds.jdbc.Driver)
    I/O Error: DB server closed connection.




    org.pentaho.di.core.exception.KettleDatabaseException:
    Error occurred while trying to connect to the database


    Error connecting to database: (using class net.sourceforge.jtds.jdbc.Driver)
    I/O Error: DB server closed connection.




    at org.pentaho.di.core.database.Database.normalConnect(Database.java:459)
    at org.pentaho.di.core.database.Database.connect(Database.java:357)
    at org.pentaho.di.core.database.Database.connect(Database.java:328)
    at org.pentaho.di.core.database.Database.connect(Database.java:318)
    at org.pentaho.di.core.database.DatabaseFactory.getConnectionTestReport(DatabaseFactory.java:80)
    at org.pentaho.di.core.database.DatabaseMeta.testConnection(DatabaseMeta.java:2719)
    at org.pentaho.ui.database.event.DataHandler.testDatabaseConnection(DataHandler.java:588)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.pentaho.ui.xul.impl.AbstractXulDomContainer.invoke(AbstractXulDomContainer.java:313)
    at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:157)
    at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:141)
    at org.pentaho.ui.xul.swt.tags.SwtButton.access$500(SwtButton.java:43)
    at org.pentaho.ui.xul.swt.tags.SwtButton$4.widgetSelected(SwtButton.java:136)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.eclipse.jface.window.Window.runEventLoop(Window.java:820)
    at org.eclipse.jface.window.Window.open(Window.java:796)
    at org.pentaho.ui.xul.swt.tags.SwtDialog.show(SwtDialog.java:389)
    at org.pentaho.ui.xul.swt.tags.SwtDialog.show(SwtDialog.java:318)
    at org.pentaho.di.ui.core.database.dialog.XulDatabaseDialog.open(XulDatabaseDialog.java:116)
    at org.pentaho.di.ui.core.database.dialog.DatabaseDialog.open(DatabaseDialog.java:60)
    at org.pentaho.di.ui.spoon.delegates.SpoonDBDelegate.editConnection(SpoonDBDelegate.java:89)
    at org.pentaho.di.ui.spoon.Spoon.doubleClickedInTree(Spoon.java:3087)
    at org.pentaho.di.ui.spoon.Spoon.doubleClickedInTree(Spoon.java:3021)
    at org.pentaho.di.ui.spoon.Spoon.access$2400(Spoon.java:347)
    at org.pentaho.di.ui.spoon.Spoon$27.widgetDefaultSelected(Spoon.java:6064)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.pentaho.di.ui.spoon.Spoon.readAndDispatch(Spoon.java:1339)
    at org.pentaho.di.ui.spoon.Spoon.waitForDispose(Spoon.java:7939)
    at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:9214)
    at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:653)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.pentaho.commons.launcher.Launcher.main(Launcher.java:92)
    Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    Error connecting to database: (using class net.sourceforge.jtds.jdbc.Driver)
    I/O Error: DB server closed connection.


    at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:572)
    at org.pentaho.di.core.database.Database.normalConnect(Database.java:443)
    ... 45 more
    Caused by: java.sql.SQLException: I/O Error: DB server closed connection.
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2481)
    at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:632)
    at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:371)
    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 org.pentaho.di.core.database.Database.connectUsingClass(Database.java:554)
    ... 46 more
    Caused by: java.io.IOException: DB server closed connection.
    at net.sourceforge.jtds.jdbc.SharedSocket.readPacket(SharedSocket.java:852)
    at net.sourceforge.jtds.jdbc.SharedSocket.getNetPacket(SharedSocket.java:731)
    at net.sourceforge.jtds.jdbc.ResponseStream.getPacket(ResponseStream.java:477)
    at net.sourceforge.jtds.jdbc.ResponseStream.read(ResponseStream.java:114)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2368)
    ... 52 more

  5. #5
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by minhduongmda View Post
    Errors encountered (first 10):
    Communications link failure


    Error updating batch
    Communications link failure
    Quote Originally Posted by minhduongmda View Post
    Error connecting to database: (using class net.sourceforge.jtds.jdbc.Driver)
    I/O Error: DB server closed connection.
    This sounds like either the network or the server is configured to not be able to talk to the workstation you are connecting from.

  6. #6

    Default

    [Solved] I figured out my problem.

    1. PDI6 + jdk1.8/1.7 + sqljdbc42/jtds can't browse tables of MSSQL 2008 (with my case)
    2. PDI version < 6 + jdk 1.6 + sqljdbc4 can browse tables of MSSQL 2008
    3. Mysql connection is failed because in MySQL server, /var/ partition (mysql data is stored here) is full.

    Thank you for your support.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.