wlevy
asked on
DTS produces no data when called from network server
I have created a VB program that executes a several DTS packages on our SQL Server. This works fine when I run the program from my desktop (it takes about a minute to run) but when I run it on a network server, one of the DTS packages does not move any data from the source table to the target. It appears to execute this DTS package (i.e., when I step through the code in VB the DTS.Execute step runs and no errors seem to occur), but instead of running for a minute or so, it returns instantly.
I am running the same program on my desktop and on the server, and the other configuration elements appear to be in sync. I also ran the program from a colleague's desktop and it works fine there. It seems as though the only difference is that I am calling the DTS from a network server instead of from my desktop.
This program was previously working correctly on the server. The only thing that has changed is the IP address of the source data system (DB2 on AS/400). I modified the connection in the DTS package accordingly.
Does anyone have any idea what's going on here? I'm running out of stones to turn over.
Thanks!
- Walter
I am running the same program on my desktop and on the server, and the other configuration elements appear to be in sync. I also ran the program from a colleague's desktop and it works fine there. It seems as though the only difference is that I am calling the DTS from a network server instead of from my desktop.
This program was previously working correctly on the server. The only thing that has changed is the IP address of the source data system (DB2 on AS/400). I modified the connection in the DTS package accordingly.
Does anyone have any idea what's going on here? I'm running out of stones to turn over.
Thanks!
- Walter
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Chek if SQL Server agent has permissions
ASKER
I don't think it's permissions, because I can run the program (successfully executing the DTS) from other computers including my workstation.
Can you tell me specifically what to check in terms of permissions that could be different when I run the program from the netword server rather than from my workstation?
Can you tell me specifically what to check in terms of permissions that could be different when I run the program from the netword server rather than from my workstation?
>>I don't think it's permissions, because I can run the program (successfully executing the DTS) from other computers including my workstation.<<
You are missing the point. When you schedule the DTS Package using SQL Server Agent, you are no longer running the package, but rather it is running using the SQL Server Agent account. That makes all the difference in the world.
>>Can you tell me specifically what to check in terms of permissions that could be different when I run the program from the netword server rather than from my workstation?<<
Again: Check the SQL Server Agent startup account. If it is a "System Account", that's your problem. If it is a specific account make sure it is:
A. A domain account and
B. Has access to the resources you are using in the DTS Package.
You are missing the point. When you schedule the DTS Package using SQL Server Agent, you are no longer running the package, but rather it is running using the SQL Server Agent account. That makes all the difference in the world.
>>Can you tell me specifically what to check in terms of permissions that could be different when I run the program from the netword server rather than from my workstation?<<
Again: Check the SQL Server Agent startup account. If it is a "System Account", that's your problem. If it is a specific account make sure it is:
A. A domain account and
B. Has access to the resources you are using in the DTS Package.
ASKER
I checked the SQL Server Agent (SQLSERVERAGENT) startup account in Services, and on the Log On tab the Local System Account radio button is selected.
This is as it has always been, it was not changed recently. Once again, keep in mind that this application was working perfectly both on workstations and on a network server, until the IP address of the AS400 (from which DTS is supposed to retrieve data) was changed. I modified the DNS on the workstations and on the network server to point to the new IP address. Now it works when run on workstations, but when run on a network server the DTS package that retrieves data does not return any data. Other DTS packages called by my VB program continue to work correctly.
> > "When you schedule the DTS Package using SQL Server Agent, you are no longer running the package, but rather it is running using the SQL Server Agent account. That makes all the difference in the world."
Please excuse my ignorance, but what do you mean "schedule the DTS Package using SQL Server Agent?" I execute the DTS packages from my VB program, like thus:
Call dtsExport.LoadFromSQLServe
dtsExport.Execute
...where strDTSServer and strDTSMainPackageName are variables holding the appropriate values.
I also tried a variation of the LoadFromSQLServer method where I specify a user and password corresponding to a login set up specifically for this project, with appropriate permissions to the database. This didn't help.
This is as it has always been, it was not changed recently. Once again, keep in mind that this application was working perfectly both on workstations and on a network server, until the IP address of the AS400 (from which DTS is supposed to retrieve data) was changed. I modified the DNS on the workstations and on the network server to point to the new IP address. Now it works when run on workstations, but when run on a network server the DTS package that retrieves data does not return any data. Other DTS packages called by my VB program continue to work correctly.
> > "When you schedule the DTS Package using SQL Server Agent, you are no longer running the package, but rather it is running using the SQL Server Agent account. That makes all the difference in the world."
Please excuse my ignorance, but what do you mean "schedule the DTS Package using SQL Server Agent?" I execute the DTS packages from my VB program, like thus:
Call dtsExport.LoadFromSQLServe
dtsExport.Execute
...where strDTSServer and strDTSMainPackageName are variables holding the appropriate values.
I also tried a variation of the LoadFromSQLServer method where I specify a user and password corresponding to a login set up specifically for this project, with appropriate permissions to the database. This didn't help.
>> but when run on a network server the DTS package that retrieves data does not return any data. <<
Is there a firewall blocking it ?
>.Other DTS packages called by my VB program continue to work correctly. <<
Do these also get data from the AS400 (with changed IP address)
Is there a firewall blocking it ?
>.Other DTS packages called by my VB program continue to work correctly. <<
Do these also get data from the AS400 (with changed IP address)
ASKER
The only DTS that does not work correctly (i.e. does not retrieve data from the source) is the one that is supposed to get data from the AS400. I have asked our network people if there is a firewall that might be blocking data transfers between the network server and the AS400; waiting for a reply.
I *can* ping the AS400 from the network server, but there still might be something blocking data transfers.
I wonder if there's some way to execute the DTS packages on our SQL Server box instead of the computer where the VB program that calls the DTS is running.
I *can* ping the AS400 from the network server, but there still might be something blocking data transfers.
I wonder if there's some way to execute the DTS packages on our SQL Server box instead of the computer where the VB program that calls the DTS is running.