Solved

DTS timeout problem

Posted on 2002-07-11
8
526 Views
Last Modified: 2013-11-30
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
0
Comment
Question by:mikef17
  • 5
  • 2
8 Comments
 
LVL 9

Expert Comment

by:miron
Comment Utility
I would try this,

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
0
 
LVL 9

Expert Comment

by:miron
Comment Utility
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
0
 

Author Comment

by:mikef17
Comment Utility
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
0
 
LVL 9

Accepted Solution

by:
miron earned 333 total points
Comment Utility
Hi Mike,

--I have no so much experience with TSQL scripts.
--How can i run this script?

use ISQLW.EXE, go to start menu, click run, type isqlw in the dialog box and press [Enter] key. Paste the script into the edit section and click on the green arrow in the tool bar.

--How can i CALL DTS anew for the first time?
to clarify, in a daily cycles of DTS copy process CALL(ing) DTS anew for the first time means starting first attempt on this day to copy table from SQL Server to Access database. To start DTS from TSQL script you need a command line like this.

execute xp_cmdshell 'dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password'

To learn better on how to use command line utility dtsrun.exe encourage you to read SQL Server On-Line documentation. If for some reason On-Line documentation was not installed with SQL Server on the local disk it can be found at the URL

http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409&frame=true



--How can i check if this script works?
When development is done, it would be really easy, but at this time, it looks like a question is a bit ahead of development process :)

Cheers.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:mikef17
Comment Utility
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)?
0
 
LVL 9

Expert Comment

by:miron
Comment Utility
Hi Mike,

may be some expert has Enterprise installation at home, I work with MSDE, unfortunately it has no Enterprise Manager.

Good luck.
0
 
LVL 9

Expert Comment

by:miron
Comment Utility
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.
0
 

Expert Comment

by:CleanupPing
Comment Utility
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now