mikef17
asked on
DTS timeout problem
i have a DTS that runs every night.That DTS delete a table
in Access 2000 DB and copy that table from SQL server 2000 to the Access 2000 DB.That DTS makes same operation on 5 tables.
The PROBLEM:
it's about one time in month DTS can't finish and works
some days.
Is possible set some timeout for DTS and after that check
the status(if DTS was completed successfull or not) and run it one more time if DTS wasn't finished successfull .
Thanks in advance
in Access 2000 DB and copy that table from SQL server 2000 to the Access 2000 DB.That DTS makes same operation on 5 tables.
The PROBLEM:
it's about one time in month DTS can't finish and works
some days.
Is possible set some timeout for DTS and after that check
the status(if DTS was completed successfull or not) and run it one more time if DTS wasn't finished successfull .
Thanks in advance
Ok,
looks like script needs some corrections
declare
@currenttime int
declare
@DTS_begun int,
@previous_DTS_run_id int,
@retry_id int
select
@DTS_begun = DTS_begun,
@previous_DTS_run_id = previous_DTS_run_id,
@retry_id = retry_id
FROM
status_table WHERE record_id = IDENT_CURRENT( 'status_table' )
select
@currenttime =
DATEPART( hh, GETDATE() ) * 10000 +
DATEPART( mi, GETDATE() ) * 100 +
DATEPART( ss, GETDATE() )
select @currenttime
IF( @currenttime > 230000 and @currenttime < 230200 )
BEGIN
INSERT INTO status_table (
DTS_begun,
retry_id
)
SELECT
1,
0
-- CALL DTS anew for the first time
END
IF( @DTS_begun IS NOT NULL and @previous_DTS_run_id IS NULL AND @retry_id < 5 )
BEGIN
UPDATE status_table
SET retry_id = retry_id + 1
where
record_id = IDENT_CURRENT( 'status_table' )
-- CALL DTS untill the @previous_DTS_run_id is updated or @retry_id is set to 5
END
--Cheers
looks like script needs some corrections
declare
@currenttime int
declare
@DTS_begun int,
@previous_DTS_run_id int,
@retry_id int
select
@DTS_begun = DTS_begun,
@previous_DTS_run_id = previous_DTS_run_id,
@retry_id = retry_id
FROM
status_table WHERE record_id = IDENT_CURRENT( 'status_table' )
select
@currenttime =
DATEPART( hh, GETDATE() ) * 10000 +
DATEPART( mi, GETDATE() ) * 100 +
DATEPART( ss, GETDATE() )
select @currenttime
IF( @currenttime > 230000 and @currenttime < 230200 )
BEGIN
INSERT INTO status_table (
DTS_begun,
retry_id
)
SELECT
1,
0
-- CALL DTS anew for the first time
END
IF( @DTS_begun IS NOT NULL and @previous_DTS_run_id IS NULL AND @retry_id < 5 )
BEGIN
UPDATE status_table
SET retry_id = retry_id + 1
where
record_id = IDENT_CURRENT( 'status_table' )
-- CALL DTS untill the @previous_DTS_run_id is updated or @retry_id is set to 5
END
--Cheers
ASKER
Hello Miron!
I have no so much expirience with TSQL scripts.
How can i runthis script?
How can i CALL DTS anew for the first time?
How can i check if this script works?
Mike
I have no so much expirience with TSQL scripts.
How can i runthis script?
How can i CALL DTS anew for the first time?
How can i check if this script works?
Mike
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,Miron!
Because i have no so much experience with TSQL scripts,i look for some "simple" solution.
Is it possible to set a timeout for DTS in local package (in Enterprise manager)?
Because i have no so much experience with TSQL scripts,i look for some "simple" solution.
Is it possible to set a timeout for DTS in local package (in Enterprise manager)?
Hi Mike,
may be some expert has Enterprise installation at home, I work with MSDE, unfortunately it has no Enterprise Manager.
Good luck.
may be some expert has Enterprise installation at home, I work with MSDE, unfortunately it has no Enterprise Manager.
Good luck.
Let me correct myself,
EM also supplied with Standard Edition, Developer Edition, Small Builness Edition, and Personal Edition of SQL Server. Still, neither one installed at home.
Cheers.
EM also supplied with Standard Edition, Developer Edition, Small Builness Edition, and Personal Edition of SQL Server. Still, neither one installed at home.
Cheers.
mikef17:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
create local to SQL Server where DTS is run status table.
with
record_id int identity
DTS_begun int
previous_DTS_run_id int
retry_id int
date_of_operation datetime default( GETDATE() )
just before start of copying table in the same DTS
package insert arbitrary value into DTS_begun - it
will create new identity in the field record_id by default.
Once DTS is finished with success insert into previous_DTS_run_id
current identity value - 1
call DTS using SQL Agent, SQL Agent will either wait until DTS is done with success,
or free up for next scheduled call when DTS bails out on error.
Setup schedule to call again every 1 minute - it is very cheap in terms of millicycles,
trust me.
Schedule SQL Agent to call DTS using SQL script, and in this script test the some fields. At a certain hour mark, for example, 11:00PM call DTS for the first try
declare
@currenttime int
declare
@DTS_begun int,
@previous_DTS_run_id int,
@retry_id int
select
@DTS_begun = DTS_begun,
@previous_DTS_run_id = previous_DTS_run_id
@retry_id = retry_id
FROM
status_table WHERE record_id = IDENT_CURRENT( 'status_table' )
select
@currenttime =
DATEPART( hh, GETDATE() ) * 10000 +
DATEPART( mi, GETDATE() ) * 100 +
DATEPART( ss, GETDATE() )
IF( @currenttime > 230000 and @currenttime < 230200 )
BEGIN
UPDATE status_table
SET retry_id = 1
where
record_id = IDENT_CURRENT( 'status_table' )
-- CALL DTS anew for the first time
END
then continue to call few more times.
IF( @DTS_begun IS NOT NULL and @previous_DTS_run_id IS NULL AND @retry_id < 5 )
BEGIN
UPDATE status_table
SET retry_id = retry_id + 1
where
record_id = IDENT_CURRENT( 'status_table' )
-- CALL DTS untill the @previous_DTS_run_id is updated or @retry_id is set to 5
END
Cheers