[jira] [Created] (SQOOP-1507) Sqoop2 export to MSSQL fails

["Oleksiy Sayankin (JIRA)" <ji...@apache.org>]

Oleksiy Sayankin created SQOOP-1507:
---------------------------------------

             Summary: Sqoop2 export to MSSQL fails
                 Key: SQOOP-1507
                 URL: https://issues.apache.org/jira/browse/SQOOP-1507
             Project: Sqoop
          Issue Type: Bug
          Components: sqoop2-server
    Affects Versions: 1.99.3, 2.0.0, 1.99.4
         Environment: MS Windows
MS SQL Server
            Reporter: Oleksiy Sayankin
             Fix For: 1.99.3


One of our customer reported sqoop2 failure when exporting
table to MSSQL. The export fails during transfer from stage table to target
table. 

<JT stderr logs>
2014-07-15 19:33:11,681 [ main ] INFO 
org.apache.sqoop.connector.jdbc.GenericJdbcExportDestroyer  - Job completed,
transferring data from stage table to destination table.
log4j:ERROR Attempted to append to closed appender named [ maprfsTLA ].
2014-07-15 19:33:11,933 [ main ] ERROR
org.apache.sqoop.connector.jdbc.GenericJdbcExecutor  - Got SQLException while
migrating data from: reg_stage to: reg
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'SELECT'.
    at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
    at
com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
    at
com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792)
    at
com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
    at
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
    at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
    at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
    at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:642)
    at
org.apache.sqoop.connector.jdbc.GenericJdbcExecutor.migrateData(GenericJdbcExecutor.java:91)
    at
org.apache.sqoop.connector.jdbc.GenericJdbcExportDestroyer.moveDataToDestinationTable(GenericJdbcExportDestroyer.java:55)
    at
org.apache.sqoop.connector.jdbc.GenericJdbcExportDestroyer.destroy(GenericJdbcExportDestroyer.java:39)
    at
org.apache.sqoop.connector.jdbc.GenericJdbcExportDestroyer.destroy(GenericJdbcExportDestroyer.java:26)
    at
org.apache.sqoop.job.mr.SqoopDestroyerExecutor.executeDestroyer(SqoopDestroyerExecutor.java:65)
    at
org.apache.sqoop.job.mr.SqoopNullOutputFormat$DestroyerOutputCommitter.commitJob(SqoopNullOutputFormat.java:70)
    at org.apache.hadoop.mapred.Task.runJobCleanupTask(Task.java:1102)
    at org.apache.hadoop.mapred.MapTask.run(MapTask.java:333)
    at org.apache.hadoop.mapred.Child$4.run(Child.java:282)
    at java.security.AccessController.doPrivileged(Native Method)
    at javax.security.auth.Subject.doAs(Subject.java:415)
    at
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1117)
    at org.apache.hadoop.mapred.Child.main(Child.java:271)

</JT stderr logs>

Additional Info: 

- The issue is re-producible 
- Import from MSSQL works fine.

Steps to re-produce: 
    - Import a MSSQL table using sqoop2
    - Try to export the same table to MSSQL.

Job info (from my test box): 

---------------
sqoop:000> update job --jid 1
Updating job with id 1
Please update job metadata:
Name: to MSworld

Database configuration

Schema name: dbo
Table name: reg
Table SQL statement: 
Table column names: id,name
Stage table name: reg_stage
Clear stage table: true

Input configuration

Input directory: /sqoop/reg2
---------------

Throttling resources

Extractors: 
Loaders: 
Job was successfully updated with status FINE
----------------
The query looks like this:

INSERT INTO TestTable ( SELECT * FROM StageTable )

Apparently MSSQL does not allow parentheses around the SELECT part of the
statement.

This would work.
INSERT INTO TestTable SELECT * FROM StageTable



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)