I've talked before about my love for PrimalScript as a VBScript and JavaScript editor. It's just got some features that I've grown to really love. Well unfortunately I just don't get the chance to use it that much anymore because most of the web coding I do is in .NET and I tend to do all that coding in Visual Studio for all the class helpers and such. And since I'm a DBA, and not a Windows admin, I just don't d I’ve talked before about my love for PrimalScript as a VBScript and JavaScript editor. It’s just got some features that I’ve grown to really love. Well unfortunately I just don’t get the chance to use it that much anymore because most of the web coding I do is in .NET and I tend to do all that coding in Visual Studio for all the class helpers and such. And since I’m a DBA, and not a Windows admin, I just don’t do that much Windows scripting… though it does happen every couple months or so.Well, now I’m going to get to use PrimalScript more because I’ve just figured out a new use for it and I just can’t believe it took me this long to think of it. See, PrimalScript has this script packager that will package up all of your scripts into an exe so you can put them out there for users without having to worry about them getting at your connection strings or changing the logic. And that’s how I’ve always used it in the past. But it just hit me a couple weeks ago that I can use it to deploy my DB scripts too. See, like many shops, we have to send our deployment scripts to an official change manager who then deploys them into production. It’s that whole separation of duties thing we all love so much. Well anyway… the problem is simple… if we send him our scripts, which number in the tens sometimes, we have to make sure we tell him what order to run them in, and then trust that he does it correctly. And then if something blows up there’s always that argument about whether the scripts were right or whether he changed something by mistake. Then there’s the problem of whether he should deploy to all of our systems because we don’t want him to have SQL rights on all of our boxes, etc. So you can see how the process can get fairly complicated and even counter-productive at times.Enter PrimalScript… now I’m packaging up all my scripts and just sending him an exe that all he has to do is run. In fact, I can put them in order, and even call them with whatever account I like and he can actually deploy code to boxes he doesn’t even have rights on. I’m telling you, that’s just fabulous. It’s actually pretty easy. All I have to do is specify a .vbs file with a series of osql statements in it. Each osql statement uses an input file as it code source. So whatever script you generate, just point it at the file and you’re done. Then inside PrimalScript, you attach the vbs as the script to run, and attach the SQL scripts as data files. They all get compiled up with the exe and you can send the entire set as a single build to your deployment team. Now the arguments just don’t exist anymore. You know the scripts ran in order, you know nothing was changed, and you know they ran under an account that had rights to perform all the actions. Personally, I’m in love.I don’t think this was necessarily the intended use for the script packager, but it certainly works really well. Now that I’ve written this blog, maybe the Sapien guys will see this and give us DBAs an enhancement that will be just for us. What I’d like to see is something specially for DB deployments and allow me to just attach SQL scripts with user creds and bypass the vbs altogether. That would be nice. This new version of the script packager would also clean up after itself too. It would allow me to create a folder to unzip all the files in while they’re executing, and then either let me keep it or delete it. If I were pressed on what I’d want in a perfect deployment utility, I’d also ask for a nice progress screen and a basic report to send back to the dev team to let them know what errors were encountered, or that the whole thing went well. This would close the loop and allow the build to be closed in source control. It would also let me choose which scripts continued on error and which ones halted. I know I’m starting to ask for a lot, but these guys seem to be in the position to do this for us. But at the very least, I’d like to be able to just compile my SQL scripts and run them without a vbs. The way I’m doing it is easy enough and I’ve already got a template, but all the same… there’s no reason it can’t be any easier. So if you haven’t already, go get you a copy of PrimalScript and see what it can do for your deployments. I’m lovin’ it. Here’s a screenshot of the PS script packager. Notice how you can add as many script files and supporting files as you like: View image And NO, the Sapien guys aren’t paying me for this. In fact, I don’t even think they follow my blog. But it’s hard to tell because anyone who’s anyone follows my blog. But I’ll tell you this… I just got a press release that they’re releasing 4 new bundles that should suit everyone’s needs. Basically what they’re doing is bundling up PrimalScript with their training and script libraries. I think I’ve seen all(ok, most) of thier training vids and I got a lot out of them. In fact, I still refer to them from time to time simply because I don’t do a lot of Windows scripting so it helps to have them as a reference on the odd occasion when I do.For those interested, here are the links to the bundles:https://www.primalscript.com/Editions/prokit.asp >https://www.primalscript.com/Editions/enterprisekit.asp>https://www.primalscript.com/Editions/universal.asp>https://www.primalscript.com/Editions/bundles.asp OK, while PrimalScript is an excellent utility for this type of thing surely it’s not the only game in town, right? Well, you’re right. There are other apps that do this same thing. What I was a little surprised to find though was that there aren’t lots of them. And most of them are really not that good to be perfectly honest.Here, I’ll go over some of them: Admin Script Editor— This is a direct competitor to PrimalScript and it’s an excellent editor with some very nice features overall. It definitely gives PS a run for its money on features. But while that’s true overall, I don’t feel that its script packager is as good and here’s why. PS let’s you run several scripts in a row and attach supporting files. ASE also lets you attach supporting files, but as far as I can tell it doesn’t let you run more than one script. Now, you can force the issue if you like by making a master script that calls all the other scripts and then you just attach them as files, but that’s an extra step that shouldn’t be necessary. It does have a couple cool features though. First, it let’s you use console mode which is a way to use a single window in case you have prompts to answer. And second it saves the scripts in specially named folders for execution so you can have separate executions going at the same time w/o inteferring with each other. That’s not really relevant to our deployment scenario here, but it’s cool nontheless. Here’s a screenshot of the ASE script packager. From what I can tell, you can only add one script to run. If you can add more, it’s not obvious: View image You know though, most of the other utilities aren’t really worth going into in detail because they’re so limited in functionality it’s just not worth my time. However, I’ll list a couple of them for you so you can read up on them if you like. ExeScript Vbs2Exe ScriptCryptor Batch File Compiler Pro 3.0.2 Quick Batch File Compiler 1.0.1.8 OK, and finally to finish up… some of you may not be very familiar with VBScript and may not know how to code the solution even if you got one of these tools. So here’s a simple script that you can save as a .vbs and use to deploy your .sql scripts to as many servers as you like. Dim WSHShell Set WSHShell = WScript.CreateObject(“WScript.Shell”) ”CERT WSHShell.Exec(“osql.exe -Sservername -E -ic:DWBuildDBA1255961.sql -oc:DWBuildErrorsDBA1255961.txt”)Now, all you have to do is save that as a .vbs and run it. Of course, I hope I don’t have to tell you to use your own paths and scripts, right?You can have any SQL in that file that you want. I routinely have several hundred lines of code in mine and they run very well. And if you have more than one .sql file to run, simply tach on another .Exec line and change the file name that the osql is pointing to. It’s really that simple. I put the -o there to capture the output because the way the .exe compiles it just runs what you give it, it typically won’t spit out errors and you’ll have to track them down yourself. Of course, I’m sure all of you have smooth deployments because you’ve tested your code thoroughly, right? Anyway, I hope this helps someone. It’s an important topic and I think it deserves some attention in your organization.Oh, one more thing… you can do all of this in .Net and get a lot richer deployment experience, but this solution is a lot easier to get going, and doesn’t rely on the framework. And since you might not know much about the guy doing your deployments, it never hurts to keep things as simple as possible. Just a thought. Watch my free SQL Server Tutorials at: https://MidnightDBA.ITBookworm.com Read my book reviews at: www.ITBookworm.com Blog Author of: DBA Rant – https://dbarant.blogspot.com Databases