Reply
Contributor
Posts: 48
Registered: ‎05-30-2015

sqoop with MS sql server 2005

Hi there,

 

I have this sqoop problem with MS SQL sedrver 2005 on my Cloudera setup, I tried to use sqljdbc4.0.jar to list the database it gave me the following error.

 

#sqoop list-databases --connect jdbc:sqlserver://ip address:1433 --username user --password password


Warning: /opt/cloudera/parcels/CDH-5.4.9-1.cdh5.4.9.p0.19/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
16/01/29 14:58:15 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5-cdh5.4.9
16/01/29 14:58:15 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
16/01/29 14:58:15 INFO manager.SqlManager: Using default fetchSize of 1000
Jan 29, 2016 2:58:16 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 ClientConnectionId: 654ff20e-f663-4af0-9ec2-99832ef4e78d Server major version:8 is not supported by this driver.
16/01/29 14:58:16 ERROR manager.CatalogQueryManager: Failed to list databases
com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server version 8 is not supported by this driver. ClientConnectionId:654ff20e-f663-4af0-9ec2-99832ef4e78d
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1667)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1654)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.Prelogin(SQLServerConnection.java:1556)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1319)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:215)
    at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:880)
    at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
    at org.apache.sqoop.manager.CatalogQueryManager.listDatabases(CatalogQueryManager.java:57)
    at org.apache.sqoop.tool.ListDatabasesTool.run(ListDatabasesTool.java:49)
    at org.apache.sqoop.Sqoop.run(Sqoop.java:143)
    at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
    at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227)
    at org.apache.sqoop.Sqoop.main(Sqoop.java:236)
16/01/29 14:58:16 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server version 8 is not supported by this driver. ClientConnectionId:654ff20e-f663-4af0-9ec2-99832ef4e78d
java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server version 8 is not supported by this driver. ClientConnectionId:654ff20e-f663-4af0-9ec2-99832ef4e78d
    at org.apache.sqoop.manager.CatalogQueryManager.listDatabases(CatalogQueryManager.java:73)
    at org.apache.sqoop.tool.ListDatabasesTool.run(ListDatabasesTool.java:49)
    at org.apache.sqoop.Sqoop.run(Sqoop.java:143)
    at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
    at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227)
    at org.apache.sqoop.Sqoop.main(Sqoop.java:236)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server version 8 is not supported by this driver. ClientConnectionId:654ff20e-f663-4af0-9ec2-99832ef4e78d
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1667)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1654)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.Prelogin(SQLServerConnection.java:1556)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1319)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:215)
    at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:880)
    at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
    at org.apache.sqoop.manager.CatalogQueryManager.listDatabases(CatalogQueryManager.java:57)
    ... 7 more

 

I was told that I need to use jtds driver which I downloaded the latest 1.3.1 and put under /var/lib/sqoop, it gave me another error as follow:

 

#sqoop list-databases --connect jdbc:jtds:sqlserver://ip address:1433 --username user --password password

Warning: /opt/cloudera/parcels/CDH-5.4.9-1.cdh5.4.9.p0.19/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
16/01/29 15:01:09 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5-cdh5.4.9
16/01/29 15:01:09 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
16/01/29 15:01:10 INFO manager.SqlManager: Using default fetchSize of 1000
16/01/29 15:01:11 ERROR manager.CatalogQueryManager: Failed to list databases
java.sql.SQLException: Invalid object name 'SYS.DATABASES'.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
    at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:505)
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1427)
    at org.apache.sqoop.manager.CatalogQueryManager.listDatabases(CatalogQueryManager.java:59)
    at org.apache.sqoop.tool.ListDatabasesTool.run(ListDatabasesTool.java:49)
    at org.apache.sqoop.Sqoop.run(Sqoop.java:143)
    at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
    at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227)
    at org.apache.sqoop.Sqoop.main(Sqoop.java:236)
16/01/29 15:01:11 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeException: java.sql.SQLException: Invalid object name 'SYS.DATABASES'.
java.lang.RuntimeException: java.sql.SQLException: Invalid object name 'SYS.DATABASES'.
    at org.apache.sqoop.manager.CatalogQueryManager.listDatabases(CatalogQueryManager.java:73)
    at org.apache.sqoop.tool.ListDatabasesTool.run(ListDatabasesTool.java:49)
    at org.apache.sqoop.Sqoop.run(Sqoop.java:143)
    at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
    at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227)
    at org.apache.sqoop.Sqoop.main(Sqoop.java:236)
Caused by: java.sql.SQLException: Invalid object name 'SYS.DATABASES'.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
    at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:505)
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1427)
    at org.apache.sqoop.manager.CatalogQueryManager.listDatabases(CatalogQueryManager.java:59)
    ... 7 more

 

Not sure if anybody have the similar experience before?

 

Posts: 1,893
Kudos: 432
Solutions: 302
Registered: ‎07-31-2013

Re: sqoop with MS sql server 2005

Does your user have permissions on the SQLServer-end to access all DBs
(i.e. to list them via internal table SYS.DATABASES)?

Also, are you absolutely certain your DB is SQL Server 2005? The page at
https://msdn.microsoft.com/en-us/library/ms378422.aspx does claim that the
v4.0 should support SQL Server 2005; But you get an error that appears to
indicate your server is running SQL Server 2000 instead (per
https://support.microsoft.com/en-us/kb/321185#kb-link-56 version 8 would
mean an SQL Server 2000).
Highlighted
Contributor
Posts: 48
Registered: ‎05-30-2015

Re: sqoop with MS sql server 8

Thank you Harsh J, Just check with the DB Admin, it seems that the SQL is version 8, you are right.

 

The DB admin had given the public anf db_datareader, I still getting the sys.databases error.

Announcements
New solutions