Solved

SQL script execution tool

Posted on 2013-10-27
7
335 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
email about the whoisactive result 7 36
SQL Server Count where two id types exist in column 8 27
SQL Insert parts by customer 12 34
MS SQL BCP Extra Lines Between Records 2 19
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

809 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