March 7, 2006 at 4:17 pm
Hi I have a DTS in which we do some transformations. The owner of the DTS is sa and when I run the DTS under sa user account, the DTS runs successfully. I ran this DTS from EM using sa account.
But when I schedule the job and run the job it fails. I read some articles that the job should run under the same account as the server agent. The job is running under the same account as the SQLServerAgent is running. The job owner is also same as the user under which the SQL SERVER AGENT and SQL SERVER runs. This user account has SYSadministrator role and DBOwner role.
Would any one explains what I am doing wrong with this?
Thanks
March 8, 2006 at 2:13 am
Can you say what the error is, I have found the most common problem is connections rather than accounts but it depends on several things.
March 8, 2006 at 2:36 am
Another common problem when scheduling DTS jobs is file permissions. A good way to debug these is to log on using the SQL Agent account and run your DTS from QA - this should help you discover what files and folders you need access to.
However, as juliekenny says, unless you include the error messages you are getting the best that any of us can do to help is guess.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 8, 2006 at 7:38 am
Actually, I am not accessing any files the server. I just copy from one table to another by SP. Also all these jobs are working fine before. As far as I know, only change is the servername changed.
Here is the error message:
Executed as user: \SQLServerAgentAdmin. 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: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 17 (11) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
March 8, 2006 at 7:41 am
Again with no errors I can only guess but another problem I'd seen from time to time was when someone did a lot of changing of service user accounts and such, the accoutn SqlServer Agent was running as couldn't make sense fo the Path environmental var. So instead of DTSRun.exe [very long GUI package name] it had to be specified as "C:\programs\...\dtsrun.exe" [very long GUI package name].
-Luke.
March 8, 2006 at 7:49 am
From the error you are receiving it sounds as if your connection strings are not correct.
You mentioned you changed server names? Do you mean you are using the same db/DTS on a different server, ie you restored to another server. When you did this did you verify the new settigns in the connetion objects?
Try retesting you Connections
March 8, 2006 at 9:02 am
"Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. "
I agree with Luke. Open the package and check the SQL Server connections. My guess is you didn't change the server name in the connections.
Greg
Greg
March 9, 2006 at 7:22 am
What account is your SQLServer Agent running under? Check to make sure that account has access to the database and has execute permissions on the stored proc...
Chris
March 9, 2006 at 3:59 pm
I ran into similar job problems when moving system DBs from one server to another (long story). The same problem happens if a server is renamed. Have you done anything similar? Can you edit the job? If it won't let you, then you problem may be related to a renamed server as well. The solution for us was to rename the server back to its original name, drop the job, rename the server back to the name we wanted to use, then rebuild the job.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply