jbogard/Respawn

Reset fails in SQL Server when temporal table or history table are in non-default schemas

Closed this issue · 0 comments

I have a temporal table where both table and history table are in non-default (dbo) schema:

CREATE SCHEMA [TableSchema] AUTHORIZATION [dbo];
GO

CREATE SCHEMA [HistorySchema] AUTHORIZATION [dbo];
GO

create table TableSchema.Foo (
	Value [int] not null primary key clustered,
    ValidFrom datetime2 generated always as row start, 
    ValidTo datetime2 generated always as row end,
    period for system_time(ValidFrom, ValidTo)
) with (system_versioning = on (history_table = HistorySchema.FooHistory))
GO

If I try to reset the database:

var checkpoint = new Checkpoint();
checkpoint.CheckTemporalTables = true;
await checkpoint.Reset(_connection);

I get a SQL exception:

Cannot find the object "Foo" because it does not exist or you do not have permissions.