Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    11

    Unanswered: Can I script out SQL Server jobs programmatically?

    Does anyone know how to script out a complete job definition (job creation script) from a T-SQL query? In SQL Server Managment Studio, you can right click a job and script out a complete definition. I just want to call this same functionality from code rather than the UI. I am using SQL Server 2005.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    See Books Online.

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

  3. #3
    Join Date
    Jun 2009
    Posts
    11
    Thanks for the reply, Pat.

    I have seen that method and it looks promising. However, the only way I know of to work with it is through a clr sproc and the import into SQL Server would be messy. I would have to import not only my assembly, but the smo management assembly (which is subject to change with each new version of SS).

    Is there an easier method than a clr in this case?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    sp_oacreate and it's kin.

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

  5. #5
    Join Date
    Jun 2009
    Posts
    11
    I found my solution. I am indeed using the SMO and the Script() method you mentioned, but I am simply calling it from my client code. For some reason I was set on doing this in SQL Server, but that was not necessary. I did it in my client program in less than 5 lines of code.

    Thanks for your help Pat!!

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sometimes it is amazing when you just take a step back... The view can be amazing!

    I'm glad that you're working now!

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

Posting Permissions

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