Don't forget to reboot after installing SQL Server 2016 CTP 3.3

On a SQL 2016 CTP 3.2 server I was writing PowerShell scripts to bulk copy data into a database with simple ADO.NET classes. It was working perfectly fine to start with.

$bulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($bulkCopyConnection, [System.Data.SqlClient.SqlBulkCopyOptions]::Default, $bulkCopyTransaction)

foreach ($table in $dataSet.Tables) {
    Write-Verbose "Saving $($table.TableName)"
    $bulkCopy.DestinationTableName = "[$schemaName].[$($table.TableName)]"

    # Required in case we've added columns, they will not be in order, and as long as you specify the names here it will all work okay
    $bulkCopy.ColumnMappings.Clear()
    $table.Columns | %{
        $bulkCopy.ColumnMappings.Add((New-Object System.Data.SqlClient.SqlBulkCopyColumnMapping($_.ColumnName, $_.ColumnName))) | Out-Null
    }
    $bulkCopy.WriteToServer($table)
}

But as I needed to spread the load I installed SQL 2016 CTP 3.3 onto another server and copied the scripts over. They immediately started throwing a weird exception that appeared to have nothing to do with my code.

Errors             : {System.Data.SqlClient.SqlError: Expression type bit is invalid for COLLATE clause.}
ClientConnectionId : 539d4ba3-fdd1-4a82-a42d-fa874d7ae433
Class              : 16
LineNumber         : 1
Number             : 447
Procedure          :
Server             : .\SQL2016
State              : 1
Source             : .Net SqlClient Data Provider
ErrorCode          : -2146232060
Message            : Expression type bit is invalid for COLLATE clause.
Data               : {HelpLink.ProdName, HelpLink.ProdVer, HelpLink.EvtSrc, HelpLink.EvtID...}
InnerException     :
TargetSite         : Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action])
StackTrace         :    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
                        at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
                        at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler,
                     TdsParserStateObject stateObj, Boolean& dataReady)
                        at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
                     stateObj)
                        at System.Data.SqlClient.SqlBulkCopy.RunParser(BulkCopySimpleResultSet bulkCopyHandler)
                        at System.Data.SqlClient.SqlBulkCopy.SubmitUpdateBulkCommand(String TDSCommand)
                        at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1
                     source)
                        at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
                        at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
                        at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
                        at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
                        at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
                        at CallSite.Target(Closure , CallSite , Object , Object )
HelpLink           :
HResult            : -2146232060

Just to be sure I wasn't missing something, I traced the code and errors from the database side and it was indeed the bulk insert statement causing SQL Server to throw the error. When I ran the code on the old server, it could write to the old database server or the new database server. But when I ran the code on the new server it would fail writing to either database server!

It was a real head scratcher and I lost a good lot of time trying to work out why it was happening and which part of the stack was broken; when I remembered I never rebooted the new server after the CTP 3.3 install.

I thought there was no way this could possibly be related… and yet after I rebooted the code immediately started working and has continued working since! So let that be a reminder that sometimes you really really need to reboot after a SQL Server install otherwise it can cause weird issues.

It's possible the CTP installs a .NET update which causes this to happen. These outstanding .NET updates have been causing me a lot of grief lately but they can be picked up by checking a registry key; it's important because they can cause Data Quality Services (DQS) to fail and SQLCLR to repeatedly refuse to load. Both of those problems can be picked up in the SQL errorlog.

Share on: