x

Consolidate multiple scripts into one job?

Hi,

Apologies if this seems very trivial, would you happen to know if I can consolidate a bunch of scripts (17 to be exact) into just one job? Would it be a simple matter of copying them all into just one script; adding 'end' at the end of each script etc? Or is there another way? can I attach them all to one maintenance plan or job for instance? If so, can you tell me the exact steps I need to take in order to do this? Currently I'm executing 1 script at a time, waiting for the result, and then executing another script. The scripts are designed to upgrade the database they are referring to, which it does manage to do successfully.

Any help in this matter is greatly appreciated.

Many thanks,

Torik

more ▼

asked Dec 07 '09 at 01:11 PM in Default

Torik Noor gravatar image

Torik Noor
31 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort oldest

If you want to run them through Enterprise Manager, Query Analyser or some other IDE then you can just make one big file with 'GO' on a line on it's own between each file's text.

If you want to run it from C# or something, then just read the files in sequentially and fire each one in a batch at the server.

You will want some error handling in there, though...

more ▼

answered Dec 07 '09 at 01:17 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

Matt has a great point, but I want to second the recommendation for error handling.

Also, pasting everything into one big file with go statements will generally execute each command in the T-sql script sequentially. This may be want you want, but if you want to gauruntee parallelism then you can either keep them split in multiple jobs or use another tool that will let you send the next command without waiting for the first to finish.
Dec 07 '09 at 01:40 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

It really depends on what each of these scripts is doing. As a general concept can you copy & paste them all together? Sure, but, at the very least, you'll probably want to wrap each one with a BEGIN and END statement. You may want to add error handling if you don't have it already.

From your description these are a serious of data definition language scripts, changing tables, adding procedures, that sort of thing? You should be able to join them all together.

more ▼

answered Dec 07 '09 at 01:17 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
91k 19 21 74

(comments are locked)
10|1200 characters needed characters left

If you want to keep the scripts separate, you can run them from sqlcmd or using SSMS in SQLCMD mode like this:

:r script1.sql
GO
:r script2.sql
GO

As Matt said, I recommend adding error handling as well.

more ▼

answered Dec 07 '09 at 01:22 PM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

Thanks a lot Matt, Tim, Grant and Tom - thats all extremely helpful. I'll let you know the results tomorrow. Can I just verify that I do not need to add 'end' at the end of each script, I can just simply add 'Go' after each one, as Tom and Matt have suggested?

Many thanks
Dec 07 '09 at 02:11 PM Torik Noor
That is correct.
Dec 07 '09 at 03:29 PM Tom Staab
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x402
x85
x66
x32

asked: Dec 07 '09 at 01:11 PM

Seen: 2304 times

Last Updated: Dec 07 '09 at 05:14 PM