question

Torik Noor avatar image
Torik Noor asked

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

stored-proceduresscriptjobmaintenance-plans
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
Matt Whitfield avatar image
Matt Whitfield answered

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...

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image TimothyAWiseman commented ·
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.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered

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.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered

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.

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Torik Noor avatar image Torik Noor commented ·
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
0 Likes 0 ·
Tom Staab avatar image Tom Staab ♦ commented ·
That is correct.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.