Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: how to create and schedule a job

    Hi,

    I wrote a number of SQL scripts to do a data migration, from MSSQL 2000 to 2008 R2. These scripts should be run each night. All error message should be captured, though execution may continue, so we have an overview of all errors in the morning. We know when one failing script will cause others scripts to fail, so that's not a real problem.
    Multiple related SQL scripts are grouped in a file for ease of development. There a are multiple files, and their number is still growing.

    How can I automate this? Can I leave multiple SQL scripts in one file, or do I have to limit the number of scripts per file to one?

    I am thinking of using a scheduled job, but I have never created one and I don't know how to handle the error messages.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Assuming you plan to run this as a SQL Agent Job (which is a very good plan for this case), the process is pretty simple.

    There are a number of strategies for how to handle multiple scripts. One school of thought is to make them all one script, which facilitates finding any given piece of code and appreciating the overall scope of operations. Another school of thought that I prefer is to leave the files as they are and possibly subdivide them even further to make each script do one logical operation, which leads to a more manageable job structure and usually far easier to interpret error messages from the jobs.

    The job structure is pretty simple. You create a job, then create one or more job steps that actually do the work (in your case execute SQL scripts). Each job step can log a summary or detail (there is a log file setting on the advanced tab). Each job step can signal success or failure, and you as the creator can choose between a couple of ways to handle success or failure of a step.

    Review the idea of SQL Agent Operators. These include emails and net messages among other options, and they can be really useful for letting humans know about the outcome of jobs (success, failure, or both).

    This is a pretty high level overview. I expect questions, but don't know what you'll want to ask yet!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I'll search for "SQL Agent Job" and "SQL Agent Operator" and give it a try.

    You know some good URL's that could help me getting started?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    BOL to the rescue once again! Starting with the story "straight from the horse's mouth" at least indoctrinates you with the official party line, and in this case that might well be enough.

    SQL Server Agent covers the basics of SQL Agent Jobs for SQL 2012, but any differences between it and 2008 R2 are probably trivial. I'd recommend using the GUI to build at least your experimental jobs, but I'd also suggest that you build at least one experimental using either Transact-SQL or .NET just so that you see what pieces go into the process... That gives you a much deeper understanding of how things work and what is possible, and it isn't all that difficult especially if you use one of the example jobs provided in the BOL article!

    The destructions on creating a SQL Agent Operator are at Create an Operator (which is also SQL 2012, but I don't know of any significant differences here either).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Mar 2012
    Location
    Somewhere In Europe
    Posts
    24
    Hi Wim
    Is there any limitations to implement it as a SSIS package?
    You can easily process it in loop and increase number of scripts.

    Regards
    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •