Solved

SQL script execution tool

Posted on 2013-10-27
7
337 Views
Last Modified: 2013-11-04
Every time when I update the production database, I need to update the lot of script files one by one.   It takes more time and it requires more manual work.  Is there any tool execute all the script files one by one ?
0
Comment
Question by:Varshini S
7 Comments
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 39604666
One way to do this is to convert your scripts to stored procedures on a separate database, and create one more procedure that just executes those one by one. That way after you update the production database, you only have to execute this one stored procedure to run all your scripts.
0
 
LVL 4

Assisted Solution

by:rshq
rshq earned 50 total points
ID: 39604835
Hi
  Maybe create a job help.
  In each step use SQLCMD command to execute a script file.
 http://technet.microsoft.com/en-us/library/ms170572%28v=sql.105%29.aspx
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39605664
>I need to update the lot of script files one by one.
If you could tell us why this has to happen, perhaps we can provide another solution.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Varshini S
ID: 39606123
jimhorn -   During application upgrade from one version to another version .

I have to execute data migration script , alter script , insert script , delete script
0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 39607520
If the scripts are different every time you have to perform this task, perhaps the easiest way is to add the all to a single stored procedure. Just copy paste them one after another and create one SP, then execute this SP.
0
 

Author Comment

by:Varshini S
ID: 39607763
I tried this one , sometimes I am getting memory exception if the common SP have more number of insert or update commands
0
 
LVL 13

Accepted Solution

by:
Koen Van Wielink earned 450 total points
ID: 39607801
That might have to do with the way you wrote the procedure. If you instruct it to execute it in chunks I think memory should not be an issue. Try putting the scripts in individual transactions (BEGIN Transaction, Commit Transaction statements). Also use the GO command to break down the final procedure in manageable batches. I'm not sure if this will affect memory though. I'm not a proper DBA so my knowledge of tuning is limited.
Point is that if the scripts would run OK on their own, there should not be a reason why you could not execute them all from a single stored procedure.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question