Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unanswered: SQL Send Mail Failure

    I have written a DTS procedure to export some data to excel then email it to key people in the company. Each part works fine independanly of each other but once I set the workflow to wait until the Excel has been created the SQL Mail function fails. I have tried putting another step inbetween the Excel Creation and the Mail function to see if it would release the locked file but it still doesn't allow me the access the file. If the workflow is removed I can click on the send mail immediately after the Excel function finishes and it will send with no problems. If anyone has a solution I would appreciate it.

    Thanks
    Kevin E.

  2. #2
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    My guess is that it is DTSRun.exe (the program that actually executes the DTS package) that is locking the file, not the particular step. This means that executeion of the package would need to stop after creation in order to get hold of it for mailing.

    Maybe you can create a seperate package to send the email (?). How to trigger it though...

    How about another step that makes a copy of the file, then mail the copy?

    Or maybe a status table with a trigger. Set the status when done with creation, then end execution. Let the trigger kick off a mail sp that sends the file?

    Early Monday morning... sorry for the rambling.
    -bpd

  3. #3
    Join Date
    Sep 2003
    Posts
    522
    have you tried to replace the mail icon with a task and call xp_sendmail from there?

  4. #4
    Join Date
    Sep 2003
    Posts
    2

    Thanks

    Thanks for the input. I had thought the same thing but I wasn't sure where to begin looking for the lock. I eventually found under Advanced properties of the Excel Object in DTS a place where it allows you to terminate connection on completion. This seemed to fix it. I just thought I'd pass this around jsut in case someone else ran into this problem.

    Kevin E

    Originally posted by bpdWork
    My guess is that it is DTSRun.exe (the program that actually executes the DTS package) that is locking the file, not the particular step. This means that executeion of the package would need to stop after creation in order to get hold of it for mailing.

    Maybe you can create a seperate package to send the email (?). How to trigger it though...

    How about another step that makes a copy of the file, then mail the copy?

    Or maybe a status table with a trigger. Set the status when done with creation, then end execution. Let the trigger kick off a mail sp that sends the file?

    Early Monday morning... sorry for the rambling.

Posting Permissions

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