Hi,we use aqua studio V18.5 on a Ms Sql 2016 Server to compare two databases with the schema compare tool. Between the source and destination database are some differences in datatypes. Unfortunatly in a primary key column. Aqua studio recognizes it successfully and create an alter column. But running the script it stops at this point. Sql can’t handle the alter columns, if it refers to a primary key. So I have to drop the primary key first, and create it afterwards.
Why does’nt Aqua studio do this for me ? Is there any option where I can set this feature ?
I think you are referring to Schema Sychronization rather than Schema Compare. I ran the following test case and it worked correctly for me.
I created these two tables:
--SOURCECREATE TABLE [dbo].[t100] ( [c1] bigint NOT NULL, [c2] varchar(25) NULL )ON [PRIMARY] WITH ( DATA_COMPRESSION = NONE )GO
--TARGETCREATE TABLE [dbo].[t100] ( [c1] int NOT NULL, [c2] varchar(25) NULL, CONSTRAINT [t100p] PRIMARY KEY CLUSTERED([c1]) ON [PRIMARY])ON [PRIMARY] WITH ( DATA_COMPRESSION = NONE )GO
I ran Schema Sychronization which gave me the following script:
// Script generated by Aqua Data Studio Schema Synchronization for MS SQL Server 2016 on Wed Jun 20 12:52:34 PDT 2018// Execute this script on:// tom_target/dbo - This database/schema will be modified// to synchronize it with MS SQL Server 2016:// tom_source/dbo
// We recommend backing up the database prior to executing the script.
SET XACT_ABORT ONGOSET TRANSACTION ISOLATION LEVEL SERIALIZABLEGOBEGIN TRANSACTIONGOIF @@ERROR <> 0 SET NOEXEC ONGO
// Drop primary key constraint dbo.t100pPRINT N'Drop primary key constraint dbo.t100p'GOALTER TABLE [dbo].[t100] DROP CONSTRAINT [t100p]GOIF @@ERROR <> 0 SET NOEXEC ONGO
// Alter table dbo.t100PRINT N'Alter table dbo.t100'GOALTER TABLE [dbo].[t100] ALTER COLUMN [c1] bigint NULLGOIF @@ERROR <> 0 SET NOEXEC ONGO
COMMIT TRANSACTIONGOIF @@ERROR <> 0 SET NOEXEC ONGODECLARE @Success AS BITSET @Success = 1SET NOEXEC OFFIF (@Success = 1) PRINT 'The database update succeeded'ELSE BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION PRINT 'The database update failed'ENDGO
Notice that the drop constraint happens before the alter column. If your test case is different, please include it so I can analyze it.
Yes, I am referring to Schema Synchronization.
Your test case is exactly like my case.
Drop Constraint and Alter Column is all right in my case, too.
But what about recreating the primary key for c1 afterwards ?
With the test case above, we would not create a primary key for the target table since the source table does not have a primary key. Remember, we are synchronizing the source to the target.
Hope that helps, Tom
I had a primary key in my source table. I changed your example script and it run’s fine.
But in my productive script which run about 150 tables and exits of 20tsd lines I’m still getting errors.
I suppose it has something to do with statistics. Next I try to delete them before.
I go ahead with my work and may come back if I need additional help.
Thank you. Achim
Ok. Let us know if you find other issues.