December 22, 2010 at 9:38 am
I have a SQL 2000 box here in the UK that has a scheduled DTS package which runs every hour. The DTS package contains an ActiveX task which (amongst over things) calls another DTS Package which is hosted on another SQL 2000 box in Australia.
I call the remote DTS Package with LoadFromSQLServer as follows:
[font="Courier New"] Dim oPkg
SET oPkg = CreateObject("DTS.Package")
oPkg.LoadFromSQLServer myServer,"","",256,"","","",myDTS
oPkg.Execute
SET oPkg = Nothing[/font]
(I have even tried hard-coding in the actual server name in place of "myServer", and likewise with "myDTS")
The strange thing is that this always used to work without any problems prior to December 2010, and now it throws the error most of the time. However, sometimes it does actually work without me changing anything whatsoever.
The fact that it sometimes works (either through the scheduled job or manual running of the UK DTS) kinda rules out the possibility of account access rights.
When it does fail (most of the time), the remote server in Australia is definitely running / available (I get ping response, can remote to the box and run Ent Manager, etc).
So, I'm really baffled as to what could be causing the failures.
Any ideas would be most greatly appreciated.
Cheers
Gary
December 22, 2010 at 6:00 pm
Typically that error comes from the server down, or a network issue. I'm not sure the retry interval for loading and running a package.
Can you instead send a note to the Australian server (maybe an update in a table or sp_startjob) to run th package from there?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 23, 2010 at 2:59 am
Thanks fro the suggestion, but I'm afraid that it is not quite as straight forward as that.
What I didnt mention was that there are other DTS calls in the ActiceX task and they rely on the previous call in order to work.
The ActiveX taskprocess is as follows:
1. Call DTS on UK server which loads and formats data from an external file
2. Call DTS on Australian server to load data from a table that is populated in step 1
3. Call DTS on UK server to load data from a table that is populated in step 1
Step 3 cannot be executed before Step 2, and that is why I cannot see your suggestion being workable.
I also believe that this is more than likely a network issue, although the server always appears to be online (I have an RDC session to the box - even when the job fails).
I think that what I need to do now is introduce some error capture so that at the very least the process will be reset for the next hours execution (currently if it fails then the process is left in a state that stops further executions).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply