invoke-sqlcmd adds a semicolon at the end of stored procedures

This topic contains 2 replies, has 2 voices, and was last updated by  Windows LiveUser61 3 years, 8 months ago.

  • Author
    Posts
  • #14559

    Windows LiveUser61
    Participant

    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

  • #14573

    Don Jones
    Keymaster

    It sounds as if this is something Invoke-SqlCmd is doing whether you like it or not. I'm not sure you can change that without reprogramming the cmdlet. You might consider logging that as a bug or suggestion with the SQL Server team at connect.microsoft.com.

  • #14574

    Windows LiveUser61
    Participant

    Thank you...I may do that. I have a ticket opened with Microsoft right now and they have not gotten back to me yet so I will see what they have to say first.

You must be logged in to reply to this topic.