SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Simple way of Automating Scripts


A Simple way of Automating Scripts

Author
Message
Doug Bishop
Doug Bishop
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 67
Why, oh why, oh why, oh why do people still insist on using scripts? They do it here too and it is frustrating.

Anyone ever hear about a new object in SQL Server called stored procedures?

Not only that, but SQL Server also came out with something called the SQL Server AGent a while back (maybe not everyone is read up on it yet), and Jobs. The jobs have steps and you can control the order in which you execute stored procedures. If one fails, you can take specific action, including sending out email, paging someone, etc. It is really cool stuff. Besides, you do not need to learn hieroglyphics (@for /r %%s in (.) do (echo ..............%%s /b >>Wink.

Not only is it more efficient, using stored procedures allows SQL Server to compile the code and results in overall faster execution. And, if you need to insert something between *_A and *_B, just insert a job step, and all references in both upstream and downstream steps are automatically adjusted.
Martin Bell-290511
Martin Bell-290511
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 135
Doug Bishop (11/29/2010)
Why, oh why, oh why, oh why do people still insist on using scripts? They do it here too and it is frustrating.

Anyone ever hear about a new object in SQL Server called stored procedures?

Not only that, but SQL Server also came out with something called the SQL Server AGent a while back (maybe not everyone is read up on it yet), and Jobs. The jobs have steps and you can control the order in which you execute stored procedures. If one fails, you can take specific action, including sending out email, paging someone, etc. It is really cool stuff. Besides, you do not need to learn hieroglyphics (@for /r %%s in (.) do (echo ..............%%s /b >>Wink.

Not only is it more efficient, using stored procedures allows SQL Server to compile the code and results in overall faster execution. And, if you need to insert something between *_A and *_B, just insert a job step, and all references in both upstream and downstream steps are automatically adjusted.


Some people use a thing called version control for those stored procedure scripts, and although there are some flashy features in Visual Studio or third party tools to help maintain code and deploy them; some companies are either not in the position to do that; or just don't want to use them, especially when it can be done on the command line for no extra licence cost.

Martin
Doug Bishop
Doug Bishop
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 67
Martin Bell-290511 (11/29/2010)
Some people use a thing called version control for those stored procedure scripts, and although there are some flashy features in Visual Studio or third party tools to help maintain code and deploy them; some companies are either not in the position to do that; or just don't want to use them, especially when it can be done on the command line for no extra licence cost.

Martin



Okay, I am confused; license cost for what? A stored procedure is a script that has been saved to the database using CREATE PROCEDURE procname. There are no licensing costs, the procedure gets backed up everytime the database is backed up. If you have version control software, it can be used with SQL Server. If you don't want to integrate it into SQL Server, then script the procedure and save that manually under version control.

The only reason they have in our shop for using scripts is, "that is the way it used to be done so that is how they are still doing it." Whenever I have to go in and maintain code that is in a script that is run through OSQL, I change it to a stored procedure and delete the old script code form the server.
Martin Bell-290511
Martin Bell-290511
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 135
Doug Bishop (11/29/2010)Okay, I am confused; license cost for what? A stored procedure is a script that has been saved to the database using CREATE PROCEDURE procname. There are no licensing costs, the procedure gets backed up everytime the database is backed up. If you have version control software, it can be used with SQL Server. If you don't want to integrate it into SQL Server, then script the procedure and save that manually under version control.

The only reason they have in our shop for using scripts is, "that is the way it used to be done so that is how they are still doing it." Whenever I have to go in and maintain code that is in a script that is run through OSQL, I change it to a stored procedure and delete the old script code form the server.


If you are using version control then you would use the script held in version control as the definitive copy. Relying on backups to keep a history of version of a procedure is not workable when there are more than one developer working on a project or if you need to keep an accurate history of changes. Making changes on the fly is also not acceptable on systems that require the code changes to be accountable, and when you start to implement releases rather than ad-hoc changes you will usually need to run one or more script (procedure or otherwise) in a given order and in a reliable way.. hence the reason for this thread.

Martin
Doug Bishop
Doug Bishop
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 67
Martin Bell-290511 (11/29/2010)
If you are using version control then you would use the script held in version control as the definitive copy. Relying on backups to keep a history of version of a procedure is not workable when there are more than one developer working on a project or if you need to keep an accurate history of changes. Making changes on the fly is also not acceptable on systems that require the code changes to be accountable, and when you start to implement releases rather than ad-hoc changes you will usually need to run one or more script (procedure or otherwise) in a given order and in a reliable way.. hence the reason for this thread.

Martin

I am still confused as to how using scripts is better than using stored procedures and using a job with multiple steps to control the flow. Furthermore, in Sadequl's article, he specifically mentions that you cannot check for errors that occur between scripts, something that can be done with a job and you are controlling the flow of execution of the steps. You could add that, but then your code to execute code starts becoming complex, using pipes and FIND and branching (mind you extremely limited branching). Using a job, each step can branch to a completely different step if the executing step fails, thus customizing error handling.

I am also somewhat opposed to saying, "execute anything in the folder and always assume the last character of the filename (prior to the period) is the sort order". The code presented would attempt to execute a temp file, a text file, a backup copy of a current script, etc. At the very least, if I was going to implement this methodology, I would have a control table in SQL Server that specified the script name and order of execution. I could then pull the script name from that table in execution order. Then I could give my scripts meaningful names without worrying about them having the "correct" prefix or suffix.

I did not state, or even imply that backups are a method of version control. I stated that you can either integrate your version control software into SQL Server, or script the procedure and manually add updates to your version control (check out existing, check in changes). I was implying that backing up the database backs up the procedures.
Martin Bell-290511
Martin Bell-290511
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 135
Doug Bishop (11/29/2010)
I am still confused as to how using scripts is better than using stored procedures and using a job with multiple steps to control the flow. Furthermore, in Sadequl's article, he specifically mentions that you cannot check for errors that occur between scripts, something that can be done with a job and you are controlling the flow of execution of the steps. You could add that, but then your code to execute code starts becoming complex, using pipes and FIND and branching (mind you extremely limited branching). Using a job, each step can branch to a completely different step if the executing step fails, thus customizing error handling.


For software releases, especially if they are to multiple servers or third party systems you need a rubust and reliable method to deploy your scripts. A job is just not going to do this, and there is always the question of creating the job in the first place. There are means to check a script has worked so I am not sure where in Sadequl's article it says otherwise or if it's been taken in the correct context.

Doug Bishop
I am also somewhat opposed to saying, "execute anything in the folder and always assume the last character of the filename (prior to the period) is the sort order". The code presented would attempt to execute a temp file, a text file, a backup copy of a current script, etc. At the very least, if I was going to implement this methodology, I would have a control table in SQL Server that specified the script name and order of execution. I could then pull the script name from that table in execution order. Then I could give my scripts meaningful names without worrying about them having the "correct" prefix or suffix.


If you control what is in the given folder and the names of the files, then it is not a problem. How do you get the data into the control tables that you mention?

Doug Bishop
I did not state, or even imply that backups are a method of version control. I stated that you can either integrate your version control software into SQL Server, or script the procedure and manually add updates to your version control (check out existing, check in changes). I was implying that backing up the database backs up the procedures.


You can integrate version control with SSMS, that is a tool not SQL Server itself and Visual Studio or some third party tools can also interface with version control systems. Version control is only the starting point for a release process and a small part of implementing change control. Third party tools can package up releases for you, but not everyone wants to use them or has the budgets to buy them.

Martin
Doug Bishop
Doug Bishop
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 67
Martin Bell-290511 (11/29/2010)
Doug Bishop (11/29/2010)
I am still confused as to how using scripts is better than using stored procedures and using a job with multiple steps to control the flow. Furthermore, in Sadequl's article, he specifically mentions that you cannot check for errors that occur between scripts, something that can be done with a job and you are controlling the flow of execution of the steps. You could add that, but then your code to execute code starts becoming complex, using pipes and FIND and branching (mind you extremely limited branching). Using a job, each step can branch to a completely different step if the executing step fails, thus customizing error handling.


For software releases, especially if they are to multiple servers or third party systems you need a rubust and reliable method to deploy your scripts. A job is just not going to do this, and there is always the question of creating the job in the first place. There are means to check a script has worked so I am not sure where in Sadequl's article it says otherwise or if it's been taken in the correct context.

Doug Bishop
I am also somewhat opposed to saying, "execute anything in the folder and always assume the last character of the filename (prior to the period) is the sort order". The code presented would attempt to execute a temp file, a text file, a backup copy of a current script, etc. At the very least, if I was going to implement this methodology, I would have a control table in SQL Server that specified the script name and order of execution. I could then pull the script name from that table in execution order. Then I could give my scripts meaningful names without worrying about them having the "correct" prefix or suffix.


If you control what is in the given folder and the names of the files, then it is not a problem. How do you get the data into the control tables that you mention?

Doug Bishop
I did not state, or even imply that backups are a method of version control. I stated that you can either integrate your version control software into SQL Server, or script the procedure and manually add updates to your version control (check out existing, check in changes). I was implying that backing up the database backs up the procedures.


You can integrate version control with SSMS, that is a tool not SQL Server itself and Visual Studio or some third party tools can also interface with version control systems. Version control is only the starting point for a release process and a small part of implementing change control. Third party tools can package up releases for you, but not everyone wants to use them or has the budgets to buy them.

Martin

Dilemna: you have some form of version control software that you want to integrate for change management, but it is not integrated into SQL Server.
Solution: script the stored procedure and save the script under version control. Check that in and out of your version control. A scripted stored procedure is a script.

Chances are, if you are maintaining the scripts, you are checking them out, modifying and executing them for testing in SSMS and saving the completed script for checkin. If it is a procedure, right-click on it in the SSMS object explorer, select Script Stored Procedure As > Create to > New Query Editor Window (or File).

As far as packaging up releases, the job does that for you and control of execution is maintained in the order of the job steps.
Martin Bell-290511
Martin Bell-290511
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 135
This may work for you, but it is not going to work or be a option for everyone.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63428 Visits: 19115
I still think my old method of using scripts is a good one. It handles dependencies, and it integrates VCS with a release process.

Building a stored procedure for a release can work, but I'm not sure it's that efficient, especially as the release grows.


http://www.sqlservercentral.com/articles/System+Development+Life+Cycle/vcspart3/525/

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
vijay.singh 97987
vijay.singh 97987
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 53
Hi Sadequl Hussain,

Thanks.Very use full script. I have 1 question. how can we capture the output of the result file to and variable?

Regards,

Vijay Singh.C
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search