Post reply

Converting SQL scripts to jobs

  • 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

  • December 15, 2005 at 1:01 pm

    #609865

    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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • December 15, 2005 at 1:06 pm

    #609866

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

    Thanks...Nali

  • December 15, 2005 at 1:15 pm

    #609867

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

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • December 15, 2005 at 1:18 pm

    #609868

    Yes, I have admin access to all the servers.

  • December 15, 2005 at 1:24 pm

    #609870

    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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • December 16, 2005 at 12:52 am

    #609971

    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

  • December 16, 2005 at 6:27 am

    #610029

    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

  • December 16, 2005 at 9:05 am

    #610091

    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.

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

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