Solved

DTS timeout problem

Posted on 2002-07-11
8
533 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Syntax issue with my Where Clause SQL 2012 20 38
Powershell help for creating accounts 283 49
SQL Throw Error 7 34
SQL 2012 Instance Problem 3 55
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

749 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