March 4, 2005 at 7:19 am
Hello, I'm a fairly novice DTS Package designer that has been forced into maintaining something a bit over my head and need some help. I understand how to build and execute packages, but I am having a problem successfully scheduling them.
Here's my set-up: My web hosting company runs SQL Server 2000 on an NT machine. I have XP Professional, and I am using Enterprise Manager to connect to their database. I have MSDE2000 on my local machine.
I have no problem seeing or editing packages that I make. When I execute them manually, they work. When I schedule a job, however, it fails (I don't know how to get the error message generated from a failed job, so I can't tell why it failed). However, when the server administrators or my former programmers schedule jobs using this exact same package it works.
I think the source of my problem has to do with my access to the database or setup of Enterprise Manager. When I look at the jobs queue, I can see only the jobs that I have created. I can NOT see any existing jobs that have been made by other people, when my administrator tells me that I should be able to see everything.
Any thoughts? I've been trying to get this answered for five weeks now and will gladly name my child after the person who helps me solve this.
Also, do you know of any resources concentrating on set-up of Enterprise Manager, or concentrating on DTS Job Properties. I have found nada on these subjects. Thanks!
March 4, 2005 at 7:25 am
Matthew,
It certainly sounds like a permissions issue to me. A good site for you to check out on DTS would be http://www.sqldts.com/
Good luck,
Darrell
March 4, 2005 at 9:30 am
Matthew,
As Darrell says, the failure of your scheduled DTS packages sounds like a permissions issue. When you schedule a DTS package, it run in the security context of the login used for SQL Server Agent, not your security context. It may be that the package is trying to access a file that the login doesn't have permissions to.
To see the error generated by the failed job in Enterprise Manager, find it in Management -> SQL Server Agent -> Jobs, right-click on the job and select View Job History. Check the "Show step details box" and you should see the error that caused the job to fail.
I assume you're not a sysadmin for the SQL Server since you can only see the jobs you own. You could ask your administrator to add you to the Targetserversrole role in MSDB. That should allow you to see all jobs regardless of owner.
Hope this helps,
Greg
Greg
March 4, 2005 at 2:00 pm
Thank you DSP and Greg. I will pass this along to my administrator. They tell me that I have full SQL rights, but I'm assuming that this doesn't necessarily mean I'm sysadmin...
March 7, 2005 at 9:38 am
Right. In this case, Sysadmin is a server-level role in SQL Server.
Greg
Greg
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply