Problem with BULK INSERT And SQL MAIL

  • Hello all,

    Yeah, you heard me.  Or read me, as the case may be.  This is driving me nuts, hope someone can provide some insight.

    I have the following statements in a sproc:

    set @v_SQL = @v_sql + N'BULK INSERT Sometable FROM ''' + @v_ImportFilePath +

          ''' WITH (MAXERRORS = 1, FIELDTERMINATOR = ''\t'')' + char(10) + char(13)

    exec sp_executesql @v_sql

    set @v_error = @@error

    if @v_error <> 0

    BEGIN

     rollback tran

     set @v_Msg = 'Problem with import file [' + @p_FileName + '], error ' + cast(@v_error as varchar(9)) + ' reported!'

     set @v_Msg = @v_Msg + char(10) + char(13)

     if @p_Initial = 'N'

      exec master..xp_sendmail @recipients = @v_RecipientList, @message = @v_Msg, @subject = 'Import Error!'

     else

      exec master..xp_sendmail @recipients = @v_RecipientList, @message = @v_Msg, @subject = 'Import Error!',

                 @attachments = @v_ImportFilePath

    return

    END

    The intent here is that if there's something wrong with the bulk insert, shoot out an email.  Unfortunately, if an error occurs with the bulk insert, the sendmail never happens.  Mail is working fine on our server and I have plenty of other alerts in this sproc, which are sent out ok.  Mail fails right after the bulk insert.  Hard coding the bulk insert statement so as not to use sp_executesql doesn't make a difference, and running the code in SQA only works if there's a GO between the bulk insert and xp_sendmail.

    Anyone see this happen before?  Anyone?  Anyone?

    Vik

  • Try to replace exec sp_executesql @v_sql with exec (@v_sql)

  • Actually, it used to be exec (@v_sql).  I changed it as part of my fruitless debugging session.  Sorry, should've mentioned it.

    Vik

  • So where exactly does it fail and what is the error message?

    Does it get to the xp_sendmail part?

    --------------------
    Colt 45 - the original point and click interface

  • There is no error returned, nothing in the event log or the SQL logs.  Everything works as it should, just that xp_sendmail doesn't.  Couldn't find anything on msdn, will do some more investigation and post results here if they warrant.

    Vik

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply