May 13, 2009 at 4:38 pm
Hi,
I have a procedure in SQL server 2000 that checks for two errors after an insert into a table. One is for a constraint error (err 2627) and one is for a null violation (err 515). Depending on the error, a char variable is updated with an error message and the data and messge written to an error table.
This works fine if I call the proc from query analyzer, but run via a job in sql server 2000 it fails with the 'null violation error' msg 2627 after about 6 inserts into the error table.
How can i make the job run the proc successfully.
thanks in advance.
May 13, 2009 at 6:10 pm
Hmmm. So which error is it (you apparently mixed them up, saying 'null violation error' msg 2627)? If it is error 2627, it's saying that you're trying to insert a row that would result in a duplicate key. Is it the original application insert that's reporting the 2627, or are you getting it from the error-handling step? If it's the first, then take a look at what's in your test data and then search the table for matches on the key. If it's on the error-logging, then look at how you're distinguishing separate events and where a duplicate key could show up.
If that doesn't turn up what the problem is, please post a bit more detail (table definition, insert statements to load it with sample data, the code for your procedure, and the data source that it's using). Your post hasn't given folks much to work with. Take a look at these guidelines for effectively asking for help:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 14, 2009 at 2:51 am
Ok yes. Apologies, it was late at night!
Table Def..
CREATE TABLE [dbo].[run_stats] (
[APPLICATION] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SCHEDTAB] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[GROUPNAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ORDERNO] [int] NOT NULL ,
[JOBNAME] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ODATE] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[START_C] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[STARTTIME] [datetime] NOT NULL ,
[END_C] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ENDTIME] [datetime] NOT NULL ,
[RUNCOUNT] [int] NOT NULL ,
[DURATION_C] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DURATION_SECS] [int] NOT NULL ,
[RETURN_CODE] [decimal](18, 0) NOT NULL ,
[MEMNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NODEID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RUN_DATE] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CMDLINE] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SYSOUT] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TASKTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[run_stats] ADD
CONSTRAINT [PK_run_stats] UNIQUE NONCLUSTERED
(
[ORDERNO],
[RUNCOUNT]
) ON [PRIMARY]
GO
-----------------------------
Yes I got mixed up in the latter part of the post.
Both errors come direct from the insert and the 2627 is a constraint error based upon two columns in the above unique constraint. The 515 is when a column value inserted is null.
The part of the code within the cursor loop is below..
insert into run_stats
(APPLICATION, SCHEDTAB, GROUPNAME, ORDERNO, JOBNAME, ODATE, STARTTIME, START_C, ENDTIME, END_C, RUNCOUNT, DURATION_SECS, DURATION_C, RETURN_CODE, MEMNAME, NODEID, RUN_DATE, CMDLINE, SYSOUT, TASKTYPE)
values(@application, @schedtab, @groupname, @orderno, @jobname, @odate, @start_time, @start_c, @end_time, @end_c, @runcount, @seconds, @duration_c, @returncode, @memname, @nodeid, substring(@start_c,1,8), @cmdline, @sysout,@tasktype);
set @err = @@ERROR
IF @err > 0
BEGIN
if @err = 515 set @error_msg = 'Null not allowed' + Convert(varchar,@err);
if @err = 2627 set @error_msg = 'Constraint violation' + Convert(varchar,@err);
print 'NF .. ' + @error_msg + '. ORDERNO is ' + convert(varchar,@orderno) + '. RUNCOUNT is ' + convert(varchar,@runcount);
insert into run_stats_err
(APPLICATION, SCHEDTAB, GROUPNAME, ORDERNO, JOBNAME, ODATE, STARTTIME, START_C, ENDTIME, END_C, RUNCOUNT, DURATION_SECS, DURATION_C, RETURN_CODE, MEMNAME, NODEID, RUN_DATE, CMDLINE, SYSOUT, TASKTYPE,TIMESTMP,ERROR_MSG)
values(@application, @schedtab, @groupname, @orderno, @jobname, @odate, @start_time, @start_c, @end_time, @end_c, @runcount, @seconds, @duration_c, @returncode, @memname, @nodeid, substring(@start_c,1,8), @cmdline, @sysout,@tasktype,getdate(),@error_msg);
END
If I run the proc from query analyzer, i get the following........
Server: Msg 2627, Level 14, State 2, Procedure update_stats_tst, Line 128
Violation of UNIQUE KEY constraint 'PK_run_stats'. Cannot insert duplicate key in object 'run_stats'.
The statement has been terminated.
NF .. Constraint violation 2627. ORDERNO is 67653. RUNCOUNT is 12
Server: Msg 2627, Level 14, State 2, Procedure update_stats_tst, Line 128
Violation of UNIQUE KEY constraint 'PK_run_stats'. Cannot insert duplicate key in object 'run_stats'.
The statement has been terminated.
NF .. Constraint violation 2627. ORDERNO is 67817. RUNCOUNT is 65
Server: Msg 2627, Level 14, State 2, Procedure update_stats_tst, Line 128
Violation of UNIQUE KEY constraint 'PK_run_stats'. Cannot insert duplicate key in object 'run_stats'.
The statement has been terminated.
NF .. Constraint violation 2627. ORDERNO is 67817. RUNCOUNT is 66
As you can see, the insert fails and the appropriate message is displayed and action taken.
From 85 rows, the 78 duplicates were loaded to the error table and the 7 'good' rows were correctly loaded to the main table.
The run_stats_err table is exactly the same as the run_stats table except all columns are nullable, there are no constraints and it has two extra columns for timestmp and error_msg which are type datetime and varchar(150)
[TIMESTMP] [datetime] NOT NULL ,
[ERROR_MSG] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL .
But, as I said in the original post, if I run this proc as a job within SQLServer Enterprise Manager (v8), then it always fails with the following message...
Executed as user: NT AUTHORITY\SYSTEM. before if [SQLSTATE 01000] (Message 0) Violation of UNIQUE KEY constraint 'PK_run_stats'. Cannot insert duplicate key in object 'run_stats'. [SQLSTATE 23000] (Error 2627) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
When I look at the run_stats_err table afterwards, I can see it inserted 6 error rows and 3 ok rows before failing.
despite the code, keys, definitions etc, it seems to me that it is running the proc via a job that is the main issue, since it always works fine when run via analyzer.
So the problem isn't the duplicates or nulls, but that the procedure handles them and completes ok when run via analyzer, but not when run as a job defined in Enterprise Manager.
thanks for any help.
May 18, 2009 at 5:37 pm
I'll ask around on this. To summarize: You have a stored procedure that gathers data somehow, then loops with a cursor to insert that data into table run_stats. The procedure tests the error code from the Insert and, if it's either a duplicate key or NULL in a non-nullable column error, inserts the data with a time stamp and the error message to table run_stats_err. When you run this proc in the query analyzer, it works as expected, putting out error messages and inserting data to the appropriate table. When, however, you run this same proc as a job, it handles the first six (of seventy-eight in your test data) duplicate keys and then stops.
May 18, 2009 at 5:55 pm
Thanks.
Yes, that's exactly right. I've googled handling errors and the code used is from these. I've tried using goto Fail on error, and if not error then continue and just using if statements as in this example, and always get the same problem.
There are three possible values that could be null and I've had to use ..
if @application is null or @schedtab is null or @groupname is null .......
for one error and currently the other is not handled, although I shouldn't get duplicates anyway (in theory!).
If anyone can solve this issue, I'd be really grateful.
May 18, 2009 at 7:45 pm
Please post the complete procedure code and the CREATE TABLE definitions of the run_stats, run_stats_err and the source tables.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 19, 2009 at 6:50 am
If you could post the full definition of the proc, and the source tables, I bet I can not only fix this, but eliminate the cursor and make it much more efficient.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 19, 2009 at 1:22 pm
But, as I said in the original post, if I run this proc as a job within SQLServer Enterprise Manager (v8), then it always fails with the following message...
Executed as user: NT AUTHORITY\SYSTEM. before if [SQLSTATE 01000] (Message 0) Violation of UNIQUE KEY constraint 'PK_run_stats'. Cannot insert duplicate key in object 'run_stats'. [SQLSTATE 23000] (Error 2627) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
The first post seems somewhat misleading. This is clearly a duplicate key error.... which means there are duplicate inserts being attempted, despite anything else. A couple of thoughts: Are you sure the job is running the same procedure as you are running through query manager? Are you sure the parameters are the same? Are you sure the source table is the same? Are you sure the target tables are the same? Are you sure the procedure in the job is running from the same db as the procedure you are running in query analyzer. Are sure you are on the right server?
I'm sure you are are tiredly answering "Yes... yes... yes... " Now go back and actually check. 🙂
The odds are likely that you are overlooking one of the above, instead of a procedure mysteriously not working depending on where it's run from. I recently spent two days running down a problem in a procedure being run as a scheduled job. Turned out there was a typo in one of the input parameters. So like an old mentor of mine once told me: "If you're even holding your tongue differently when you press the Enter key, pay attention to that." When you read "the same" think "identical."
But by all means post your code and take Barry and Gus up on the offer to get rid of the cursor if at all possible. Your system will be glad you did. 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 19, 2009 at 3:44 pm
yes, yes, yes to the previous post.
exec update_stats_tst1 (from query analyzer with correct database showing).
exec update_stats_tst1 (from job step - showing the same database in the step)
I am definitely on the same server when i run it- I have to Remote desktop to it before accessing analyzer and Enterprise Manager - and they are connecting to the same.
There are no parameters passed in.
I am 100% certain the proc run is the same. Apart from anything else, I was amending the proc adding debug statements, eg 'after if' etc and if I was connecting to different a database etc, i would not be able to see the rows added to the error table after running the proc, when querying the table from analyzer.
I know i am attempting inserts of duplicates, but in reality these should never happen unless the lookup 'from dates' etc are altered, it is when one of three values is null that really concerns me and trapping the rows will allow me to work back to determine why. But while adding the trap for that, thought I would make it bullet proof as it were.
And it's not the duplicates/nulls that is my real problem, I just want to understand why it doesn't work run by a job.
Anyway, Here goes in adding the data requested...
Below are the table defs, proc, Job error message and some output from running the proc in analyzer.
If you can do as you say, then great and many thanks..
I have left it in for completeness, but the initial part of the proc before the cursor is irrelevant and part of the process that populates the tmp table before the main cursor.
The run_stats definition is in a previous post, and I have since changed return_code from decimal to smallint to match the other tables and the procedure variable def, but this made no difference.
TABLE run_stat_tmp definition
========================
CREATE TABLE [dbo].[run_stats_tmp] (
[ORDERNO] [int] NOT NULL ,
[JOBNAME] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ODATE] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[START_C] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[END_C] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RUNCOUNT] [int] NOT NULL ,
[RETURN_CODE] [smallint] NOT NULL ,
[MEMNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NODEID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TASKTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
TABLE run_stats_err definition - same as run_stats but all cols are nullable except for two extra cols TIMESTMP and ERROR_MSG
=======================
CREATE TABLE [dbo].[run_stats_err] (
[APPLICATION] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SCHEDTAB] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GROUPNAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ORDERNO] [int] NULL ,
[JOBNAME] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ODATE] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[START_C] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STARTTIME] [datetime] NULL ,
[END_C] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ENDTIME] [datetime] NULL ,
[RUNCOUNT] [int] NULL ,
[DURATION_C] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DURATION_SECS] [int] NULL ,
[RETURN_CODE] [smallint] NULL ,
[MEMNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NODEID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RUN_DATE] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CMDLINE] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SYSOUT] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TASKTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TIMESTMP] [datetime] NOT NULL ,
[ERROR_MSG] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
PROCEDURE CODE
=========================
CREATE PROCEDURE dbo.update_stats_tst
as
begin
-- Written by: Nigel Freshwater
declare @err int;
declare @error_msg varchar(150);
declare @last_endtime char(14);
declare @tmp_rows_inserted int;
declare @rows_inserted int;
declare @procname varchar(50);
declare @application varchar(20);
declare @schedtab varchar(20);
declare @groupname varchar(20);
declare @jobname varchar(64);
declare @orderno int;
declare @odate char(8);
declare @start_c char(14);
declare @end_c char(14);
declare @runcount int;
declare @returncode smallint;
declare @memname varchar(30);
declare @nodeid varchar(50);
declare @server varchar(50);
declare @line_count int;
declare @date_str char(19);
declare @start_time datetime;
declare @end_time datetime;
declare @duration_c char(8);
declare @seconds int;
declare @cmdline varchar(1000);
declare @sysout varchar(100);
declare @time_from char(14);
declare @time_to char(14);
declare @tasktype char(1);
declare @message_txt1 varchar(200);
declare @message_txt2 varchar(200);
set @procname = 'Update stats';
set @line_count = 0;
declare @rest_of_line varchar(100);
begin transaction;
SELECT @last_endtime = parm_value from parameter_control
where parameter = 'last end time';
--print 'last time = ' + @last_endtime;
if @last_endtime is null SELECT @last_endtime = max(TO_DATE) from dbo.stats_process_log where procname = @procname;
-- Quick and dirty way to add 1 second to latest end time may result in invalid time, e.g. 235960 but it still does the job!
set @time_from = convert(numeric,@last_endtime) + 1;
-- The time below should be a few milliseconds before current time, but just incase a job row is inserted bwteen the AJF extract and this proc, we use the time from the AJF insert
select @time_to = replace(replace(replace(CONVERT(VARCHAR(19), max(RUN_DATE), 120),':',''),'-',''),' ','') from dbo.stats_process_log where procname = 'AJF extract';
print ' Extracting data where end time greater than ' + @last_endtime + ' and upto ' + @time_to;
truncate table run_stats_tmp;
-- 5080 is 'failed to submit', 5164 is 'dummy. status changed to ok' messages
insert into run_stats_tmp
(ORDERNO, JOBNAME, ODATE, START_C, END_C, RUNCOUNT, RETURN_CODE, MEMNAME, NODEID, TASKTYPE)
select JI.ORDERNO, JI.JOBNAME, (select top 1 ODATE from ctrlm630.dbo.CMR_IOALOG IO where IO.ORDERNO = JI.ORDERNO)
, JI.STARTRUN, JI.ENDRUN, JI.RUNCOUNT, JI.OSCOMPSTAT, JI.MEMNAME, JI.NODEID, JI.TASKTYPE
from ctrlm630.dbo.CMR_JOBINF JI where JI.ENDRUN between @time_from and @time_to
union all
select ORDERNO, JOBNAME, ODATE, LOGDATE + LOGTIME, LOGDATE + LOGTIME, 0,
RETURN_CODE = case MSGID
when '5080' then 90
when '5164' then 0
end
, MEMNAME, NODEID,
TASKTYPE
from ctrlm630.dbo.CMR_IOALOG
where MSGID in ( '5080' , '5164' ) and LOGDATE + LOGTIME between @time_from and @time_to;
set @tmp_rows_inserted = @@RowCount;
set @message_txt1 = 'Rows inserted to tmp table = ' + rtrim(Convert(char,@tmp_rows_inserted));
print @message_txt1;
-- Define cursor that reads in data and appends to main table, adding in reformatted date data etc.
-- The order by here is needed to ensure the latest endtime is the last one processed, as this is stored within the loop and used to update 'to date' time afterwards
-- also convert TASKTYPE to more obvious values, i.e JOB from B to J, DUMMY from U to D. C for command left as is
declare stats_tmp cursor local
for
select ORDERNO, JOBNAME, ODATE, START_C, END_C, RUNCOUNT, RETURN_CODE, MEMNAME, NODEID,
TASKTYPE = case TASKTYPE
when 'B' then 'J'
when 'U' then 'D'
else TASKTYPE
end
from run_stats_tmp
order by END_C asc;
OPEN stats_tmp;
-- Fetch first row from table
FETCH NEXT FROM stats_tmp
into @orderno, @jobname, @odate, @start_c, @end_c, @runcount, @returncode, @memname, @nodeid, @tasktype;
WHILE @@FETCH_STATUS = 0
BEGIN
set @line_count = @line_count +1;
insert into run_stats_err
( ORDERNO, JOBNAME, ODATE, STARTTIME, START_C, ENDTIME, END_C, RUNCOUNT, DURATION_SECS, DURATION_C, RETURN_CODE, MEMNAME, NODEID, RUN_DATE, CMDLINE, SYSOUT, TASKTYPE,TIMESTMP,ERROR_MSG)
values(@orderno, @jobname, @odate, @start_time, @start_c, @end_time, @end_c, @runcount, @seconds, @duration_c, @returncode, @memname, @nodeid, substring(@start_c,1,8), @cmdline, @sysout,@tasktype,getdate(),'ALL OK' + convert(varchar,@line_count));
set @application = null;
set @schedtab = null;
set @groupname = null;
set @cmdline = null;
select top 1 @application = AL.APPLICATION, @schedtab = AL.SCHEDTAB, @groupname = AL.GROUPNAME, @cmdline = AL.CMDLINE
FROM application_lookup AL
where AL.ORDERNO = @orderno
and AL.RUNCOUNT <= @runcount
order by AL.RUNCOUNT desc;
-- If above query returns zero rows, it is because we are not inserting every run No of each job into application lookup
-- and entry for job may have a higher runcount than this job, so we select the lowest above this run No.
if @@RowCount = 0
select top 1 @application = AL.APPLICATION, @schedtab = AL.SCHEDTAB, @groupname = AL.GROUPNAME, @cmdline = AL.CMDLINE
FROM application_lookup AL
where AL.ORDERNO = @orderno
order by AL.RUNCOUNT asc;
exec dbo.char_date_conv @start_c, @date_str output, @start_time output;
exec dbo.char_date_conv @end_c, @date_str output, @end_time output;
set @seconds = datediff(second, @start_time, @end_time);
set @duration_c = dbo.secs_hours_conv(@seconds);
IF @returncode = 90
BEGIN
set @sysout = 'FAILED TO SUBMIT SCRIPT';
set @cmdline = 'FAILED TO SUBMIT SCRIPT';
-- determine correct runcount for 'FAILED TO SUBMIT' records. Initially set at zero, but is not necessarily first run, so check for previous runs and add one if any.
select @runcount = coalesce(max(runcount) + 1,1) from run_stats where ORDERNO = @orderno;
--if @@rowcount = 0 or @set @runcount = 1;
END
ELSE IF @tasktype = 'D'
BEGIN
set @sysout = null
set @cmdline = 'DUMMY JOB';
-- determine correct runcount for dummy job records. Initially set at zero, but is not necessarily first run, so check for previous runs and add one if any.
select @runcount = coalesce(max(runcount) + 1,1) from run_stats where ORDERNO = @orderno;
END
ELSE
BEGIN
-- test if nodeid exists in sysout_nodes table. If not, set sysout to null otherwise set true sysout name
select @server = sysout_dir from sysout_nodes where nodeid = @nodeid;
if @@RowCount = 0 set @sysout = null
else set @sysout = @server + '/' + replace(replace(@memname,'.','_'),'-','_') + '.LOG_' + right('00000' + dbo.F_NUMERIC_TO_BASE_N(@orderno),6) +'_' + right(100000 + @runcount,5)
END
print 'before if';
insert into run_stats
(APPLICATION, SCHEDTAB, GROUPNAME, ORDERNO, JOBNAME, ODATE, STARTTIME, START_C, ENDTIME, END_C, RUNCOUNT, DURATION_SECS, DURATION_C, RETURN_CODE, MEMNAME, NODEID, RUN_DATE, CMDLINE, SYSOUT, TASKTYPE)
values(@application, @schedtab, @groupname, @orderno, @jobname, @odate, @start_time, @start_c, @end_time, @end_c, @runcount, @seconds, @duration_c, @returncode, @memname, @nodeid, substring(@start_c,1,8), @cmdline, @sysout,@tasktype);
set @err = @@ERROR;
IF @err 0
BEGIN
rollback transaction;
if @err = 515 set @error_msg = 'My message - Null not allowed - Error code :' + convert(varchar,@err);
if @err = 2627 set @error_msg = 'MY message - Constraint violation - Error code ' + convert(varchar,@err);
print @error_msg;
begin transaction;
insert into run_stats_err
(APPLICATION, SCHEDTAB, GROUPNAME, ORDERNO, JOBNAME, ODATE, STARTTIME, START_C, ENDTIME, END_C, RUNCOUNT, DURATION_SECS, DURATION_C, RETURN_CODE, MEMNAME, NODEID, RUN_DATE, CMDLINE, SYSOUT, TASKTYPE,TIMESTMP,ERROR_MSG)
values(@application, @schedtab, @groupname, @orderno, @jobname, @odate, @start_time, @start_c, @end_time, @end_c, @runcount, @seconds, @duration_c, @returncode, @memname, @nodeid, substring(@start_c,1,8), @cmdline, @sysout,@tasktype,getdate(),@error_msg);
--commit transaction;
END
FETCH NEXT FROM stats_tmp
into @orderno, @jobname, @odate, @start_c, @end_c, @runcount, @returncode, @memname, @nodeid, @tasktype;
END
print 'after loop'
CLOSE stats_tmp;
DEALLOCATE stats_tmp;
set @err = @@ERROR;
print 'last retcode is ' + convert(varchar,@err);
end;
GO
ERROR FROM JOB
=========================
Executed as user: NT AUTHORITY\SYSTEM.
Extracting data where end time greater than 20090519213000 and upto 20090519213000 [SQLSTATE 01000] (Message 0)
Rows inserted to tmp table = 0 [SQLSTATE 01000] (Message 0)
before if [SQLSTATE 01000] (Message 0)
Violation of UNIQUE KEY constraint 'PK_run_stats'. Cannot insert duplicate key in object 'run_stats'. [SQLSTATE 23000] (Error 2627)
The statement has been terminated. [SQLSTATE 01000] (Error 3621).
OUTPUT FROM QUERY ANALYZER (some, just to get the idea)
==============================
Extracting data where end time greater than 20090519213000 and upto 20090519213000
(0 row(s) affected)
Rows inserted to tmp table = 0
(1 row(s) affected)
before if
Server: Msg 2627, Level 14, State 2, Procedure update_stats_tst1, Line 166
Violation of UNIQUE KEY constraint 'PK_run_stats'. Cannot insert duplicate key in object 'run_stats'.
The statement has been terminated.
in error if
MY message - Constraint violation - Error code 2627
(1 row(s) affected)
(1 row(s) affected)
before if
(1 row(s) affected)
(1 row(s) affected)
before if
(1 row(s) affected)
(1 row(s) affected)
before if
Server: Msg 2627, Level 14, State 2, Procedure update_stats_tst1, Line 166
Violation of UNIQUE KEY constraint 'PK_run_stats'. Cannot insert duplicate key in object 'run_stats'.
The statement has been terminated.
in error if
MY message - Constraint violation - Error code 2627
(1 row(s) affected)
(1 row(s) affected)
before if
Server: Msg 2627, Level 14, State 2, Procedure update_stats_tst1, Line 166
Violation of UNIQUE KEY constraint 'PK_run_stats'. Cannot insert duplicate key in object 'run_stats'.
The statement has been terminated.
in error if
MY message - Constraint violation - Error code 2627
(1 row(s) affected)
(1 row(s) affected)
before if
Server: Msg 2627, Level 14, State 2, Procedure update_stats_tst1, Line 166
Violation of UNIQUE KEY constraint 'PK_run_stats'. Cannot insert duplicate key in object 'run_stats'.
The statement has been terminated.
in error if
MY message - Constraint violation - Error code 2627
(1 row(s) affected)
May 19, 2009 at 6:11 pm
I don't known for sure why this would behave differently as a job rather than from QM, but it may be part of the problem that the BEGIN TRANSACTION at the top is not balanced with a COMMIT at the end -- only a conditional ROLLBACK. It would appear that the sucessful inserts to run_stats would be rolled back upon hitting the first NULL or Dup Key error. Then, there's a new "Begin Transaction" in the error-handling code which itself isn't terminated explicitly.
Running from the QM may be allowing the connection to continue while running as a job does not. I'll defer to the more experienced folks who've joined this discussion as to whether that's a likely cause for the difference. Meanwhile, I'm glad to see that Gus (GSquared) has taken the challenge of re-forming this process with a set-based approach.
May 19, 2009 at 7:09 pm
The errors in the job output in the GUI are truncated and you're probably not seeing the whole thing and that's why it looks different. Notice that it didn't say the job was terminated.... it said the statement was terminated just like the run from QA did.
Also, as some have suggested, there's no need for a cursor here. I'll go even further and say there's no need to program by exception at all. You have all of the data you want to insert in a temp table to start with. Add a couple of validation columns to the temp table and pre-validate the data using updates on the temp table with joins to the target table. When you do the final insert from the temp table to the target table, use the validation column(s) to identify what's good to insert. There should be no errors to rollback and you shouldn't need a BEGIN TRANSACTION either.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 20, 2009 at 1:23 am
Unfortunately, the code is the latest 'incarnation' of attempts. The print statements have changed and I have played around endlessly with begins, rollbacks and commits in trying to get it to work.
I think in the first version, there were none at all, since the failed insert didn't really need rolling back and all updates were committed at the end when the proc finished.
I will run the code again with begins,commits added/remove further today to check.
With some versions while trying to get it to work, I also managed to get the error
"Associated statement is not prepared [SQLSTATE HY007] (Error 0)" errors which I could not understand and added/removed begins,commits to try and correct it. - In the end, I started with the standard proc again and added the error trapping again, as I didn't manage to resolve that error.
thanks.
May 20, 2009 at 3:26 pm
If i don't need a cursor and one of the members can show how, then great.
However, if i have another validation step, wouldn't i negate any improvement in speed and efficiency by removing the cursor.
I need validation of some sort as I need to trap the rows where one of three values is null and investigate soon after further back in the data creation process.
Also, regardless of wheher or not the error message in the job is truncated, it is still failing as a job overall and not processing all rows whereas the proc run via QA does.
I'm keen to learn of a 'better way' than cursors, but as the code runs every 4 minutes to update the table, the number of rows inserted by this part of the process on any one run is at most, about 100, so only runs for a few seconds. More important to me for this, is to get the job to process all rows and complete.
thanks again.
May 21, 2009 at 7:30 am
If an error happens in a job the whole job fails.
No matter if you have error handler in the code or not.
Forget your Try-Catch habits while in T-SQL, they are useless here.
_____________
Code for TallyGenerator
May 22, 2009 at 8:45 am
Sergiy (5/21/2009)
If an error happens in a job the whole job fails.No matter if you have error handler in the code or not.
Forget your Try-Catch habits while in T-SQL, they are useless here.
I am new to SS2K5. would you please explain in greater detail WHY ?
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply