July 5, 2007 at 11:11 am
Hello,
I have 'ABC' Job. It should be start with the certain condition that in the Table1. I need to have other job that will every day check this condition and run ABC job.
select count(*)CNT from Table1 where convert(varchar,GenDate,101) = convert(varchar,Getdate(),101)
If CNT=1 then
run ABC job
else
end if
July 5, 2007 at 11:14 am
Look up sp_start_job in BOL.
July 5, 2007 at 12:01 pm
I did exec msdb..sp_start_job @job_name='ABC' but ActiveX script does not like it. Only I can run this code in transact-SQL script.
I have code:
SQL= "select count(*)CNT from AuctionLotteryAdmin_tbl where convert (varchar,GenWinDate,101) = convert(char(10),Getdate(),101)"
Set Conn = CreateObject("ADODB.Connection")
CONN.OPEN "abcd","admin","123"
Set RS = Conn.Execute(SQL)
if CNT=1 then
???????? exec msdb..sp_start_job @job_name='ABC'
end if
July 5, 2007 at 1:41 pm
Which error are you getting?
Are you using Conn.Execute("exec msdb..sp_start_job @job_name='ABC'")?
Or something like that, it's been a while since I've used VBScript 🙂
July 5, 2007 at 1:50 pm
Thanks. I solve the problem by using regular sp.
Declare
@CNT int
DECLARE autorun_cursor CURSOR FOR select count(*)CNT from Table1 where convert(varchar,GenWinDate,101) = convert(char(10),Getdate(),101)
OPEN autorun_cursor
FETCH NEXT FROM autorun_cursor
INTO @CNT
WHILE @@FETCH_STATUS = 0
BEGIN
IF @CNT = 1
exec msdb..sp_start_job @job_name='ABC'
FETCH NEXT FROM autorun_cursor INTO @CNT
END
CLOSE autorun_cursor
DEALLOCATE autorun_cursor
July 6, 2007 at 12:31 am
Great.
But I'm missing something, why is the cursor needed here?
Wouldn't the following code do the same?
Declare @CNT int
select @CNT = count(*) from Table1 where convert(varchar,GenWinDate,101) = convert(char(10),Getdate(),101)
IF @CNT = 1
exec msdb..sp_start_job @job_name='ABC'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply