SQL Server Performance

Xp_CMDShell : Error at Destination for row number 1

Discussion in 'General DBA Questions' started by L0st_Pr0phet, Sep 26, 2007.

  1. L0st_Pr0phet Member

    Hi,
    I have come across a problem that I cant seem to find any info on. I am running a DTS package which is being called by a stored procedure using the XP_CMDSHELL extended pprocedure. problem is when I do this I get the following error:
    DTSRun: Loading...
    DTSRun: Executing...
    DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
    DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
    DTSRun OnStart: DTSStep_DTSDataPumpTask_1
    DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147217911 (80040E09)
    Error string: Error at Destination for Row number 1. Errors encountered so far in this task: 1.
    Error source: DTS Data Pump
    Help file:
    Help context: 0

    Error Detail Records:

    Error: -2147217911 (80040E09); Provider Error: -118490897 (F8EFF8EF)
    Error string: Cannot update. Database or object is read-only.
    Error source: Microsoft JET Database Engine
    Help file:
    Help context: 5003027

    DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147213206 (8004206A)
    Error string: The number of failing rows exceeds the maximum specified.
    Error source: Microsoft Data Transformation Services (DTS) Data Pump
    Help file: sqldts80.hlp
    Help context: 0

    Error Detail Records:

    Error: -2147213206 (8004206A); Provider Error: 0 (0)
    Error string: The number of failing rows exceeds the maximum specified.
    Error source: Microsoft Data Transformation Services (DTS) Data Pump
    Help file: sqldts80.hlp
    Help context: 0


    Error: -2147217911 (80040E09); Provider Error: -118490897 (F8EFF8EF)
    Error string: Cannot update. Database or object is read-only.
    Error source: Microsoft JET Database Engine
    Help file:
    Help context: 5003027

    DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
    DTSRun: Package execution complete.
    NULL
    Now I have ruled out security rights for SQL Server, as this package run fine if directly ran from Enterprise manager. I have ruled out a problem with the created cmdshell command because I have printed it out and ran in directly from the cmd prompt. It seems to only generate this error when I run it through XP_CMDShell. Below is the stored procedure running the DTS Package:

    CREATE Procedure Prc_Run_MC_Export
    @err nvarchar(255) out
    as

    Declare @OfficeID int,
    @ExportDir as nvarchar(225),
    @Tsm as nvarchar(255),
    @OID as int,
    @sql1 as nvarchar(255)

    Declare Office Cursor
    for
    select TSM, OID from office where Active=1 and OID not in(1,35,36,37)
    Open Office
    Fetch Next From Office
    Into @TSM, @OID
    While @@Fetch_Status =0
    Begin
    Set @ExportDir = (select value from configuration where config='MC_Export_Dir') + Rtrim(Ltrim(@TSM)) +'.xls'

    set @sql1 = 'DTSRun /S "'+ ( select value from configuration where config='Server') +
    '" /N "Walkbooks_NPower_MC_Export" /A "Office":"8"="'
    + RTrim(Ltrim(@OID))+'" /A "ExportDir":"8"="'
    + RTrim(LTrim(@ExportDir)) + '" /W "0" /E'

    --Print @sql1
    exec master..xp_cmdshell @sql1
    Fetch Next From Office
    Into @TSM, @OID
    End
    Close office
    Deallocate office
    If @@error <>0
    Begin
    Print 'Error Exporting Files '
    set @err = 'Error Exporting Files '
    Return
    End
    Else
    Begin
    Print 'MC Files Exported'
    set @err = 'MC Files Exported'
    End
    GO
    Any ideas?
  2. satya Moderator

    What is the privilege for the SQLAGent user account on the SQL server?
  3. L0st_Pr0phet Member

    hi satya,
    Its running as a domain account, local admin to the server, with full read write privileges to the location I am writing to.
    stew
  4. L0st_Pr0phet Member

    Ok I have just ran the stored procedure using the xp_cmdshell but so it was writing locally to the SQL Server. This worked fine. It seems that the issue is when I use Xp_cmdshell to run the DTS package when it is writing to a remote file server, even though the SQLAgent and SQLserver both are using a domain account with full read write access to the folder in which its trying to write too!
    [:S]
    I am just going to give the SQL server account local admin on the file server see if that resolves the issue!
  5. satya Moderator

    That is what I meant to asking the SQL Server service account on all the servers that are involved inthis DTS action.
    As it runs using SQLAgent account, it should have proper privileges on local and remote servers.
  6. L0st_Pr0phet Member

    FYI, Just incase anyone else has this issue; I found that this issue was due to security rights not propagating down properly. A couple of reboots( in this case 3) for SQL server and the file server finally resolved the issue. And we managed to just have the SQLAgent and service accounts with just read write access to the folders was enough no need to go down route of putting account onto the file server!I expect this is a issue to do with are infrastructure more than anything else.

Share This Page