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
Solved

DTS timeout problem

Posted on 2002-07-11
8
531 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
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 9

Accepted Solution

by:
miron earned 333 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

792 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