Why is SQL Source Control stripping "ALTER" out of stored procedures?

rpaul

Last week I tried to commit a stored procedure through SQL Source Control, and was surprised to see that it was stripping the "ALTER" out of "CREATE OR ALTER" in the file. This is obviously a problem because the file will fail in deployment if it is an update.

DanC

Hi @rpaul

When it comes to SQL Source Control and the Scripts folders, the scripts folder is just a representation of a schema, and is not intended to be runnable scripts.

SQL Source Control and SQL Compare (the engine behind the tool) create targeted scripts dynamically based on the state of the database you're deploying to.

rpaul

Hi Dan, sorry, I clarified my question. The file is a stored procedure and is in that folder.

DanC

Hi @rpaul

All objects in the scripts folder albeit Views, Stored Procedures, and Tables will all be scripted as CREATE statements, if you're using SQL Compare and comparing the SQL Source Control project to another database, it will dynamically use ALTER statement or CREATE based on the state (whether the object is pre-existing)

rpaul

Thanks @DanC. Is it possible I'm not using the tool correctly? My devs make changes to objects in their local db and then push it to GitHub using SQL Source Control. In that way, the tool is not dynamically picking the right statement because the objects do already exist but it is still removing "ALTER OR". Should we be doing something differently or is this a bug?

DanC

Hi @rpaul

With regards to using the tool correctly, it's both yes and no, I'll do my best to explain here. 

When you commit to GitHub with SQL Source Control it's just storing the Scripts Folder, as mentioned earlier that script folder is just a representation of a schema.

If you create a stored procedure in SSMS with Create or Alter and then script it out into a new window, it will just have the Create statement, so when SQL Source Control is picking up the object, it's just grabbing its current state. 

If an object already exists in the repository it will just overwrite what's existing, if an object has a change in the repository and you're doing a pull, it won't have an ALTER statement, it will just overwrite and recreate it in the state from the repository as shown below:



If you wish to update the database, you should make use of SQL Compare, using the SQL Source Control project as the source and deploying the changes to your target database, this will dynamically change the statements based on the state of the target. 

rpaul

@DanC, we can't use SQL Compare because our devs don't have write access to the prod server. We also have an automated CI/CD tool (Jenkins) so we just need to get changes into GitHub.

DanC

Hi @rpaul

Could you tell me more about your use case and I can assist further?

rpaul

@DanC, We are an enterprise data repository team needing to clean up our version control process. We use Jenkins to deploy to our production server, tied into GitHub. We previously just had one user committing everything to GitHub but decided to have each dev use local databases and push their own branches for review. Since we had access to Redgate tools, SQL Source Control seemed like the best way to allow everyone to push from SSMS. Most frequently our work consists of updating stored procedures or inserting data. 

DanC

Hi @rpaul

Thanks for that confirmation, what tools are you using in order to deploy the project?

We have SQL Change Automation which will work directly with your SQL Source Control project and script everything into migrations to be applied against your target within Jenkins. 

https://documentation.red-gate.com/sca/deploying-database-changes/add-ons/jenkins

rpaul

@DanC Jenkins handles the deployment. I've started looking at the documentation for Change Automation but right now I'm unclear how the process works for the devs. 

EDIT: To be more specific, we manually deploy once a week.

DanC

Hi @rpaul

From the information you've given me so far, it would be relatively simple to implement SQL Change Automation into your current workflow. 

Essentially you would have your Jenkins pipeline configured to use SCA and reference your SQL Source Control project as the source.