I'm hoping that someone may know something about this: We're writing a script (t-sql in SQL Server 2008) to all in one: 1) Drop a stored procedure, then 2)create the just dropped stored procedure, and finally 3) grant Execute permissions to the newly written procedure.
The problem is, the code we wrote when executed doesn't work. However we've pulled (from SQL Compare) code that does the exact same thing, which works (for the creation and graning permissions). We've compared the code of both scripts, the SQL Compare code works, but ours (written by hand) doesn't and they are practically identical.
Does anyone have any ideas how to help us get our hand-written code to work?
The most likely reason is the lack of the go terminator between the drop and create. If this is the case then you are probably greeted with lovely
error. Please check that your are not missing any batch terminators. One of the most evil things to do in T-SQL is to forget terminating the procedure definition, because without it, the procedure text will include the rest of the script you are trying to execute, which can lead to some really unwanted results. Once I accidentally ran into the existing procedure definition which included something as nasty as checking existence and creation of some index, simply because the procedure definition did not end with the word GO, several scripts were compiled into a single script and the whole batch then got deployed. You can use the template like this for your create proc script: