On 6/22/09 3:36 PM, in article 97feae9955a3d@uwe, "josephs" <u52758@uwe>
wrote:

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

"josephs" wrote:

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

"josephs" wrote:

> I have delved into the smo and the script method a little using a clr, and
> the implementation looks a bit messy.  I believe I would have to import my
> assembly and the necessary smo assemblies (which can change with each new
> version of Sql Server) as "unsafe".  This is certainly doable, but less than
> ideal.  I was hoping for something slightly less messy.
>
>

>Linchi, thank you for pulling me out of my daze!!!  I'm not sure why I was so
>set on doing this in SQL Server.  After reading your tidbit of wisdom I used
>the SMO in my client program and voila; it works beautifully and simply (less
>than 5 lines of code).
>
>Thank you everyone who contributed to this post.  I now have exactly what I
>was looking for.

"soccerhawg" wrote:

> How about showing us your work.  I've been looking for this too and I know
> nothing about SMO.  I'd appreciate seeing what you did.  I could probably use
> it to meet my needs as well.
>
> Thanks!
>
> josephs wrote:
> >Linchi, thank you for pulling me out of my daze!!!  I'm not sure why I was so
> >set on doing this in SQL Server.  After reading your tidbit of wisdom I used
> >the SMO in my client program and voila; it works beautifully and simply (less
> >than 5 lines of code).
> >
> >Thank you everyone who contributed to this post.  I now have exactly what I
> >was looking for.
>
>

>Here's a simple C# code.
>
>// Begin
>using System;
>using System.Collections;
>using System.Collections.Specialized;
>using Microsoft.SqlServer.Management.Smo;
>using Microsoft.SqlServer.Management.Smo.Agent;
>using Microsoft.SqlServer.Management.Common;
>
>public class Junk
>{
>    public static void Main(string[] args)
>    {
>        Server srv = new Server("MySQLInstance");
>        StringCollection strs = srv.JobServer.Jobs["Job 3"].Script();
>
>        foreach (string str in strs)
>            Console.WriteLine("{0}", str);
>
>    }
>}  // class Junk
>
>// End
>
>Copy the code between //Begin and //End to a text file named junk.cs. And
>copy the following line to a file named csc_junk.bat (a single line with no
>text wrap):
>
>csc junk.cs /reference:"c:\Program Files\Microsoft SQL
>Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll","c:\Program
>Files\Microsoft SQL
>Server\90\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll","c:\Program
>Files\Microsoft SQL
>Server\90\SDK\Assemblies\Microsoft.SqlServer.SmoEnum.dll","c:\Program
>Files\Microsoft SQL
>Server\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll"
>
>Put both files in the same directory (say, c:\junk). Assume you have .NET
>Framework SDK 2.0 installed. Open a command prompt with .NEt SDK Command
>prompt so that you have the environment variables correctly set, and type the
>following to compile the C# code:
>
>cmd>csc_junk.bat
>
>Run junk.exe to script out a job named "Job 3" on a SQL instance named
>MySQLInstance:
>
>cmd>junk.exe
>
>Linchi
>
>> How about showing us your work.  I've been looking for this too and I know
>> nothing about SMO.  I'd appreciate seeing what you did.  I could probably use
>[quoted text clipped - 9 lines]
>> >Thank you everyone who contributed to this post.  I now have exactly what I
>> >was looking for.

"soccerhawg via SQLMonster.com" wrote:

> Thank you.  Since I'm completely new to all of this, I have one more question.
> Using your code as an example, is it easy enough to modify this code to
> script out all jobs on the SQL instance specified?
>
> Thanks.
>
> Linchi Shea wrote:
> >Here's a simple C# code.
> >
> >// Begin
> >using System;
> >using System.Collections;
> >using System.Collections.Specialized;
> >using Microsoft.SqlServer.Management.Smo;
> >using Microsoft.SqlServer.Management.Smo.Agent;
> >using Microsoft.SqlServer.Management.Common;
> >
> >public class Junk
> >{
> >    public static void Main(string[] args)
> >    {
> >        Server srv = new Server("MySQLInstance");
> >        StringCollection strs = srv.JobServer.Jobs["Job 3"].Script();
> >
> >        foreach (string str in strs)
> >            Console.WriteLine("{0}", str);
> >
> >    }
> >}  // class Junk
> >
> >// End
> >
> >Copy the code between //Begin and //End to a text file named junk.cs. And
> >copy the following line to a file named csc_junk.bat (a single line with no
> >text wrap):
> >
> >csc junk.cs /reference:"c:\Program Files\Microsoft SQL
> >Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll","c:\Program
> >Files\Microsoft SQL
> >Server\90\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll","c:\Program
> >Files\Microsoft SQL
> >Server\90\SDK\Assemblies\Microsoft.SqlServer.SmoEnum.dll","c:\Program
> >Files\Microsoft SQL
> >Server\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll"
> >
> >Put both files in the same directory (say, c:\junk). Assume you have .NET
> >Framework SDK 2.0 installed. Open a command prompt with .NEt SDK Command
> >prompt so that you have the environment variables correctly set, and type the
> >following to compile the C# code:
> >
> >cmd>csc_junk.bat
> >
> >Run junk.exe to script out a job named "Job 3" on a SQL instance named
> >MySQLInstance:
> >
> >cmd>junk.exe
> >
> >Linchi
> >
> >> How about showing us your work.  I've been looking for this too and I know
> >> nothing about SMO.  I'd appreciate seeing what you did.  I could probably use
> >[quoted text clipped - 9 lines]
> >> >Thank you everyone who contributed to this post.  I now have exactly what I
> >> >was looking for.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200906/1
>
>

>Sure and the change is simple. Just loop through the jobs. Here is the code:
>
>using System;
>using System.Collections;
>using System.Collections.Specialized;
>using Microsoft.SqlServer.Management.Smo;
>using Microsoft.SqlServer.Management.Smo.Agent;
>using Microsoft.SqlServer.Management.Common;
>
>public class Junk
>{
>    public static void Main(string[] args)
>    {
>        Server srv = new Server("LINCHIS-LT1");
>
>        foreach (Job job in srv.JobServer.Jobs)
>        {
>            Console.WriteLine("-- Script for Job = {0}", job.Name);
>            foreach (string str in job.Script())
>                Console.WriteLine("{0}", str);
>        }
>
>    }
>}  // class Junk
>
>Linchi
>
>> Thank you.  Since I'm completely new to all of this, I have one more question.
>> Using your code as an example, is it easy enough to modify this code to
>[quoted text clipped - 59 lines]
>> >> >Thank you everyone who contributed to this post.  I now have exactly what I
>> >> >was looking for.

"josephs" <u52758@uwe> wrote in message news:97feae9955a3d@uwe...
> 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.
>