Problem with BULK INSERT And SQL MAIL

Question

Post reply

Problem with BULK INSERT And SQL MAIL

vhanda

SSCrazy

Points: 2240
More actions
July 26, 2004 at 7:47 pm

#108754

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

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

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

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

vhanda SSCrazy Points: 2240 More actions · Answer 2

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

Vik

philcart SSC-Forever Points: 47796 More actions · Answer 3

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

vhanda SSCrazy Points: 2240 More actions · Answer 4

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