Sql-server – AlwaysEncrypted Conversion Fails

always-encryptedsql serversql-server-2016

I'm trying to encrypt a column with AlwaysEncrypted and I get the following error.

Could not deploy package.

Warning SQL72012: The object [AlwaysEncryptedDemo] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

Warning SQL72012: The object [AlwaysEncryptedDemo_log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

Warning SQL72015: The table [dbo].[tmp_ms_xx_t1] is being dropped, data loss could occur.

Error SQL72014: .Net SqlClient Data Provider: Msg 50000, Level 16, State 127, Line 6 Rows were detected. The schema update is terminating because data loss might occur.

Error SQL72045: Script execution error.

The executed script:

IF EXISTS (SELECT TOP 1 1
           FROM   [dbo].[tmp_ms_xx_t1])
    RAISERROR (N'Rows were detected. The schema update is terminating' +
          ' because data loss might occur.', 16, 127)
        WITH NOWAIT;

This happens both in SSMS and using the generated PowerShell. The table schema is simply one column, nvarchar(10). We're only testing this out.

Can someone help me get past this error please?

Best Answer

So, this actually wasn't a bug. There is (was) a trigger on the table that was changing data. The changing data is what caused the conversion to fail. Disabling the trigger did not help, it had to be dropped.

Related Solutions

Sql-server – missing some output

You cannot loop through objects in the way you have coded. Here's the working code:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

$ServerName = 'xyzabc123'
$DatabaseName = 'test1'
$TableName = 'main'
$TableSchemaName = 'dbo'

$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName
$Server.ConnectionContext.SqlExecutionModes = [Microsoft.SqlServer.Management.Common.SqlExecutionModes]::CaptureSql

$Database = $Server.Databases[$DatabaseName]

$Table = $Database.Tables[$TableName, $TableSchemaName]

$TablesToArchive = @($Table)
$Database.Tables | Select -ExpandProperty ForeignKeys | % {
    if($_.ReferencedTable -eq $TableName -and $_.ReferencedTableSchema -eq $TableSchemaName)
        { $TablesToArchive += $_.Parent }
}
# $TablesToArchive | % { $_.Name }

$ObjectsToArchive = ($TablesToArchive | Select -ExpandProperty Indexes) + ($TablesToArchive | Select -ExpandProperty ForeignKeys)
# $ObjectsToArchive | % { $_.Name }

$ObjectsToArchive | % {
    $_.Rename($_.Name + '_archive')
}
$RenameCommands = $Server.ConnectionContext.CapturedSql.Text
# $RenameCommands

For example, you were expecting

$Database.Tables.ForeignKeys

To give you a combined ForeignKeys collection made up of ForeignKeys of each Tables object. If you added the debug commands

# $ObjectsToArchive | % { $_.Name }

You would have been able to track it down very quickly.

Sql-server – Publishing database changes with SSDT that include altering data types of columns

I've been tempted to bypass that flag also but have come down on the side of your co-workers and now try to deal with these issues "correctly". The (marginally) less clunky route is to use pre and post deployment scripts to do the work with a rename.

Rename the existing table in a pre-deployment script.
With the existing table missing the table in focus will be created as per the new schema definition.
In a post-deployment script copy from the renamed original table to the new version.

Depending on the nature of the target you may of course need to take care of dropping and recreating foreign key constraints.

Related Question