Reading the contents of a SQL Server job in VB.NET

Question

I am writing an application that will call a SQL Server job on all our client's systems.

I'm able to execute it, and wait until it's finished. It has an output file that's save location is hard coded into the job however, and this is not the same location on all client's.

Since the job is identical on systems minus this one value, I want my code to read in the script of the job and parse out the location.

I'm having trouble figuring out how to get the actual text of the job itself.

Is this possible in .NET? If so, how?

Answer 1

You can get the contents of the job by using the following query:

SELECT [command]
FROM msdb.dbo.sysjobs job JOIN msdb.dbo.sysjobsteps steps
ON job.job_id = steps.job_id 
WHERE [name] = 'YourJobName'

From there, just create a recordset in VB.NET and use the query above.

Answer 2

@LittleBobbyTables, has the correct query. However, whenever you are in doubt or where the system stores something, look at the script that SSMS generates to create the object. In this case, a job, SSMS calls several system stored procedures. You can look at the system stored procedures code in SSMS and see where things get put:

msdb.dbo.sp_add_job
msdb.dbo.sp_add_jobSchedule
msdb.dbo.sp_add_jobStep
msdb.dbo.sp_add_jobStep_internal

just select the msdb database and then Programmability, then Stored Procedures, and then System Stored Procedures, and then find the ones listed above. Right click on them and "Modify", which will bring up the source code. Just don't save any changes!

which ends up being msdb.dbo.sysjobs and msdb.dbo.sysjobs