Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 546
  • Last Modified:

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
0
mikef17
Asked:
mikef17
  • 5
  • 2
1 Solution
 
mironCommented:
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
 
mironCommented:
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
 
mikef17Author Commented:
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
mironCommented:
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
 
mikef17Author Commented:
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
 
mironCommented:
Hi Mike,

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

Good luck.
0
 
mironCommented:
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
 
CleanupPingCommented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now