Error While Transfering System Versioned Temporal Tables

[Garwin4j]

Hello,

While doing a direct database Transfer using the SMO.Transfer Object, I get this error at the System Versioned Temporal Tables:

Unhandled exception. Microsoft.SqlServer.Management.Common.TransferException: An error occurred while transferring data. See the inner exception for details. ---> System.InvalidOperationException: The given ColumnMapping does not match up with any column in the source or destination. at Microsoft.Data.SqlClient.SqlBulkCopy.AnalyzeTargetAndCreateUpdateBulkCommand(BulkCopySimpleResultSet internalResults) at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource1 source) at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource1 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(DbDataReader reader) at Microsoft.SqlServer.Management.Smo.Transfer.SqlBulkCopyData(SqlConnection sourceConnection, SqlConnection destinationConnection, TransferWriter writer, SqlTransaction transaction) at Microsoft.SqlServer.Management.Smo.Transfer.TransferData() --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()

I am hoping that there is a flag that I need to turn on or that because I turned off a setting this has happened. Here is my current setup for my Transfer object:

`var transfer = new Transfer(sourceDB);

        transfer.CopyAllObjects = false;
        transfer.CopyData = true;
        transfer.CopyAllTables = false;
        transfer.CopyAllViews = false;

        transfer.CopyAllStoredProcedures = false;
        transfer.CopyAllUserDefinedFunctions = false;
        transfer.PrefetchObjects = true;

        transfer.Options.WithDependencies = true;
        transfer.Options.ContinueScriptingOnError = true;
        transfer.Options.IncludeIfNotExists = true;
        transfer.Options.AllowSystemObjects = false;
        transfer.Options.Indexes = true;
        transfer.Options.ClusteredIndexes = true;
        transfer.Options.NonClusteredIndexes = true;
        transfer.Options.DriAll = true; 
        transfer.Options.ChangeTracking = true;
        transfer.Options.EnforceScriptingOptions = false;

        transfer.Options.Bindings = true;
        transfer.Options.IncludeHeaders = true;
        transfer.Options.ClusteredIndexes = true;
        transfer.Options.ExtendedProperties = true;
        transfer.Options.AllowSystemObjects = true;
        transfer.Options.Permissions = false;
        transfer.DestinationDatabase = destinationDBName;
        transfer.DestinationServer = destinationSQLInstanceName;
        transfer.DestinationPassword = destinationSQLPassword;
        transfer.DestinationLogin = destinationSQLUser;
        transfer.DestinationLoginSecure = false;

`

With a custom list of tables and Views.

[shueybubbles]

thx for opening an issue! Can you share the contents of the column mapping that leads to the error? Maybe SMO is trying to copy an auto-generated column.

[Garwin4j]

Really happy you reached out.

I am not sure how to do that. For more context, I am using Transfer to Copy a database schema and data from one db to a new db, so the destination is completely blank. This process was working before my team incorporated a System Version Temporal Tables.

I can show you the creation script for the table bellow:

CREATE TABLE [dbo].[ProviderClientPrograms](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ProgramId] [int] NOT NULL,
    [ProviderClientId] [int] NOT NULL,
    [StartDate] [datetime2](7) NULL,
    [EndDate] [datetime2](7) NULL,
    [IsEnabled] [bit] NOT NULL,
    [CreatedOn] [datetime2](7) NOT NULL,
    [CreatedById] [int] NULL,
    [LastModified] [datetime2](7) NOT NULL,
    [ModifiedById] [int] NULL,
    [ImportId] [int] NULL,
    [ImportIdList] [nvarchar](max) NULL,
    [PeriodEnd] [datetime2](7) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    [PeriodStart] [datetime2](7) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
 CONSTRAINT [PK_ProviderClientPrograms] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
    PERIOD FOR SYSTEM_TIME ([PeriodStart], [PeriodEnd])
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[ProviderClientProgramsHistory])
)
GO

ALTER TABLE [dbo].[ProviderClientPrograms] ADD  DEFAULT ('9999-12-31T23:59:59.9999999') FOR [PeriodEnd]
GO

