In SQL Compare can generated scripts do 'IF EXISTS....' check before adding columns?

JayR

We are having SQL Compare generate scripts to upgrade databases. We have a fairly complicated scenario where we need to add a new not null column to a table in pre-scripts and populate the new column with scripts. RedGate is generating scripts that attempt to add the column that is already there. It would be really great if there was a way to tell RedGate to check for the columns existence before the script attempts to adding the column.      

Alex B

Hi @JayR,

There is an option in SQL Compare to Add object existence checks:
​

Have you tried this or does it not add them for columns?

Kind regards,
Alex

JayR

This is what we are currently doing. And it does not appear to work with the new column. [string] $RedGateOptions = "CaseSensitiveObjectDefinition,IgnoretSQLt,IgnoreUsersPermissionsAndRoleMemberships,ObjectExistenceChecks,IgnoreUsers,NoTransactions,NoErrorHandling",

Alex B

Hi @JayR,

Hmm, to clarify - are you generating the SQL Compare deployment before or after you run the pre-script that generates and populates the new column?

SQL Compare should be generating it's deployment script based on the current state, which would seem to indicate that the deployment script is being generated before the pre-script that adds the new not-null column.  In that case, there's not much SQL Compare can do.

If that doesn't seem to be the case, can you give a bit more overview of what your process is and perhaps an example of the pre-script and the table source and target where the issue is occurring?

If needed, it looks like you are supported so I can reach out through a support ticket to get that information from you.

Kind regards,
Alex

JayR

Here is a copy of the script that was generated.  The column we added is called 'ARCo' .  Basically that entire chunk the the script should have an "if exists..." check so that it can be skipped if the column is already there. Possibly RedGate isn't putting in the appropriate "if exists..." because of various combination of not null/indexes/primary keys on the table. 

Script created by SQL Compare Engine version 12.4.10.4968 from Red Gate Software Ltd at 5/24/2019 10:36:02 PM

*/

SET NUMERIC_ROUNDABORT OFF

GO

SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON

GO

PRINT N'Dropping constraints from [dbo].[vARStatementDelivery]'

GO

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PK_vARStatementDelivery]', 'PK') AND parent_object_id = OBJECT_ID(N'[dbo].[vARStatementDelivery]', 'U'))

ALTER TABLE [dbo].[vARStatementDelivery] DROP CONSTRAINT [PK_vARStatementDelivery]

GO

PRINT N'Dropping index [IX_vARStatementDelivery_CustGroup_Customer] from [dbo].[vARStatementDelivery]'

GO

IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_vARStatementDelivery_CustGroup_Customer' AND object_id = OBJECT_ID(N'[dbo].[vARStatementDelivery]'))

DROP INDEX [IX_vARStatementDelivery_CustGroup_Customer] ON [dbo].[vARStatementDelivery]

GO

PRINT N'Rebuilding [dbo].[vARStatementDelivery]'

GO

CREATE TABLE [dbo].[RG_Recovery_1_vARStatementDelivery]

(

[ARStatementDeliveryID] [int] NOT NULL IDENTITY(1, 1),

[CustGroup] [dbo].[bGroup] NOT NULL,

[Customer] [dbo].[bCustomer] NOT NULL,

[Recipient] [varchar] (60) COLLATE Latin1_General_BIN NULL,

[Email] [varchar] (60) COLLATE Latin1_General_BIN NULL,

[Address1] [varchar] (60) COLLATE Latin1_General_BIN NULL,

[Address2] [varchar] (60) COLLATE Latin1_General_BIN NULL,

[City] [varchar] (30) COLLATE Latin1_General_BIN NULL,

[State] [char] (4) COLLATE Latin1_General_BIN NULL,

[Country] [char] (2) COLLATE Latin1_General_BIN NULL,

[PostalCode] [dbo].[bZip] NULL,

[DateSent] [dbo].[bDate] NULL,

[DeliveryMethod] [char] (1) COLLATE Latin1_General_BIN NOT NULL,

[DeliveryStatus] [char] (1) COLLATE Latin1_General_BIN NOT NULL,

[UniqueAttchID] [uniqueidentifier] NULL,

[ARCo] [dbo].[bCompany] NOT NULL

) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[RG_Recovery_1_vARStatementDelivery] ON

GO

INSERT INTO [dbo].[RG_Recovery_1_vARStatementDelivery]([ARStatementDeliveryID], [CustGroup], [Customer], [Recipient], [Email], [Address1], [Address2], [City], [State], [Country], [PostalCode], [DateSent], [DeliveryMethod], [DeliveryStatus], [UniqueAttchID]) SELECT [ARStatementDeliveryID], [CustGroup], [Customer], [Recipient], [Email], [Address1], [Address2], [City], [State], [Country], [PostalCode], [DateSent], [DeliveryMethod], [DeliveryStatus], [UniqueAttchID] FROM [dbo].[vARStatementDelivery]
....
...
...
...
..
...
...
...

Alex B

Hi @JayR,

Looks like you're still using version 12.4.10.4968 - is that for any particular reason?  It looks like you are supported so you should be able to install version 13 side by side with version 12.  Could you please do this and see if the same thing occurs in the latest 13.7.16?

Kind regards,
Alex

JayR

We are mid stream of doing some releases so I don't know that we will be able to fit that in. Has there been bug fixes for issues like this?

Alex B

Hi @JayR,

Actually, wait, the bit in the script you have there is the temp table recovery table

CREATE TABLE [dbo].[RG_Recovery_1_vARStatementDelivery]

We would create that table to be like the source, then insert the data from the original target table, and then delete the original and rename the recovery to the original name (to preserve data).  That's why the INSERT doesn't have an entry for the ARCo column as the target didn't have it to start.

Going over this again, SQL Compare wouldn't create an object existence for the recovery table as it is generated at the time so wouldn't exist and then dropping and renaming wouldn't collide with an existing object.  Where are you actually getting an error and what specifically does it say?

Also, I need to clarify this again as it sounds like you are comparing two tables - the source with the ARCo column and the target without - but then you are running a script outside of the SQL Compare process to add the ARCo column to the target and populate it with data which we can't account for.

Kind regards,
Alex

JayR

A simple default will not work for setting that column and also the column is not null and there is already data in the table. Given that complexity we needed to write a script to set the column value. RedGate cannot do that for us. The error we are getting is RedGate isn't wrapping that chunk of code in a "if exists...".  

Alex B

Hi @JayR,

Righto, so the main problem here is that columns are not considered their own objects in SQL Compare and so the object existence check isn't going to work for them and thus there isn't going to be a way to get this to work for you with the current functionality.

I was getting stuck on thinking the object existence check was for the temp table itself rather than seeing if the column existed to determine whether the temp table needed to be created, drop original and rename temp all needed to happen.

You should create a suggestion on the SQL Compare Uservoice Forum https://redgate.uservoice.com/forums/141379-sql-compare for this.

Kind regards,
Alex