May 28, 2004 at 11:15 pm
i want to show reports in my website, and currently iam using asp page which has query written in it, it fires, retrives data and displays it, what iam planning to do is create a job which will fire a query and store the result in html page,
but can i call job from my asp page, what u ppl think about this.?, will it work, how is my idea
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
May 29, 2004 at 8:00 am
You can invoke a job via sp_start_job which is default to the public role so any account can do it. However if it is running at the time it cannot be running again at the same time so another user hitting at the same time would fail. You could use DTS and have it pop a one time run job so each run will occurr. But the question is what does the job buy you and what are you hoping to accomplish?
June 1, 2004 at 3:47 pm
As Antares stated you can use sp_start_job but if it is running you will get an error. I needed to do something like what you seem to want and here is how I went about it.
I created a database (ManageJob) that has 1 view and 3 tables plus all my supporting stored procedures. I then created a job (GetJobToRun) that is run every minute. To activate a job you simply add a record to the RunJob table. I use the NextJobName to add a record to the RunJob table when the current job is done.
You can then start a job from a web page, an application or TSQL by simply adding a record to the RunJob table in the ManageJob database.
I use this to redo snapshot replication subscriptions on demand. Since this requires running SPs on both the publisher and the subscriber I had to implement this design. Basically an application will put a record into my RunJob table on the subscriber machine. It then is picked up by the GetJobToRun job and runs an SP that then adds a record on the publisher machine through a linked server connection. It continues running the SPs via a job in the correct order on each machine until the subscription has been updated.
The basic TSQL is below
-- This is the TSQL called by the GetJobToRun job that is scheduled to run every minute.
IF NOT EXISTS(select * from vActiveConflictingJobs)
BEGIN
DECLARE @JobName sysname
, @C cursor
SET @C = cursor for
SELECT DISTINCT JobName
FROM ManageJob.dbo.RunJob
WHERE Status = 1
OPEN @C
FETCH @C INTO @JobName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb..sp_start_job
@job_name = @JobName
FETCH @C INTO @JobName
END
CLOSE @C
DEALLOCATE @C
END
-- Jobs we want to allow to be started
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.AvailableJobToRun'))
DROP TABLE dbo.AvailableJobToRun
GO
CREATE TABLE dbo.AvailableJobToRun
(
JobName sysname NOT NULL ,
NextJobName sysname NOT NULL ,
LastModifiedDateTime datetime NOT NULL DEFAULT (getdate()),
CONSTRAINT PK_AvailableJobToRun PRIMARY KEY CLUSTERED
(
JobName,
NextJobName
)
)
GO
-- Jobs we want to start
-- Valid Status are as follows
-- 1 Start
-- 2 Running
-- 3 Error during process
-- 4 Successful
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.RunJob'))
DROP TABLE dbo.RunJob
GO
CREATE TABLE dbo.RunJob
(
RunJobID int IDENTITY (1, 1) NOT NULL ,
JobName sysname NOT NULL ,
NextJobName sysname NULL DEFAULT('END') ,
Status int NULL ,
LastModifiedDateTime datetime NULL DEFAULT (getdate()),
CONSTRAINT PK_RunJob PRIMARY KEY CLUSTERED
(
RunJobID DESC
),
CONSTRAINT FK_RunJob_AvailableJobToRun FOREIGN KEY
(
JobName,
NextJobName
) REFERENCES dbo.AvailableJobToRun (
JobName,
NextJobName
)
)
GO
-- This should contain all the jobs in the AvailableJobToRun table plus
-- any jobs that would cause a problem if it ran too. IE: GetJobToRun
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.ConflictingJobs'))
DROP TABLE dbo.ConflictingJobs
GO
CREATE TABLE dbo.ConflictingJobs
(
job_id uniqueidentifier NOT NULL , -- From msdb..sysjobs table
name sysname NOT NULL -- From msdb..sysjobs table
)
GO
-- Gets jobs that are currently running that would conflict with the jobs we want to start.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vActiveConflictingJobs]'))
DROP VIEW dbo.vActiveConflictingJobs
GO
CREATE VIEW vActiveConflictingJobs
AS
SELECT *
FROM (SELECT sj.job_id
, sj.name
, sjs.run_status
, max(sjs.run_date) LastRunDate
, max(sjs.run_time) LastRunTime
, max(sjs.instance_id) LastID
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobhistory sjs
ON sj.job_id = sjs.job_id
JOIN ManageJob.dbo.ConflictingJobs CJ
ON sj.name = CJ.name
GROUP BY sj.job_id, sj.name, sjs.run_status
HAVING max(sjs.instance_id) in(SELECT Max(instance_id)
FROM msdb.dbo.sysjobhistory
GROUP BY job_id)
) L
WHERE run_status = 4
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
June 2, 2004 at 12:04 am
actually what i wanted to do is , iam displaying some reports
what i thought instead of some sql queries firing from an asp page and then displaying report,
THERE IS A WEB ASSISTANT WIZARD WHICH MAKES A WEBPAGE, WE CAN SET THE FREQUENCY
what i plan to do was instead of asp pages quering to tables and getting data and displaying why not make a job which will make a page, update it frequently and store it in a proper folder
user will get the report at the click, so it will be much more faster.. and so on..
sorry for not providing u ppl full details, actually in our company , development has to be very fast so no time to think or experimentation..
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
June 2, 2004 at 5:59 am
If that meets you needs then yes, just schedule and be very happy. Just remember to keep in mind the balance between how often it would actualy run on average and how often you schedule. I saw once where a particualr report was hit once eveyr few hours but the person scheduled every hour, thus it was actually doing more work the scheduled way than would have been normally.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply