Solved

SQL script execution tool

Posted on 2013-10-27
7
338 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

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 we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

735 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