June 11, 2007 at 12:50 am
Hi All
I have a error check script that runs at the end of a batch run ,that does a basic count and then if the count is greater than zero it raises an error. Before doing the check I insert the count and date into a table ,just so that I can audit the job in event of an anomaly.If I run the insert part in step 1 manually it runs fine,however at 1pm in the morning automatically via a job,then it does not do the insert and it does not generate and error.
Please advise
So Step 1
insert into dm_cyclecheck
SELECT COUNT(*) as CycleCount,getdate() as TimeofRun ,T6XGCT as T6Date
FROM N9DEBTP, T6DEBTP WHERE N9BECT <> 0
AND N9BECT <= T6XGCT
group by T6XGCT
Step2
--insert into dm_cyclecheck values (@COUNTER,getdate(),@T6)
DECLARE @COUNTER INT,@T6 NUMERIC
SET @COUNTER = 0
SELECT @counter=COUNT(*)
FROM N9DEBTP, T6DEBTP WHERE N9BECT <> 0
AND N9BECT <= T6XGCT
group by T6XGCT
print @counter
--select * from dm_cyclecheck
IF @COUNTER = 0
BEGIN
USE MSDB
EXEC SP_START_JOB @JOB_NAME = 'DM_CYCLE_CHECK_OK'
END
IF @COUNTER > 0
BEGIN
USE MSDB
EXEC SP_START_JOB @JOB_NAME = 'DM_CYCLE_CHECK_ERROR'
END
Thanks
June 15, 2007 at 4:39 am
What happens if you run the job not at 1am (I assume that's what you mean by 1pm in the morning), but at the same time you try to run it manually? This will tell us whether the problem is with the time of day the query is run, or the user that runs the query.
John
June 20, 2007 at 4:31 am
What does those job DM_CHECK_ERROR/OK do. Does it call a stored procedure or any TSQL code.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 21, 2007 at 12:08 am
The DM Check Job does the following if the count is > 0 it sends a condition via control M(via DTS ->BAT File) to our operators,that theres a problem with the batch run,at which point the batch is abended ,until the batch issues are resolved.
It runs at around 1 am in the morning.
thanks
June 22, 2007 at 7:38 am
USE statements are not allowed in procedures.
IF @COUNTER = 0
BEGIN
USE MSDB
EXEC SP_START_JOB @JOB_NAME = 'DM_CYCLE_CHECK_OK'
END
IF @COUNTER > 0
BEGIN
USE MSDB
EXEC SP_START_JOB @JOB_NAME = 'DM_CYCLE_CHECK_ERROR'
END
Change to:
IF @COUNTER = 0
BEGIN
EXEC MSDB.dbo.SP_START_JOB @JOB_NAME = 'DM_CYCLE_CHECK_OK'
END
IF @COUNTER > 0
BEGIN
EXEC MSDB.dbo.SP_START_JOB @JOB_NAME = 'DM_CYCLE_CHECK_ERROR'
END
June 22, 2007 at 3:29 pm
Im not using a stored proc its a job step,and believe it or not the use statement works in it.The problem im having is prior to this point though,my problem is that this insert doesnt work,which is the 1st step.
insert into dm_cyclecheck
SELECT COUNT(*) as CycleCount,getdate() as TimeofRun ,T6XGCT as T6Date
FROM N9DEBTP, T6DEBTP WHERE N9BECT <> 0
AND N9BECT <= T6XGCT
group by T6XGCT
June 22, 2007 at 4:01 pm
It seems like we are missing part of the story here. JC, there is obviously more code than what you've posted here. Can you post your code? What is happening prior to the INSERT? Are there any triggers on your dm_cyclecheck table?
June 22, 2007 at 4:07 pm
ok so before i do my check,i insert a row count into a table,for a simple audit trail
this is done by using this statement :
So Step 1
insert into dm_cyclecheck
SELECT COUNT(*) as CycleCount,getdate() as TimeofRun ,T6XGCT as T6Date
FROM N9DEBTP, T6DEBTP WHERE N9BECT <> 0
AND N9BECT <= T6XGCT
group by T6XGCT
thereafter i do my checking.
The problem im having is that the above step is not doing the insert into the table,but is doing all the other steps ie;if i run step 1 manually it works,but via a schedule.
Step2
--insert into dm_cyclecheck values (@COUNTER,getdate(),@T6)
DECLARE @COUNTER INT,@T6 NUMERIC
SET @COUNTER = 0
SELECT @counter=COUNT(*)
FROM N9DEBTP, T6DEBTP WHERE N9BECT <> 0
AND N9BECT <= T6XGCT
group by T6XGCT
print @counter
--select * from dm_cyclecheck
IF @COUNTER = 0
BEGIN
USE MSDB
EXEC SP_START_JOB @JOB_NAME = 'DM_CYCLE_CHECK_OK'
END
IF @COUNTER > 0
BEGIN
USE MSDB
EXEC SP_START_JOB @JOB_NAME = 'DM_CYCLE_CHECK_ERROR'
END
June 22, 2007 at 4:13 pm
What security context is the job running under? Does it have permissions to insert into your table? I would set up a mock job to do a simple INSERT into your table and run it via SQL Agent and see what happens (this is so you don't have to wait until 1:00 AM to troubleshoot). This sounds like a permissions problem to me.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply