Linked SQL Scripts

Question

Post reply

Linked SQL Scripts

wayne.mcdaniel

SSCommitted

Points: 1546
More actions
October 6, 2009 at 4:36 pm

#142396

I thought I had seen a way to do this before, but I can't seem to find it now. What I have are a series of SQL scripts that I need to run in order, and I may or may not want to stop after some of them.
Is there a simple way to write a control script that runs the other scripts? Maybe something like...
Run "C:\script\A.SQL"
Run "C:\script\B.SQL"
Run "C:\script\C.SQL"
I have about 45 sequential scripts, and trying to keep them all open in management studio at once is confusing.
I suppose I could use xp_cmdshell, but are there any options other than that?
Wayne

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

Have you thought about SQL Server Agent jobs, or just converting them to stored procedures?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

OTF SSCertifiable Points: 6104 More actions · Answer 2

You can set SSMS to SqlCmd Mode and use code similar to the following:

-- Your Scripts Folder

:setvar scripts "d:\scripts\"

-- Execute your Script Files

use DatabaseA

:r $(scripts)ScriptFileToExecute.sql

use DatabaseB

:r $(scripts)ScriptFileToExecute.sql

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

Or, instead of running in SSMS in sqlcmd mode (great idea, btw), just go and run the scripts in sqlcmd directly.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

wayne.mcdaniel SSCommitted Points: 1546 More actions · Answer 4

Thanks for the suggestions. Due to the interactive nature of the scripts I am trying to run, and the fact that it's one of the critical but infrequent projects, I'm just going to stick with loading the scripts one at a time, because I want to see the results (and hopefully the error free status) of each one.