Converting SQL scripts to jobs

Question

Converting SQL scripts to jobs

Nali

SSCommitted

Points: 1525
More actions
December 15, 2005 at 12:45 pm

#169866

Hi there,
There's a limit to the number of characters in a single job step.
Is there a way to get around this other than breaking up the script into multiple steps?
I have a large number of SQL servers to which I want to deploy the job to from a Master server, using the MSX/TSX system.
Thanks...Nali

Viewing 9 posts - 1 through 8 (of 8 total)

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

Phil Parkin SSC Guru Points: 246944 More actions · Answer 1

Maybe you could put your script into a stored procedure and have your job run that?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Nali SSCommitted Points: 1525 More actions · Answer 2

How would I then deploy the stored procedure to the 260+ plus servers?

Thanks...Nali

Phil Parkin SSC Guru Points: 246944 More actions · Answer 3

Do you have network and admin access to all of the servers?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Nali SSCommitted Points: 1525 More actions · Answer 4

Nali

SSCommitted

Points: 1525

December 15, 2005 at 1:18 pm

#609868

Yes, I have admin access to all the servers.

Phil Parkin SSC Guru Points: 246944 More actions · Answer 5

There will be other guys on this forum who have better experience with this than I (I've never had anything like that many servers to look after), but one way of doing this is writing a script that will iterate around all of your servers and execute the same piece of code on each.

Obviously, in this case, the code would essentially be CREATE PROCEDURE ...

There may well be third-party tools out there that help out with this sort of multi-server admin, meaning that you don't have to do this, but I don't know of them.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 6

Create a stored procedure for your step, use EM to create a T-SQL script to create the stored procedure. This is to overcome the number of characters limitation in a Job step.

Create a Job to EXEC the stored procedure, use EM to create a T-SQL script to create the Job.

Edit the stored procedure script to append the job script and save it for your deployment script.

Use this deployment script on a 2nd server to find any server specific issues in the script, correct or find ways for the script to detect the differences, like using @@SERVERNAME instead of 'myserver', DB_NAME() instead of 'mydatabase', etc.

Create a batch file using OSQL to execute the T-SQL deployment script.

Copy the tested T-SQL script and tested batch file to a location that can be accessed by all of your servers.

Execute the batch file on each of your servers.

Andy

Nali SSCommitted Points: 1525 More actions · Answer 7

Thank you Andy. This is what I did based your advise:

1. Created a stored procedure in SQL Analyzer and tested it, then copied that script into a .sql script file.

2. Created a .bat file to execute OSQL to execute the .sql file onto a remote server. Seemed to have worked over the lan.

3. Will now test over the wan and push out to all the servers.

Too bad the jobs in SQL server have this limitation.

Thanks again...Nali

Omri Bahat-228920 Mr or Mrs. 500 Points: 540 More actions · Answer 8

You can use tools like SQL Farm Combine from http://www.sqlfarms.com, to deploy the code on all your databases and servers in parallel by a click of a button, without the need for MSX and such.

Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.