Tuesday, August 12, 2008
Linux Programming on SQL Server
13:34 |
Posted by
Sean McCown |
Edit Post
I've currently got a SQL programmer who really should be a Linux guy instead. He consistently sends me DB releases with directions like this:
1. Open the stored procedure folder and run all the .sql scripts.
(Then I go to the folder and there are 3 dozen files in there.)
2. Add a CustTypeCd column to the Orders table and use Server2.DW.dbo.Orders as a guide on how to create the column.
(Then when I get there, there is no Server2.DW.dbo.Orders. Orders is a view with 12 joins and I have to sift through the code to find which column it is and where it's actually coming from.)
3. Create the CustHistDist table by running this the .sql in the Create Table folder.
(When I try to run the script there's no 'USE' statement to tell me which DB it goes in and when I track the guy down to ge the DB, the script fails because the custom data type hasn't been created.)
4. Populate the CustTypeCd column created in step 2.
(But it never says where to populate it from or any hint as to how long it will take or how much data it will populate.)
5. Refresh all views.
(So after looking you'll see that there are dozens if not hundreds of views and I'm supposed to write a cursor during an implementation that writes that SQL for me because I'm not doing them all by hand.)
Now, this isn't something where these types of things occur every now and then. Every release I get from this developer has most of these elements if not all of them. I fact, I really used the implementation doc I got from him this morning as a template for this post. And of course I kick it back every time and tell him that everything needs to be in one script and that all of his 'USE' statements need to be there, etc.
And for those of you who don't know anything about linux (which is me too, but I know this much), in linux when you download software there's rarely an actual installer. You just don't get pretty packaged software on that side of the moon. You have to compile it yourself quite often and even go to different websites and download specific versions of .dlls to make it compile and work correctly. It's not an easy task. We in the windows world are very spoiled. And as much as I complain about the Katmai installs I've been doing lately, I really couldn't imagine having to do something like that on linux. It's just ridiculous.
So in case my point isn't clear here. When you package your code for ANYBODY to deploy. Be nice to them. Don't assume that they have 2hrs to sit there and play with all of your files. Don't assume that they know anything at all about your app. Assume that they just want to hit a button and get on with their lives.
Here are some things to include when you package software:
1. Your contact info should there be a problem.
2. A single script per server assuming that's possible.
3. 'USE' statements in ALL your code. Use this to move between DBs instead of different scripts.
4. Approx. completion times so the implementor will know how to plan.
5. Verified, tested scripts. Don't make them troubleshoot or call you. Test your code several times before you send it to them.
6. If your release spans multiple servers you might even wrap it up in a .bat file for them so they just have one button to push.
So really the rule is just to realize that the guy running your code isn't there to troubleshoot your code and he's not there to tie up your loose ends. What would be be like if whenever MS released a new version of Office you had to compile this portion, or go to their website for a certain .dll because they just didn't feel like putting it up with the rest of the package? Or what if they said, ok, now manually register all 35 of these .dlls and you're good to go.
Whenever I package SQL code for someone I quite often use PrimalScript to compile it into an exe and it does its thing. So all I have to do is send them a final exe and they click on it and it just runs. I test that exe several times before sending it to them and they really have very little to worry about. THAT'S how you package software.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.ITBookworm.com
Read my book reviews at:
www.ITBookworm.com
Blog Author of:
Database Underground – http://weblog.infoworld.com/dbunderground/
1. Open the stored procedure folder and run all the .sql scripts.
(Then I go to the folder and there are 3 dozen files in there.)
2. Add a CustTypeCd column to the Orders table and use Server2.DW.dbo.Orders as a guide on how to create the column.
(Then when I get there, there is no Server2.DW.dbo.Orders. Orders is a view with 12 joins and I have to sift through the code to find which column it is and where it's actually coming from.)
3. Create the CustHistDist table by running this the .sql in the Create Table folder.
(When I try to run the script there's no 'USE' statement to tell me which DB it goes in and when I track the guy down to ge the DB, the script fails because the custom data type hasn't been created.)
4. Populate the CustTypeCd column created in step 2.
(But it never says where to populate it from or any hint as to how long it will take or how much data it will populate.)
5. Refresh all views.
(So after looking you'll see that there are dozens if not hundreds of views and I'm supposed to write a cursor during an implementation that writes that SQL for me because I'm not doing them all by hand.)
Now, this isn't something where these types of things occur every now and then. Every release I get from this developer has most of these elements if not all of them. I fact, I really used the implementation doc I got from him this morning as a template for this post. And of course I kick it back every time and tell him that everything needs to be in one script and that all of his 'USE' statements need to be there, etc.
And for those of you who don't know anything about linux (which is me too, but I know this much), in linux when you download software there's rarely an actual installer. You just don't get pretty packaged software on that side of the moon. You have to compile it yourself quite often and even go to different websites and download specific versions of .dlls to make it compile and work correctly. It's not an easy task. We in the windows world are very spoiled. And as much as I complain about the Katmai installs I've been doing lately, I really couldn't imagine having to do something like that on linux. It's just ridiculous.
So in case my point isn't clear here. When you package your code for ANYBODY to deploy. Be nice to them. Don't assume that they have 2hrs to sit there and play with all of your files. Don't assume that they know anything at all about your app. Assume that they just want to hit a button and get on with their lives.
Here are some things to include when you package software:
1. Your contact info should there be a problem.
2. A single script per server assuming that's possible.
3. 'USE' statements in ALL your code. Use this to move between DBs instead of different scripts.
4. Approx. completion times so the implementor will know how to plan.
5. Verified, tested scripts. Don't make them troubleshoot or call you. Test your code several times before you send it to them.
6. If your release spans multiple servers you might even wrap it up in a .bat file for them so they just have one button to push.
So really the rule is just to realize that the guy running your code isn't there to troubleshoot your code and he's not there to tie up your loose ends. What would be be like if whenever MS released a new version of Office you had to compile this portion, or go to their website for a certain .dll because they just didn't feel like putting it up with the rest of the package? Or what if they said, ok, now manually register all 35 of these .dlls and you're good to go.
Whenever I package SQL code for someone I quite often use PrimalScript to compile it into an exe and it does its thing. So all I have to do is send them a final exe and they click on it and it just runs. I test that exe several times before sending it to them and they really have very little to worry about. THAT'S how you package software.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.ITBookworm.com
Read my book reviews at:
www.ITBookworm.com
Blog Author of:
Database Underground – http://weblog.infoworld.com/dbunderground/
Subscribe to:
Post Comments (Atom)
About Me
- Sean McCown
- I am a Contributing Editor for InfoWorld Magazine, and a frequent contributor to SQLServerCentral.com as well as SSWUG.org. I live with my wife and 3 kids, and have practiced and taught Kenpo for 22yrs now.
Labels
Blogumulus by Roy Tanck and Amanda Fazani
1 comments:
I feel your pain...
We have the same issues with a couple of our devs. We throw it back too...but its like a boomerang...just comes flying back to hit us again.
Thankfully, we have a good Change Manager who supports us with these issues and highlights common problems, where we've given them multiple examples of how we would like the scripts presented, in meetings with their managers. Its seems to be working...slowly.
Post a Comment