ALTER TABLE [dbo].[ProviderClientPrograms] ADD  DEFAULT ('0001-01-01T00:00:00.0000000') FOR [PeriodStart]
GO

ALTER TABLE [dbo].[ProviderClientPrograms]  WITH CHECK ADD  CONSTRAINT [FK_ProviderClientPrograms_AspNetUsers_CreatedById] FOREIGN KEY([CreatedById])
REFERENCES [dbo].[AspNetUsers] ([Id])
GO

ALTER TABLE [dbo].[ProviderClientPrograms] CHECK CONSTRAINT [FK_ProviderClientPrograms_AspNetUsers_CreatedById]
GO

ALTER TABLE [dbo].[ProviderClientPrograms]  WITH CHECK ADD  CONSTRAINT [FK_ProviderClientPrograms_AspNetUsers_ModifiedById] FOREIGN KEY([ModifiedById])
REFERENCES [dbo].[AspNetUsers] ([Id])
GO

ALTER TABLE [dbo].[ProviderClientPrograms] CHECK CONSTRAINT [FK_ProviderClientPrograms_AspNetUsers_ModifiedById]
GO

ALTER TABLE [dbo].[ProviderClientPrograms]  WITH CHECK ADD  CONSTRAINT [FK_ProviderClientPrograms_ProviderClients_ProviderClientId] FOREIGN KEY([ProviderClientId])
REFERENCES [dbo].[ProviderClients] ([Id])
GO

ALTER TABLE [dbo].[ProviderClientPrograms] CHECK CONSTRAINT [FK_ProviderClientPrograms_ProviderClients_ProviderClientId]
GO

ALTER TABLE [dbo].[ProviderClientPrograms]  WITH CHECK ADD  CONSTRAINT [FK_ProviderClientPrograms_ProviderPrograms_ProgramId] FOREIGN KEY([ProgramId])
REFERENCES [dbo].[ProviderPrograms] ([Id])
GO

ALTER TABLE [dbo].[ProviderClientPrograms] CHECK CONSTRAINT [FK_ProviderClientPrograms_ProviderPrograms_ProgramId]
GO

[shueybubbles]

interesting. Our internal tests for Transfer work fine for temporal tables with hidden row end columns like this, but it's possible we aren't using the exact combination of settings you use. As for "how to do that" you'd need to run the app in a debugger or debug a full memory dump of the crash and break on the exception to see if there's any object on the call stack that has the mapping it fails on.

[shueybubbles]

for comparison this is the settings our internal tests use.

var transfer = new Management.Smo.Transfer()
{
    PreserveDbo = true,
    Database = db,
    CopyData = true,
    CopyAllUsers = true,
    CopyAllObjects = true,
    CopyAllTables = true,
    CreateTargetDatabase = ServerContext.DatabaseEngineType == DatabaseEngineType.Standalone,
    DropDestinationObjectsFirst = true,
    UseDestinationTransaction = this.ServerContext.VersionMajor >= 12 && !db.HasMemoryOptimizedObjects,
    DestinationDatabase = destinationDatabaseName,
    DestinationServer = db.Parent.Name,
    DestinationServerConnection = useDestinationServerConnection ? destinationServerConnection : null,
    DatabaseFileMappings = new DatabaseFileMappingsDictionary(),
    Options =
        {
            FullTextCatalogs = true,
            FullTextIndexes = true,
            FullTextStopLists = ServerContext.VersionMajor >= 10,
            Permissions = true,
            ScriptOwner = true,
            ExtendedProperties = true,
            Statistics = true,
            SchemaQualify = true,
            WithDependencies = true,
            IncludeDatabaseRoleMemberships = true,
            EnforceScriptingOptions = true,
            Bindings = true,
            AnsiPadding = true,
            Indexes = true,
            DriAll = true,
            Triggers = true,
            SchemaQualifyForeignKeysReferences = true,
            DriIncludeSystemNames = true,
        }
};

[Garwin4j]

Okay, let me try this and see. Thanks.