Solved

SQL script execution tool

Posted on 2013-10-27
7
340 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Are triggers slow? 7 29
Need multiple Group By's 8 55
I am new to using JSON in SQL Server 2 58
EF5: Update Model from Database not working 3 83
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore 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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

751 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