Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

sqoop with MS sql server 2005

sqoop with MS sql server 2005

Rising Star

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?

 

2 REPLIES 2

Re: sqoop with MS sql server 2005

Master Guru
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).

Re: sqoop with MS sql server 8

Rising Star

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.