March 23, 2006 at 3:49 pm
Hi All need help...
I have a package created in sql server 2000 DTS. I have scheduled the package to run once a week at wednesday night. The package fails on the task when the data from production server is moved to development server; However, when I run the package manually it runs fine... It only happens when it tries to run as a scheduled job??? Could you please tell me what can be the problem?
Thanks in advance!!!
March 23, 2006 at 3:59 pm
Go into the SQL Agent>>Jobs view and look at the job properties. Who is set as the owner? Whomever it is needs to have permissions on your development server, so you will either have to have a SQL Server account that is synced up on both server, or you can use a domain account.
March 23, 2006 at 4:02 pm
That I do not think that should be the problem as when I run it manually it runs fine.
March 23, 2006 at 4:03 pm
The account is fine.
March 23, 2006 at 4:04 pm
>>It only happens when it tries to run as a scheduled job??? Could you please tell me what can be the problem?
The scheduled job doesn't run as "you". Therefore it doesn't have your permission set.
Check the login that SQL Agent is running under and ensure that account has the necessary privileges.
March 23, 2006 at 4:10 pm
Checked it... permission is not the issue here. Thanks for the help guys.
March 24, 2006 at 4:53 am
What are the specofics of the error message?
Thanks Jeet
March 24, 2006 at 9:50 am
...also, what specific tasks are in the package and which task fails?
Greg
Greg
March 24, 2006 at 12:24 pm
Sometimes the Data source or Server in the connection could causes this kind of problem if the DTS package was created from the Enterprise Manager on your desktop, and you scheduled the job on the server.
March 24, 2006 at 12:30 pm
When you run DTS manually from your machine you are using your security. When it is scheduled you are running SQL Agent security. If you are importing text files or doing anything with drive paths with letters, then both the server and your local mappings must be the same.
March 24, 2006 at 1:05 pm
Several issues here.. and for others who may have the same problem, here's my two cents..
IF you're going to have a DTS package that copies a text file to some network resource, i.e., not on the local server, then you need to have your network admin create a Domain Login for you and then give that Domain account read/write permissions on that target folder or where ever you need to copy to.
THEN, on your SqlServer box, you need to set SQL Server Agent to run NOT as a System Account (default?) but to run as the Domain account you just had created by your Network Admin (you'll need to know the login password for this account). I also run Sql Server under the same Domain Account.
This should fix your problem UNLESS in your DTS package, you are using Mapped Drives (J:\SqlTargetFolder\Cust.txt or the like). The DTS will work fine when you run it manually from Enterprise Mangler, but it will NOT run as a Job. You will need to change your DTS package to use something like: \\TargetServerName\SqlTargetFolder\Cust.txt
I guess there could be other issues, depending on whether or not you are running in Windows Authentication or Mixed Mode, but this should address most of the issues raised so far. Check out this article on Microsoft: http://support.microsoft.com/default.aspx?scid=kb;en-us;269074
Ideas, comments or snide remarks?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply