Start a Microsoft SQL Job via Stored Procedure

by Otto on January 15, 2008

As part of the data warehouse application I built for Northwest Community Credit Union, which works against Open Solutions host system, I had a need to start a SQL job from a web page.  Since the OSI tables are live, our analysts wanted to be able to bring over a specific table whenever they felt like it, without having to call me.  The screen shot below shows the transfer now link button.  Some of the OSI tables have millions of records, doing this all via a web page wouldn’t work.  I decided that the best solution would be to kick off a SQL job that transferred the table and sent the user an email when it was finished.

image

Here is the SQL stored procedure I wrote that starts the job.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Otto R. Radke
-- Create date: 2007-12-12
-- Description:    Starts a SQL Job via Stored Procedure
-- =============================================
ALTER PROCEDURE [dbo].[orr_StartSQLJob] @JobName as nvarchar(2048)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Start the job
    EXEC msdb.dbo.sp_start_job @JobName
END

The data warehouse is a key system that we use to integrate all of the various databases being used at NWCU.  As of right now, we’re able to bring in data from OSI’s 3 different Oracle databases (including all of their various schemas), OSI’s CCM module (which is Microsoft SQL based), and other internal databases.  Prior to converting to OSI, this same data warehouse was used against Fiserv’s XP Systems.

Comments on this entry are closed.

Previous post:

Next post: