xp_sendmail driving me mad in SQL 2000 !

[itsmarkdavies]

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.

 

[RickCole]

Have you looked on the SQL Server from where the query is run from rather then the client machine.

Rick.

 

[itsmarkdavies]

YES, I`ve searched the Hard Drives on both Client and Server.

 

[RickCole]

Hmmm might be a quirk with SQL Server 2000. I only run SQL Server 7.0 at the moment so can't re-create what you’re trying to do.

All I can offer you is that I run the following from a query analyser window on a client machine and it puts the file 'Test File' into C:\WINNT\system32 of my SQL Server machine.


exec master.dbo.xp_sendmail
@recipients = 'mail user',
@Message = 'Test file',
@Query = 'Print "test"',
@attachments = 'Test FIle.CSV',
@Attach_Results = 'True',
@Message = '',
@Subject = 'test',
@No_Header = 'True',
@Width = 500,
@Separator = ','


Rick.

 

[itsmarkdavies]

Tried your code thanks Rick, but it doesn`t put the file anywhere atall on my SQL Server 2000 system.

 

[RickCole]

Must be a 2000 quirk or bug.

Just a thought but how about you exchange server!!

Sorry I can't be of more help. Just in case it gets overlooked due to having a response try a new post tomorrow.

Rick.