Post reply

sql server job fails But Not the DTS

  • March 7, 2006 at 4:17 pm

    #67341

    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

    #625336

    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

    #625339

    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

    #625404

    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

    #625405

    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.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • March 8, 2006 at 7:49 am

    #625410

    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 

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • March 8, 2006 at 9:02 am

    #625440

    "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

    #625674

    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

    #625823

    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