SQLCompare Object Existence with Create or Alter
lysaer
Posts: 3 New member
in SQL Compare
We're using SQL Toolbox to generate deployment scripts comparing from TFS to a template database. We've been using these this way for a while, but recently upgraded SQL to 2017. Our developers are now using the CREATE OR ALTER PROCEDURE command in their stored procs, and when we generate the deployment script using object existence checks, we're ending up with this:
IF OBJECT_ID(N'[dbo].[stored_procedure_name]', 'P') IS NULL
EXEC sp_executesql N'CREATE OR ALTER PROCEDURE [dbo].[stored_procedure_name](@column INT)
If it does not exist, it creates, but if it does exist, it does not execute the alter.
If it does not exist, it creates, but if it does exist, it does not execute the alter.
Tagged:
Answers
I believe we have just ensured that when we read a scripts folder that contains the CREATE OR ALTER syntax we don't throw a parsing error rather than taking into account all possible interactions with that syntax.
We don't (or perhaps can't - I'm going to find out) show a sproc created using that syntax on a live database with that syntax in the Compare UI (i.e. I created this on a database using CREATE OR ALTER and it shows up only as CREATE), but going from a scripts folder where CREATE OR ALTER is used we do show it then and that is when the object existence issue comes in.
I'm going to escalate to the developers to see if we can a) see if this was how an object was created in a database and b) if we can accommodate this syntax with the object existence checks.
I'll update here when I have further information!
Kind regards,
Alex
Have you visited our Help Center?
I think what's happening is the result of the object existence is contextual. If the script file starts with alter, the generated script has IF OBJECT NOT NULL alter. If it starts with create, it generates IF OBJECT NULL create, and it's not taking the "or alter" context in.
We have a temporary work around to force drop and create instead of alter (dacia option), but that seems like overkill in the long term.
Righto, the issue is that we've only prevented scripts that have the OR ALTER portion added from causing a parsing error. SQL Compare will never create scripts like that since we can't determine from SQL Server when that syntax was used to create the object and when we write out the objects they will only be written using CREATE PROCEDURE without the ALTER (and will never create a script with just ALTER PROCEDURE since the scripts are object creation scripts).
I have created an enhancement request to add better support for the CREATE OR ALTER syntax found in a scripts folder with reference SC-10705. I've also found this SQL Compare Uservoice suggestion which you should vote on as well: https://redgate.uservoice.com/forums/141379-sql-compare/suggestions/19383934-support-create-or-alter-instead-of-create
Kind regards,
Alex
Have you visited our Help Center?