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.
Here is the SQL stored procedure I wrote that starts the job.
SET ANSI_NULLS ON -- Start the job
SET QUOTED_IDENTIFIER ON
-- 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)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
EXEC msdb.dbo.sp_start_job @JobName
SET ANSI_NULLS ON
-- Start the job
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.