Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««45678»»»

A Simple way of Automating Scripts Expand / Collapse
Author
Message
Posted Monday, November 29, 2010 9:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 5, 2011 12:56 PM
Points: 13, 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 >>).

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.
Post #1027400
Posted Monday, November 29, 2010 9:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 10, 2013 12:48 PM
Points: 19, 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 >>).

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

Post #1027419
Posted Monday, November 29, 2010 10:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 5, 2011 12:56 PM
Points: 13, 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.
Post #1027428
Posted Monday, November 29, 2010 10:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 10, 2013 12:48 PM
Points: 19, 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
Post #1027450
Posted Monday, November 29, 2010 10:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 5, 2011 12:56 PM
Points: 13, 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.
Post #1027469
Posted Monday, November 29, 2010 11:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 10, 2013 12:48 PM
Points: 19, 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
Post #1027523
Posted Monday, November 29, 2010 12:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 5, 2011 12:56 PM
Points: 13, 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.
Post #1027533
Posted Monday, November 29, 2010 12:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 10, 2013 12:48 PM
Points: 19, Visits: 135
This may work for you, but it is not going to work or be a option for everyone.
Post #1027541
Posted Monday, November 29, 2010 12:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 7:43 PM
Points: 33,046, Visits: 15,152
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
Post #1027567
Posted Tuesday, November 30, 2010 2:35 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, February 16, 2011 10:51 PM
Points: 83, 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
Post #1027813
« Prev Topic | Next Topic »

Add to briefcase «««45678»»»

Permissions Expand / Collapse