Using Bulk Copy to insert uniqueidentifier to Azure Data Warehouse results in invalid column type

Question

Our application uses the MSSQL JDBC driver to do raw bulk copy inserts to Azure Synapse Dedicated Pool (Data Warehouse), but there seems to be a bug in Data Warehouse where attempting to do bulk copy inserts with uniqueidentifier data type will result in this error:

com.microsoft.sqlserver.jdbc.SQLServerException: 110802;An internal DMS error occurred that caused this operation to fail. Details: Please use this Error ID when contacting your Administrator for assistance. EID:(d65450e6dc884758a9be76dbc1d88e25) SqlNativeBufferBufferBulkCopy.WriteTdsDataToServer, error in OdbcDone: SqlState: 42000, NativeError: 4816, 'Error calling: bcp_done(this->GetHdbc()) | SQL Error Info: SrvrMsgState: 1, SrvrSeverity: 16, Error <1>: ErrorMsg: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column type from bcp client for colid 1. | Error calling: pConn->Done() | state: FFFF, number: 418, active connections: 3', Connection String: Driver={pdwodbc17e};app=TypeD00-DmsNativeWriter:DB40\mpdwsvc (24828)-ODBC;autotranslate=no;trusted_connection=yes;server=\\.\pipe\DB.40-feeb931069d5-0\sql\query;database=Distribution_58

I've previously reported this to the mssql-jdbc driver team here, but they've advised that this is an issue with Azure Data Warehouse.

A similar issue was fixed here, but it looks like the uniqueidentifier type is still affected.

Casting the value to uniqueidentifier might work for batched statements, but that isn't currently doable with our infrastructure.

Are there any other workarounds for this issue that only use the raw bulk copy from MSSQL JDBC driver?

Answer 1

Hi FMEExpert ,

Welcome to Microsoft Q&A platform and thanks for posting your query on the platform.

As I understand your issue, you are trying to perform Bulk copy insert into Azure Synapse Datawareshouse , however , you are facing an error in case the table contains uniqueidentifier column. Please let me know if that is not the concern here.

Could you please try the below approach and see if it helps you:

Try to do a CAST to UNIQUEIDENTIFIER to perform the bulk insert.

For example:

DECLARE @val UNIQUEIDENTIFIER = CAST(? AS UNIQUEIDENTIFIER); INSERT INTO uidTable VALUES(@val);

Check out this post for more details: Using Bulk Copy to insert uniqueidentifier to Azure Data Warehouse results in invalid column type

Please let us know if it helped resolving your issue. Thankyou.

Answer 2

The other way of doing it is place the file in the Azure ADLS Gen 2 or Blob, Create external table with Varchar(max). While inserting the data into the dedicated table you can cast the columns and insert.