Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2002
    Location
    sheffield, uk
    Posts
    14

    Question Unanswered: xp_sendmail driving me mad

    Has anyone else noticed that if you create a non-existing file as an attachment using xp_sendmail in SQL Server 2000, it does not create a copy of that file on the Hard Drive, nor does it format the attached file sensibly if you attach it as a .csv file ?

    I am using the procedure below :-

    CREATE PROCEDURE mailtest AS

    declare @sql varchar (255)

    SELECT PERSONID, FORENAME, SURNAME INTO ##TEMP FROM PERSON

    SELECT @sql='SELECT * FROM ##TEMP'

    IF @@ROWCOUNT > 0

    begin

    exec master.dbo.xp_sendmail
    @recipients = 'itsmarkdavies@hotmail.com',
    @Message = 'Test file',
    @Query = @sql,
    @attachments = 'C:\MARKTEST.CSV',
    @Attach_Results = 'True',
    @Message = '',
    @Subject = 'test',
    @No_Header = 'True',
    @Width = 500,
    @Separator = ','

    end
    DROP TABLE ##TEMP
    GO

    In the example above, the file MARKTEST.CSV does not currently exist, but the procedure should create it, put it in the root of C:\ and e:mail it, as it did when it ran under SQL Server 6.5. However, under 2000 it now doesn`t put a copy on the Hard Drive and it formats the .csv file in a very odd manner.
    Mark Davies
    itsmarkdavies@hotmail.com

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    I seem to recall that there was a bug in 6.5 in regard to the message and multiple attachments - which you could get round but did not act in the way it was expected to.
    In v7 (or was it a service pack) the behaviour changed but still wasn't as documented.
    Doesn't surprise me that the behaviour is still odd in this area.

Posting Permissions

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