SqlBulkCopy.WriteToServer is not working with Fabric Data Warehouse (Azure) #2995
Description
When I try to write a DataTable to the user with the WriteToServer, I receive the the following exception:
Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): INSERT is not a supported statement type.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at Microsoft.Data.SqlClient.SqlBulkCopy.RunParser(BulkCopySimpleResultSet bulkCopyHandler)
at Microsoft.Data.SqlClient.SqlBulkCopy.SubmitUpdateBulkCommand(String TDSCommand)
at Microsoft.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
at Microsoft.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)
at AzureSQLMFA.Program.Main() in C:\Developer\CSharp\2022\Budsjettprosjekter\AzureSQLMFA\AzureSQLMFA\Program.cs:line 246
ClientConnectionId:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Error Number:22424,State:0,Class:16
ClientConnectionId before routing:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Routing Destination:XXXX.pbidedicated.windows.net\XXXXXXXXXXXXXXXX-dw,1433
I am using the following code:
bulkCopy.DestinationTableName = "Personnel";
bulkCopy.WriteToServer(personnelDataTable);
The same code is working as expected when I use an on-premise MS SQL Server 2022:
I am able to insert data to the warehouse in Azure if I use the SqlCommand object with INSERT INTO statement.
I am using the following technologies:
Microsoft.Data.SqlClient version 5.2.2: (installed with nuget packet manager)
.NET target: Core 8.0
SQL Server version: Microsoft Azure SQL Data Warehouse (RTM) - 12.0.2000.8
Operating system: Windows 11 Home (23H2)