May 22, 2009 at 10:34 am
J: This thread is in the context of SQL 2000. Try/Catch is only valid in 2005 and beyond.
__________________________________________________
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 22, 2009 at 4:37 pm
J (5/22/2009)
I am new to SS2K5. would you please explain in greater detail WHY ?
Because "T" in T-SQL means "transactional".
Because while you try you may affect the data used in other processes.
Because T-SQL is meant to run in background, absolutely unattended, and Try-Catch approach is meant for cases of unknown errors to pass decision making to front user. In SQL you need to program the way out of every possible error. And if you know what kind of errors to expect you may easily prevent it from happening.
Like in case with OP - instead of trying to catch the errors he needs just check the incoming data for validity before starting the transaction.
_____________
Code for TallyGenerator
May 23, 2009 at 8:58 pm
Sergiy (5/22/2009)
J (5/22/2009)
I am new to SS2K5. would you please explain in greater detail WHY ?Because "T" in T-SQL means "transactional".
Because while you try you may affect the data used in other processes.
Because T-SQL is meant to run in background, absolutely unattended, and Try-Catch approach is meant for cases of unknown errors to pass decision making to front user. In SQL you need to program the way out of every possible error. And if you know what kind of errors to expect you may easily prevent it from happening.
Like in case with OP - instead of trying to catch the errors he needs just check the incoming data for validity before starting the transaction.
{insert very loud applause here}... Sergiy for President!
--Jeff Moden
Change is inevitable... Change for the better is not.
May 27, 2009 at 12:12 pm
I've tried again with/without out begin/commits, ensuring there are pairs and all possible combinations, however if Sergiy is right, then clearly I'm wasting my time and will have to put in upfront validation as others suggest. Although this wasn't really the answer I was expecting!
I'll await Gus's improved code and accept my fate.
thanks in advance Gus.
August 14, 2009 at 2:23 pm
Sorry for the long delay in replying here. I lost track of this one somewhere along the way.
The proc references tables and other procs that you haven't provided definitions for.
SELECT @last_endtime = parm_value from parameter_control
where parameter = 'last end time';
Looks like "parameter_control" should be a table. I don't see a definition for it, nor a script to populate it.
Same part of the code also references dbo.stats_process_log, which looks like it's probably another table.
References ctrlm630.dbo.CMR_JOBINF in a From clause, and ctrlm630.dbo.CMR_IOALOG in another From later in the same Select Union statement.
References application_lookup in another From clause a bit further down. Uses this one twice.
I don't see a definition here for this proc:
exec dbo.char_date_conv @start_c, @date_str output, @start_time output;
Looks like there's a UDF used here:
set @duration_c = dbo.secs_hours_conv(@seconds);
Plus, there's no insert script for sample data to run this on.
Those things missing make this much more difficult to solve.
- 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
August 14, 2009 at 2:29 pm
Further analysis:
Variable @last_endtime is declared as char(14), then has various things done to it, and is then converted to numeric and has 1 added to it to add a second to the time, with a note that this can result in impossible times.
Variable @time_to has a host of string functions performed on it, but looks like it's being used as if it were a datetime value. It is declared as char(14), but its use looks time-based.
Can you clarify why it's being done that way?
It looks like the whole thing is looking at a list of processes, figuring out when they started, when they ended, and how long they took to do so, and then also looking for various errors along the way. Is that correct?
- 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
August 14, 2009 at 6:56 pm
Hi,
thanks for replying Gsquared. I'll go through your queries one by one and hopefully can detail some of the periphery code that doesn't need to be considered and then later will supply some data.
SELECT @last_endtime = parm_value from parameter_control
where parameter = 'last end time';
Yes this is a simple table that just selects a value that written to the table last time so the process only picks up data newer than when it last ran. The proc runs every 5 minutes in reality. For the purpose of testing and selecting from static data, this bit is not required or can just be set to a value lower that earliest time in main table.
dbo.stats_process_log is a table that records how many rows added each time. Again, this is peripheral and is not required for altering the logic.
The references to CMR_JOBINF and CMR_IOALOG can be ignored as this cursor populates a temp table on which the main cursor runs.
To lessen your time/work you could just replace the main cursor and so I will provide data for the temp table (run_stats_tmp) that this union populated. - Unless that is, your solution would attempt to do the whole thing, but I'd be happy seeing just the main cursor replaced to get the idea.
application_lookup is another table used to store data that is sourced from elsewhere due to the main application setup.
I will provide data for this table and definitions later.
dbo.char_date_conv and secs_hours_conv are a small proc and UDF to convert datetime strings. The start and end time columns of each record from one table is in the format 'yyyymmddhhmmss' (14 chars) and char_date_conv is a proc to convert this to a 'yyyy-mm-dd hh:mm:ss' (19chars)
which it outputs and also uses as input for the second output value of a true datetime value. The 19char value is not no used even though still returned.
Once the start/end times are converted to true datetime values, a datediff is used to get the seconds difference between them (i.e. the job duration). This is then passed to function sec_hours_conv to reformat the seconds value to format 'hh:mm:ss'. This is because the final table has columns for the values in both formats.
last_endtime/time_to
To be exact and ensure no updates are missed, all selects are done where values are between time_from and time_to values (I thought between was better than > and <=).
The bit about adding 1 to last_endtime was to ensure time_from was the next second. It may result in an invalid time value e.g. 20090814083059 (8.30am and 59secs) + 1 is 20090814083060 which is a nonsence time, but does the job as it is less than the true value of one second later which is 20090814083100. This also works when the time is the last second of the last day of a month!
And yes, basically, the code takes process values from several tables where they are not permanent and in unfriendly values and merges them together in a new table where it is easier to lookup the required data.
The application_lookup table mentioned occasionally has incomplete date resulting in nulls for three columns which was why I introduced the 515 trap for a 'nulls not allowed' constraint so I could easily see from an error table when they were present to aid tracking down the cause.
Also, each record should be unique based upon two columns (orderno and run_count) and the 2627 unique constraint trap was incase anyone inadvertantly reran the proc incorrectly altering input times and caused duplicates. I suppose i could have done an insert combined with not exists but thought this would be slower with 1000000 rows plus.
I will provide more defs and data later.
thanks
September 9, 2009 at 1:21 pm
Judging by the lapsed time, I'm hoping this one was resolved. Was it?
- 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
September 10, 2009 at 1:55 am
Hi.
No sorry, its not resolved. I was waiting for a reply to a PM about amount of data and attaching files and then providing the data got pushed down the workstack. Sorry.
Here is data for the application_lookup table
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15643','169518',' ','1','Sep 4 2009 5:27AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15650','169524',' ','1','Sep 4 2009 5:27AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15652','169526',' ','8','Sep 5 2009 5:57AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','12','Sep 4 2009 6:12AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','13','Sep 4 2009 6:15AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','14','Sep 4 2009 6:18AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','15','Sep 4 2009 6:21AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','16','Sep 4 2009 6:27AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','17','Sep 4 2009 6:30AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','18','Sep 4 2009 6:33AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','19','Sep 4 2009 6:39AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','20','Sep 4 2009 6:42AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','21','Sep 4 2009 6:45AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','22','Sep 4 2009 6:51AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','23','Sep 4 2009 6:54AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','24','Sep 4 2009 6:57AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','25','Sep 4 2009 7:03AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15648','169522',' ','1','Sep 4 2009 5:27AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','1','Sep 4 2009 5:27AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15644','169519',' ','1','Sep 4 2009 5:27AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15646','169521',' ','0','Sep 4 2009 5:27AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15649','169523',' ','0','Sep 4 2009 5:27AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15651','169525',' ','1','Sep 4 2009 5:27AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15645','169520',' ','0','Sep 4 2009 5:27AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15645','169520',' ','1','Sep 4 2009 5:30AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','2','Sep 4 2009 5:30AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15649','169523',' ','1','Sep 4 2009 5:30AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','3','Sep 4 2009 5:33AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','4','Sep 4 2009 5:39AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','5','Sep 4 2009 5:42AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','6','Sep 4 2009 5:48AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','7','Sep 4 2009 5:51AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','8','Sep 4 2009 5:54AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15652','169526',' ','0','Sep 4 2009 5:57AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','9','Sep 4 2009 5:57AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15646','169521',' ','1','Sep 4 2009 5:57AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','11','Sep 4 2009 6:06AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','28','Sep 4 2009 7:15AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','29','Sep 4 2009 7:18AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','30','Sep 4 2009 7:21AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','31','Sep 4 2009 7:27AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','32','Sep 4 2009 7:30AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','33','Sep 4 2009 7:33AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','34','Sep 4 2009 7:39AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','35','Sep 4 2009 7:42AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','36','Sep 4 2009 7:45AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','37','Sep 4 2009 7:51AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','38','Sep 4 2009 7:54AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','26','Sep 4 2009 7:06AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','27','Sep 4 2009 7:09AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15642','169517',' ','39','Sep 4 2009 7:57AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15644','169519',' ','2','Sep 4 2009 8:54AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15652','169526',' ','1','Sep 4 2009 10:48AM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15652','169526',' ','2','Sep 4 2009 12:18PM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15652','169526',' ','4','Sep 4 2009 2:30PM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15652','169526',' ','6','Sep 4 2009 2:36PM' UNION ALL
SELECT 'TEST_APP','TEST_SCHED','TEST_GRP','20090903','15652','169526',' ','7','Sep 4 2009 3:03PM' UNION ALL
September 10, 2009 at 2:08 am
Here is the data for run_stats_tmp..
SELECT '169519','JOB2','20090903','20090904052513','20090904052528','1','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904052529','20090904052539','1','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169524','JOB7','20090903','20090904052523','20090904052539','1','0','extract_dly_data.sh','server3','B' UNION ALL
SELECT '169522','JOB6','20090903','20090904052513','20090904052522','1','0','extract_dly_data.sh','server3','B' UNION ALL
SELECT '169518','JOB0','20090903','20090904052512','20090904052512','0','0','JOB0','server4','U' UNION ALL
SELECT '169517','JOB1','20090903','20090904052900','20090904052919','2','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169523','JOB9','20090903','20090904052731','20090904052741','1','0','extract_dly_data.sh','server3','B' UNION ALL
SELECT '169525','JOB8','20090903','20090904052539','20090904052731','1','0','extract_dly_data.sh','server3','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904053300','20090904053334','3','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904053700','20090904053804','4','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904054100','20090904054213','5','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904054500','20090904054623','6','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904054900','20090904055015','7','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904055300','20090904055425','8','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169521','JOB5','20090903','20090904055421','20090904055430','1','200','IDEL_doc_recovery.sh','server2','B' UNION ALL
SELECT '169520','JOB4','20090903','20090904052742','20090904055420','1','0','print_docs_check.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904060500','20090904060537','11','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904060100','20090904060124','10','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904070900','20090904071014','27','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904071300','20090904071400','28','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904055700','20090904055835','9','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904071700','20090904071800','29','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904072100','20090904072158','30','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904072500','20090904072557','31','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904072900','20090904073002','32','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904073300','20090904073400','33','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904073700','20090904073846','34','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904074100','20090904074249','35','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904074500','20090904074601','36','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904074900','20090904075008','37','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904075300','20090904075409','38','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904075700','20090904075810','39','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904060900','20090904060934','12','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904061301','20090904061332','13','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904061700','20090904061817','14','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904062100','20090904062211','15','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904062500','20090904062644','16','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904062900','20090904063017','17','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904063300','20090904063435','18','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904063700','20090904063819','19','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904064100','20090904064234','20','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904064500','20090904064618','21','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904064900','20090904065129','22','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904065300','20090904065423','23','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904065700','20090904065757','24','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904070100','20090904070200','25','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169517','JOB1','20090903','20090904070500','20090904070556','26','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169519','JOB2','20090903','20090904085221','20090904085228','2','0','ftp_hiteclabs.sh','server1','B' UNION ALL
SELECT '169526','JOB3','20090903','20090904104516','20090904111435','1','0','print_docs_check.sh','server1','B' UNION ALL
SELECT '169526','JOB3','20090903','20090904121722','20090904125457','2','0','print_docs_check.sh','server1','B' UNION ALL
SELECT '169526','JOB3','20090903','20090904142727','20090904142737','3','8','print_docs_check.sh','server1','B' UNION ALL
SELECT '169526','JOB3','20090903','20090904142949','20090904143006','4','0','print_docs_check.sh','server1','B' UNION ALL
SELECT '169526','JOB3','20090903','20090904143516','20090904143523','5','8','print_docs_check.sh','server1','B' UNION ALL
SELECT '169526','JOB3','20090903','20090904143543','20090904143555','6','0','print_docs_check.sh','server1','B' UNION ALL
SELECT '169526','JOB3','20090903','20090904150113','20090904153208','7','0','print_docs_check.sh','server1','B' UNION ALL
SELECT '169526','JOB3','20090903','20090905055514','20090905063635','8','0','print_docs_check.sh','server1','B' UNION ALL
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply