DTS error

Hello, I have been trying to solve this problem for a couple of weeks
now with no progress. I created a package that works when it runs
from my computer but when it runs as a scheduled job, it fails. I
found references to this quite a few times and the responses have
always pointed to permissions, but I am a System Administrator. My
role is Sysadmin. I changed the Windows/NT owner of the job to sa and
that didn't work either. I made sure that the account that runs from
SQL Server has Sysadmin permissions.

The problem I think has to do with the AS400 connection that I make to
pull data out of the AS400 to put into SQL Server 2000. In the
Connection of the package there is a place for User and Password. I
had the person who definitely has all Rumba permissions put his
username and password in there and it came back with the same error.
The Error message is below. I am sure at a loss. Does anyone have
any ideas? I would really appreciate anything. Could this have
anything to do with MDAC?

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error
string: [Wall Data][RUMBA Data Access][RUMBA95]Communication link
failure. APPC Primary RC = Parameter_Check; Secondary RC =
Bad_LU_Alias. Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail
Records: Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: [Wall Data][RUMBA Data Access][RUMBA95]Communication
link failure. APPC Primary RC = Parameter_Check; Secondary RC =
Bad_LU_Alias. Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete.
Process Exit Code 1. The step failed.phantomtoe@.yahoo.com (Rowan) wrote in message news:<4bbf8d70.0404061235.5e1e3334@.posting.google.com>...
> Hello, I have been trying to solve this problem for a couple of weeks
> now with no progress. I created a package that works when it runs
> from my computer but when it runs as a scheduled job, it fails. I
> found references to this quite a few times and the responses have
> always pointed to permissions, but I am a System Administrator. My
> role is Sysadmin. I changed the Windows/NT owner of the job to sa and
> that didn't work either. I made sure that the account that runs from
> SQL Server has Sysadmin permissions.
> The problem I think has to do with the AS400 connection that I make to
> pull data out of the AS400 to put into SQL Server 2000. In the
> Connection of the package there is a place for User and Password. I
> had the person who definitely has all Rumba permissions put his
> username and password in there and it came back with the same error.
> The Error message is below. I am sure at a loss. Does anyone have
> any ideas? I would really appreciate anything. Could this have
> anything to do with MDAC?
> DTSRun: Loading... DTSRun: Executing... DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 DTSRun OnError:
> DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error
> string: [Wall Data][RUMBA Data Access][RUMBA95]Communication link
> failure. APPC Primary RC = Parameter_Check; Secondary RC =
> Bad_LU_Alias. Error source: Microsoft OLE DB Provider for ODBC
> Drivers Help file: Help context: 0 Error Detail
> Records: Error: -2147467259 (80004005); Provider Error: 0 (0)
> Error string: [Wall Data][RUMBA Data Access][RUMBA95]Communication
> link failure. APPC Primary RC = Parameter_Check; Secondary RC =
> Bad_LU_Alias. Error source: Microsoft OLE DB Provider for ODBC
> Drivers Help file: Help context: 0 DTSRun OnFinish:
> DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete.
> Process Exit Code 1. The step failed.

As a complete guess, does the Rumba driver use information stored in
your profile? I recall that the IBM Client Access driver used to do
this - AS/400 connections were defined and stored in each user's
profile, so when a task was scheduled, it failed, because the profile
wasn't available.

Simon|||What's your DSN setup look like (for iSeries connection)?

phantomtoe@.yahoo.com (Rowan) wrote in message news:<4bbf8d70.0404061235.5e1e3334@.posting.google.com>...
> Hello, I have been trying to solve this problem for a couple of weeks
> now with no progress. I created a package that works when it runs
> from my computer but when it runs as a scheduled job, it fails. I
> found references to this quite a few times and the responses have
> always pointed to permissions, but I am a System Administrator. My
> role is Sysadmin. I changed the Windows/NT owner of the job to sa and
> that didn't work either. I made sure that the account that runs from
> SQL Server has Sysadmin permissions.
> The problem I think has to do with the AS400 connection that I make to
> pull data out of the AS400 to put into SQL Server 2000. In the
> Connection of the package there is a place for User and Password. I
> had the person who definitely has all Rumba permissions put his
> username and password in there and it came back with the same error.
> The Error message is below. I am sure at a loss. Does anyone have
> any ideas? I would really appreciate anything. Could this have
> anything to do with MDAC?
> DTSRun: Loading... DTSRun: Executing... DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 DTSRun OnError:
> DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error
> string: [Wall Data][RUMBA Data Access][RUMBA95]Communication link
> failure. APPC Primary RC = Parameter_Check; Secondary RC =
> Bad_LU_Alias. Error source: Microsoft OLE DB Provider for ODBC
> Drivers Help file: Help context: 0 Error Detail
> Records: Error: -2147467259 (80004005); Provider Error: 0 (0)
> Error string: [Wall Data][RUMBA Data Access][RUMBA95]Communication
> link failure. APPC Primary RC = Parameter_Check; Secondary RC =
> Bad_LU_Alias. Error source: Microsoft OLE DB Provider for ODBC
> Drivers Help file: Help context: 0 DTSRun OnFinish:
> DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete.
> Process Exit Code 1. The step failed.|||We are using a NetManage ODBC for AS400/Rumba with TCP/IP enabled. We
have it look directly at the library where we are trying to get the
data from. The connection has been made on the SQL Server box in
control panel. Is that what you mean?

I have literally tried everything I can think of. I am very
frustrated.

arrowcomputer@.bigfoot.com (Terence) wrote in message news:<440c28e5.0404070344.663f9d39@.posting.google.com>...

> What's your DSN setup look like (for iSeries connection)?
>
> phantomtoe@.yahoo.com (Rowan) wrote in message news:<4bbf8d70.0404061235.5e1e3334@.posting.google.com>...
> > Hello, I have been trying to solve this problem for a couple of weeks
> > now with no progress. I created a package that works when it runs
> > from my computer but when it runs as a scheduled job, it fails. I
> > found references to this quite a few times and the responses have
> > always pointed to permissions, but I am a System Administrator. My
> > role is Sysadmin. I changed the Windows/NT owner of the job to sa and
> > that didn't work either. I made sure that the account that runs from
> > SQL Server has Sysadmin permissions.
> > The problem I think has to do with the AS400 connection that I make to
> > pull data out of the AS400 to put into SQL Server 2000. In the
> > Connection of the package there is a place for User and Password. I
> > had the person who definitely has all Rumba permissions put his
> > username and password in there and it came back with the same error.
> > The Error message is below. I am sure at a loss. Does anyone have
> > any ideas? I would really appreciate anything. Could this have
> > anything to do with MDAC?
> > DTSRun: Loading... DTSRun: Executing... DTSRun OnStart:
> > DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
> > DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> > DTSStep_DTSDataPumpTask_1 DTSRun OnError:
> > DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error
> > string: [Wall Data][RUMBA Data Access][RUMBA95]Communication link
> > failure. APPC Primary RC = Parameter_Check; Secondary RC =
> > Bad_LU_Alias. Error source: Microsoft OLE DB Provider for ODBC
> > Drivers Help file: Help context: 0 Error Detail
> > Records: Error: -2147467259 (80004005); Provider Error: 0 (0)
> > Error string: [Wall Data][RUMBA Data Access][RUMBA95]Communication
> > link failure. APPC Primary RC = Parameter_Check; Secondary RC =
> > Bad_LU_Alias. Error source: Microsoft OLE DB Provider for ODBC
> > Drivers Help file: Help context: 0 DTSRun OnFinish:
> > DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete.
> > Process Exit Code 1. The step failed.|||I found a reference to this when searching. It may be of help to you.

http://support.microsoft.com/?kbid=269074

I have a similar problem in that my DTS package runs for me (with sa
privileges) but I can't get it to run as a scheduled task. I've not
found the solution yet, but if I do, I'll post something here in case
it is of use.

Ryan|||Thanks. I had read through that article a few times. I appreciate
your help and would love to hear your solution if you find one.
Today, I stopped all the services, changed the sql server account to
System and rebooted hoping that would solve my problem. It did solve
another problem I was having with a catalog failing to populate, but
it did not fix my package problem.

Something I left out is that my scheduled backups run just fine. So I
am wondering, if the problem is with SQL Agent permissions...wouldn't
the scheduled backups not run either? If that is the case, then is it
possible that the connection I am making to the AS400 is somehow not
getting the Username and password I specified in the connection
properties? If that could be the problem, does anybody have any
suggestions on how I might test that or fix it? I can't think of
anything else to try. I am out of ideas.

ryanofford@.hotmail.com (Ryan) wrote in message news:<7802b79d.0404080146.518ebe9d@.posting.google.com>...
> I found a reference to this when searching. It may be of help to you.
> http://support.microsoft.com/?kbid=269074
> I have a similar problem in that my DTS package runs for me (with sa
> privileges) but I can't get it to run as a scheduled task. I've not
> found the solution yet, but if I do, I'll post something here in case
> it is of use.
> Ryan|||Well, I finally got it to work today. After reading everything I
could get my hands on I put the right peices together. I had to
first, give the domain user, in our case, domain\Administrator
sysadmin rights in SQL Server. I figured that one out a while ago.
Then I had to go to the Domain Security, User Rights Assigns in Active
Directory. I modified the administrator user rights assignments to
allow it to Act as part of the operating System, Increase Quotas,
Replace a proces level token, Log on as a batch job, and Log on as a
service. To make sure everything was in sync, I stopped all services,
made sure SQL Server's account was that of the
domain\Administrator...made sure that the SQL Server Agent had the
same account, rebooted SQL Server, which restarted all the services,
and voila...my scheduled jobs will now run. Thanks to all who tried
to help. I knew it had to do with permissions, but darned if I could
figure out what to do about it. Maybe this will help someone else.

