Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004

    Unanswered: Checking job status using SQL-DMO

    I have a question I hope someone can help me with.

    My situation:
    I have a single-step job in SQL Server, which runs a stored procedure, A. This stored procedure invokes another stored procedure, B. In B, one of the statements is a 'BACKUP DATABASE' command, and a database is backed up to a file.

    The job is started by an application. Once started, the application then uses the SQL-DMO property CurrentRunStatus to periodically check the status of the job. When the property returns the value SQLDMOJobExecution_Idle (indicating the job has completed), the application code then continues processing, and attempts to access the .dat file produced by the 'BACKUP DATABASE' command.

    My problem:
    On occasion, the application will hit the problem where either the backup file cannot be located, or the file is still being locked by another process (Error=The process cannot access the file because it is being used by another process).

    Is anyone able to shed some light on this?

    I assume that the job will only return a completed status after:
    (a) both A and B have completed execution, and
    (b) the BACKUP operation has completed

    Is it possible that even though SQL Server indicates the job has finished, that the BACKUP operation still hasn't completely ended?

    Thanks for any help,

  2. #2
    Join Date
    Jun 2004
    Doesn't anyone have any ideas?

    Should I be posting this elsewhere instead?

  3. #3
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    Will VB/VBA do?


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts