xp_sendmail attach output problems

• MikeMcDonald

  Say Hey Kid

  Points: 663

  More actions

  November 4, 2003 at 8:32 pm

  #83183

  I've been asked to supply data to a 3rd party. Requirement given was for a ".csv" format file, with headings in the first row, and all data to be enclosed in quotes.

  (Note - that brings up another problem. Enclose a string of digits in quotes and Excel will assume that the field is numeric. There seems to be no way of telling Excel that it is actually a text field.)

  After much heartache, tearing out of hair, etc, I've managed to get the file into the format required - BUT WITH ONE PROBLEM - there is "trailer" record which I can't get rid of. The trailer record is a "tab" followed by lots of spaces. Does anyone know how to get rid of it?

  There has been a fair bit of interest expressed in the problems with attachments produced by xp_sendmail - mainly the "white space" that you can't eliminate.

  My "work around", while not being very elegant (that's an understatement!) may spark a few ideas with others who are having similar problems.

  The approach I've used is to generate the report as a single TEXT field.

  Any help/comments would be appreciated.

  Thanks

  Michael

  .. and here's the code excerpts ..

  CREATE PROCEDURE sp_xxx_test3 AS

  declare@quotechar(1),

  @commachar(1),

  @datanvarchar(1000)

  declare@time_stamp_achar(20)

  select@quote=char(34),@comma=','

  ... set time_stamp

  delete from yyyKTemp.dbo.mike_xxx_test

  -- Write the column headings

  insert into yyyKTemp.dbo.mike_xxx_test

  select

  @quote + 'Customer' +@quote +@comma

  +@quote + 'Order_Number' +@quote +@comma

  +@quote + 'Reference_Number' +@quote +@comma

  +@quote + 'Shipping_Line_Booking_Number' +@quote +@comma

  +@quote + 'Depot' +@quote +@comma

  +@quote + 'Shipping_Line' +@quote +@comma

  +@quote + 'Vessel' +@quote +@comma

  etc, etc

  -- Cursor to get the actual data

  declarec1 cursor for

  select

  char(10) +

  @quote + 'yyy' +@quote +@comma

  +@quote + rtrim(cust_po) +@quote +@comma

  +@quote + ord_key +@quote +@comma

  +@quote + rtrim(booking) +@quote +@comma

  +@quote +@quote +@comma

  +@quote +@quote +@comma

  +@quote + rtrim(vessel_name) +@quote +@comma

  +@quote + rtrim(text) +@quote +@comma

  +@quote + rtrim(port_name)+' '+replace(convert(char(11),dt_cutoff,106),' ','-') +@quote +@comma

  etc, etc

  -- Get the data and write it to the text file (append to the end)

  open c1

  fetch c1 into @data

  while @@fetch_status = 0

  begin

  DECLARE @ptrval binary(16)

  SELECT @ptrval = TEXTPTR(rest) from yyyKTemp.dbo.mike_xxx_test

  updatetext yyyKTemp.dbo.mike_xxx_test.rest @ptrval null 0

  @data

  fetch c1 into @data

  end

  close c1

  deallocate c1

  select rest from yyyKTemp.dbo.mike_xxx_test

  GO

  And here's the code in "Agent" that calls it up

  declare @dlen int,

  @filename nvarchar(50),

  @stamp char(19)

  set@dlen = (select datalength(rest) from yyyKTemp.dbo.mike_xxx_test)

  set@stamp = convert(char(19),getdate(),120)

  set@filename = 'ShipmentSchedule_'

  +substring(@stamp,1,4)+substring(@stamp,6,2)+substring(@stamp,9,2)

  +substring(@stamp,12,2)+substring(@stamp,15,2)+substring(@stamp,18,2)

  +'.csv'

  exec master.dbo.xp_sendmail

  @recipients='<recipient deleted>',

  @copy_recipients='<various names deleted>',

  @message='Shipping schedule attached',

  @query='exec rep_yyyk1.dbo.sp_xxx_test3',

  @attachments=@filename,

  @ansi_attachment=True,

  @attach_results=TRUE,

  @separator='',

  @subject='ShipmentSchedule',

  @width=@dlen,

  @no_header=TRUE

  Note .. @separator='' is to stop a preceding space from being entered!!

• Ferguson

  SSCertifiable

  Points: 5837

  More actions

  November 7, 2003 at 5:27 pm

  #481340

  I was fighting the trailing blank problem just the other night, so I appreciate your example. I have no idea on the trailing line.

  There's also always DTS as an option, it's not much harder and a bit easier to control output formats.

• MikeMcDonald

  Say Hey Kid

  Points: 663

  More actions

  November 9, 2003 at 1:30 am

  #481360

  DTS would have probably been a bit more awkward for the requirements - csv with quotes around headings as well as data - plus having to have a timestamp as part of the name.

  As it has turned out, the trailing blanks haven't presented a problem to the 3rd party - but I'd still like to know how to get of them!