We have an automated process that promotes code to SQL Server using powershell scripts. The powershell script executes .sql script files from a file folder. When invoke-sqlcmd executes a .sql script that alters or creates a stored procedure, it adds a semi colon at the end of the stored procedure. Even if the stored procedure ends in a semi-colon already, it adds another one. The problem is that the developers use Red Gate SQL Compare to compare their objects in dev to prod, they don’t match due to this added semi-colon. Is there a way to prevent invoke-sqlcmd from adding a semicolon to the end of the stored procedure?
I know that some may answer to use sqlcm instead of invoke-sqlcmd. I actually previously explored that for other reasons and found that I could not get the error back if the script failed like I can with invoke-sqlcmd. Getting the error back from the script is very important.
I appreciate any help you can give me!
Thanks,
Laura