Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DTS timeout problem

Posted on 2002-07-11
8
Medium Priority
?
541 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 9

Expert Comment

by:miron
ID: 7151079
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
ID: 7151147
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
ID: 7152149
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 9

Accepted Solution

by:
miron earned 999 total points
ID: 7152859
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
 

Author Comment

by:mikef17
ID: 7153662
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
ID: 7153677
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
ID: 7153712
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
ID: 9280149
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

688 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