Home Forums SQL Server 2008 T-SQL (SS2K8) I'm trying to create a proc that will drop and create a function RE: I'm trying to create a proc that will drop and create a function

  • 1) Sql cmd exe is disabled on all prod sql instances due to security issues.

    2) I can’t add them all to 1 script because it’s dropping and creating objects as well as loading data. Because the scripts are laden w/ “go” throughout if the portion before a go fails the second portion will run regardless. So, for example. If after say the second batch is an insert that fails and say 400 batches later is another insert into a table that uses the initial table whose insert failed. Then it would insert 0 records and so on. I can’t have that.

    So the separation of the scripts is kind of a failsafe to ensure the process doesn’t get too far if something fails. Again, I didn’t write this. Just stating the facts. So combine all into one is not an option at this point. If file three fails I need to run it till it succeeds before I run file four. Each script is written in a way that it’s self-healing in a sense. If a script fails half way I can rerun the whole script and everything is dropped and recreated. Which is actually part of the problem. It take around 18 hours to run all the files and I have a short window of time to get it done once we do the final push.

    The rub is u can’t drop and create certain object w/o a GO in between. As my original example states. U can’t drop a function then create a function w/o running them in separate batches. Unless I’m missing something. Is there some other method other than using GO? To be honest, it shouldn’t blindly drop and recreate. It should be checking for existence and creating if it doesn’t exist instead of blindly dropping and recreating.

    It should have drop and create objects script then a data load script.

    Keep in mind the current process works It’s a bit manual but it works. So it’s not imperative I find a solution. It’s just been on my mind lately. So worst case I kick one off. Then 1.32 hours later when I expect it’s done I manually check. If all is good I kick off the next script. I was just hoping to automate it and have it send me success or failure emails.

    Actually at the very least I could have it send me a completion email at the end of each script. I think I’ll add that now. Just thinking out loud. Again. I wouldn’t waste too much mental energy on this because I do have a working method.