SQL Server Performance

Error executing DTS through Job

Discussion in 'SQL Server DTS-Related Questions' started by Daveyboy, Apr 23, 2004.

  1. Daveyboy New Member

    Hi,<br /><br />I am very new to all this so please bare with me! I started this job thinking it was nice and simple ... silly me!<br /><br />I want to run a sql query that exports the data to a .txt file on a share on another server. Both servers are Win2k and runnig SQL 7.0.<br /><br />I have set up the DTS package and that works fine until I schedule it as a job, then it fails with the following ...<br /><br />DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: Copy Data from Results to \tchx-wb1URL_LIST_CHANGEurls.txt Step DTSRun OnError: Copy Data from Results to \tchx-wb1URL_LIST_CHANGEurls.txt Step, Error = -2147217887 (80040E21) Error string: Error opening datafile: Logon failure: unknown user name or bad password. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 1326 (52E); Provider Error: 1326 (52E) Error string: Error opening datafile: Logon failure: unknown user name or bad password. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: Copy Data from Results to \tchx-wb1URL_LIST_CHANGEurls.txt Step DTSRun: Package execution complete. Process Exit Code 1. The step failed.<br /><br />Any help greatfully appreciated, I'm very lost here! (Please remember I am new to this so 'simplistic' responses please <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Many thanks.<br /><br />Regards,<br /><br />Dave
  2. satya Moderator

  3. Daveyboy New Member

    forgive me but, is that basically saying that the sqlserver agent has to be started using an NT account that has write permissions to the share i'm trying to write to?

    Thanks.

    Regards,

    Dave
  4. satya Moderator

    Yes it is, all domain user accounts must have permission to:
    Access and change the SQL Server directory or any other directory that involves file sharing by a task.
    Access and change the .mdf, .ndf, and .ldf database files.
    Log on as a service.
    Read and write registry keys.

    If the startup account assigned to the MSSQLServer Service is not a member of the Local Administrators group, or if the BUILTINAdministrators SQL Server login has been removed, you must add the startup account for the MSSQLServer service or the SQLServerAgent service, or both, to the SQL Server system administrators (sysadmin) role. Grant the [DomainNTaccount] user a logon to SQL Server.



    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. Daveyboy New Member

    Thank you Satya, your help is much appreciated. I will endevour to complete this and let you know how I get on. Thanks again.

    Regards,

    Dave
  6. satya Moderator

    If you're unsure about the above mentioned list of privileges then take help of network adminstrator on the basis of network.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. CanadaDBA New Member

    Also have a look at this topic: "Calling a DTS package from within a SP"
  8. satya Moderator

    Farhad

    If the underlying user doesn't have permission to access the table on remote computer then the package itself will fail irrespective of running from a SP or schedule the job.

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. Daveyboy New Member

    Thanks to you all ... I got there in the end [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Regards,<br /><br />Dave
  10. sunil New Member

    Hi
    Iam running a job on SQL Server 2005 which executes an SSIS Package which sources data from SQL Server 2000 and deletes few records. The SQL Agent account for both these instances are different and would remain different based on the decision made. I have given the SQL Agent account that is used to execute this job on SQL Server 2005 required dml privilege to delete the records in SQL Server 2000. but my package fails with Process Exit Code 1. The step failed..
    Please help. I tried to look up to the link provided above but that's not available.

Share This Page