Sql 2000 DTS timeout?

Question

Sql 2000 DTS timeout?

WilburSmith

Hall of Fame

Points: 3225
More actions
October 11, 2006 at 4:24 am

#115125

Hi,
on of my customers have a few OLAP cubes and DTS packages to refresh them. The packages run OK when run from Enterprise Manager. They also run quite well when started from T-SQL procedure but not always. The DTS runs take over one hour. Could there be some timeout issue? If so, how can I affect the timeout?
CREATE Proc pOLAP_Full @DTS_PackageName varchar(100)
AS
DECLARE @DTS_Command VARCHAR(200). @Error INTEGER
SET @DTS_Command = 'DTSRun /S Oladwsql3 /E /N "' + @DTS_PackageName + '"'
EXEC @Error = master..xp_cmdshell @DTS_Command
IF @Error <> 0
RAISERROR ('Virhe kuutioiden päivityksessä!', 16, 1)

Regards, Ville

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

I'm thinking that someone has done an update in a transaction with no commit from Query Analyzer... locks up the world sometimes. Use sp_lock and sp_who2 to find out who's doing it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 2

Can you add logging or messaging to DTS to see where it is getting stuck and if and when it is getting stuck run sp_who and sp_lock to find any issues.

Like Jeff said if one of you steps have Transactions and it is not commited then subsequent steps that query the tables affected by the transactions will keep spinning for ever.

WilburSmith Hall of Fame Points: 3225 More actions · Answer 3

Hi,

I think I found the reason. There was a message in the DTS logs that stated some key error and that the log could not be written. Path not found. Didn't tell what path though.

I navigated through the Cubes and found that customer had added a file/path to key error file using his local disk under his own account ...... argh

Thank you for your efforts, Ville

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Perfect case of too many cooks spoiling the broth Thanks for the feed back on what you found, Ville... definitely something to keep an eye out for when working with a customer... unauthorized changes

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)