phantomtoe@.yahoo.com (Rowan) wrote in message news:<4bbf8d70.0404081147.67ec4d5d@.posting.google.com>...
> Thanks. I had read through that article a few times. I appreciate
> your help and would love to hear your solution if you find one.
> Today, I stopped all the services, changed the sql server account to
> System and rebooted hoping that would solve my problem. It did solve
> another problem I was having with a catalog failing to populate, but
> it did not fix my package problem.
> Something I left out is that my scheduled backups run just fine. So I
> am wondering, if the problem is with SQL Agent permissions...wouldn't
> the scheduled backups not run either? If that is the case, then is it
> possible that the connection I am making to the AS400 is somehow not
> getting the Username and password I specified in the connection
> properties? If that could be the problem, does anybody have any
> suggestions on how I might test that or fix it? I can't think of
> anything else to try. I am out of ideas.
> ryanofford@.hotmail.com (Ryan) wrote in message news:<7802b79d.0404080146.518ebe9d@.posting.google.com>...
> > I found a reference to this when searching. It may be of help to you.
> > http://support.microsoft.com/?kbid=269074
> > I have a similar problem in that my DTS package runs for me (with sa
> > privileges) but I can't get it to run as a scheduled task. I've not
> > found the solution yet, but if I do, I'll post something here in case
> > it is of use.
> > Ryansql

DTS and process task - doesnt wait my called exe application finish completely

Hi,

I need to execute a DTS that have a couple of steps and one of them is a process task that simply call an exe file i made that will send an email to warn the user.

What happens here is that the process task executes my exe file but it doesn't wait for it to compete and fires the next task after and finally closes.

There is anyway to make a "while statament" to wait until my exe application finishes?

Any idea?

Thanks in advance,
Tiago TeixeiraI Don't think there is a way to reply to SQL that an executable that it started has now stopped running.

Shame your not using the SQL email service but your prob doing other things as well.

Do you have any contriol over the .exe source code ?

Idea 1. it could possibly create a file in Dos then delete it as a last action before ending. TSQL would then loop (possibly with a while loop) to check if the file still exists notifying you that the .exe has done it's job.

GW|||Why not connect your steps with "On success" workflow/execution paths?
Also, why are you calling another application to send an e-mail when the DTS package designer can send e-mails for you? :D|||I Don't think there is a way to reply to SQL that an executable that it started has now stopped running.

Shame your not using the SQL email service but your prob doing other things as well.

Do you have any contriol over the .exe source code ?

Idea 1. it could possibly create a file in Dos then delete it as a last action before ending. TSQL would then loop (possibly with a while loop) to check if the file still exists notifying you that the .exe has done it's job.

GW

Hi,

Thanks for the help.

I don't use SQL email service because i don't have a MAPI server available to do it, and i don't want create it in IIS just for this purpose.
I found a free dll extension around that sends email without a MAPI account but, the problem is that the DTS aim is export data to excel file and after send this excel file by email, the problem is that when i send the email ( using that dll) an error occurs because the excel file still locked by the precedent task, so i created myself an exe file that frees the xls file and send the email using a .NET library instead of the dll i said, but the erro i reported appears.

About your second idea, can you please be more specific or paste a code snippet abou how to do that Loop. I would appreciate very much.

Thanks in adavance,
Tiago Teixeira|||Hi Teix

I was basically just thinking of something like this


DECLARE @.cnt int
WHILE(@.cnt <= 5)
BEGIN
DECLARE @.result int
EXEC @.result =xp_cmdshell'del Myfile.exe'
IF(@.result = 0)
PRINT'Success'
ELSE
PRINT'Failure'
WAITFORDELAY'00:15'
SET @.cnt = @.Cnt +1
END


Not sure how wise this approach is though

Good Luck

GW|||Hi GWilliy,

I found a way to manage it without code, i tried the code snippet you generously wrote but application behaved in the same way as before.

So, i created by hand a schedule job and inspite of make a single DTS package that would make all the stuff i needed i sliced the main job in 3 parts and i added each one sequentially to the Job and after that all the steps were accomplished correctly and i got the stuff working.

Thanks all for the precious help and ideas,
Best regards,
Tiago Teixeira|||Try windows handler dll, I think every exe program has unique windows handler id, use this id in a loop... Just an Idea...|||thanks i'll investigate that later, but from now i think i've the job done.

thanks
Tiago Teixeira|||Poison Ref Try windows handler dll, how would you access this from TSQL ?

may be handy for future ref

Teix - Glad U got sorted

GW

DTC restart shutdown SQL server, why?

SQL 2000 SP4 standard on Windows 2003 Enterprise.

On Friday our DTC service restarted on a couple of servers (we think cause of a config change made by the network guys) but on one server it also shutdown the sql server... We cannot find anything that indicates why. I have looked and cannot find any reason why SQL would be a require DTC to be running expecially since none of the other servers shut down.

The only difference in this one server as compared to the others is it is a publisher for merge replication and the other servers are either a subscriber or publisher of transactional. Would this have anything to do with it?

Also is SQL actually dependant on DTC for any reason that would cause it to be shutdown when DTC is restarted?

A down MSDTC shouldn't cause your sqlserver to stop running. There must be something else in the loop. What can you find in sql logs (i.e. errorlog.*).