Solved

SQL script execution tool

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

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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 12

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 12

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now