xp_sendmail Column Separator Suppressed

[tlbroadbent]

Question:

Has anyone encountered the following situation? Does anyone know of a fix? Thus far my searches haven't turned up anything.

Situation:

Use xp_sendmail in SQL Server 2000 to send results of a T-SQL query. The result can be sent in the body of the email or as an attachment. If the @separator parameter is not specified or if it is set to a single space, the result set has no spaces between columns. If the parameter is set to another character such comma or vertical bar, the result set in the email has the specified separator. If the parameter is set to two spaces, the result set arrives with two spaces between each column.

Example Query:

DECLARE @sep VARCHAR(2), @msg varchar(256), @sbj varchar(80), @sql varchar(1000)

SELECT
	@sep = SPACE(1),
	@msg = 'The attached file contains a list of databases on ' + @@servername,
	@sbj = @@servername + ' Databases',
	@sql = 'SELECT DBName = CONVERT(VARCHAR(32),[name]), [dbid], [filename] FROM dbo.sysdatabases ORDER BY 1'

EXEC master.dbo.xp_sendmail
	@recipients = 'someone@hotmail.com',
	@subject = @sbj,
	@message = @msg,
	@attach_results='true',
	@dbuse='master',
	@width=1024,
	@ansi_attachment='True',
	@separator = @sep,
	@query = @sql

Result:
[tt]
DBName dbid filename
-----------------------------------------------------------------------------
master 1E:\mssql\DATA\MASTER.MDF
model 3e:\mssql\data\model.mdf
msdb 4e:\mssql\data\msdbdata.mdf[/tt]

Configuration:

SQL Server 2000 Service Pack 4 Build 2196
OS: Windows Server 2003 Service Pack 2 Build 3790

Thanks.


Terry L. Broadbent - DBA

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents. (Nathaniel Borenstein)

 

[JayKusch]

Morning Terry,

Well the only way I could get around it was to convert the DBID to a varchar like:

DECLARE @sep VARCHAR(2), @msg varchar(256), @sbj varchar(80), @sql varchar(1000)

SELECT
    @sep = SPACE(1),
    @msg = 'The attached file contains a list of databases on ' + @@servername,
    @sbj = @@servername + ' Databases',
    @sql = 'SELECT DBName = CONVERT(VARCHAR(32),[name]), CONVERT(VARCHAR(10),[dbid]), [filename] FROM dbo.sysdatabases ORDER BY 1'

EXEC master.dbo.xp_sendmail
    @recipients = 'quabee63@gmail.com',
    @subject = @sbj,
    @message = @msg,
    @attach_results='true',
    @dbuse='master',
    @width=1024,
    @ansi_attachment='True',
    @separator = @sep,
    @query = @sql

Not sure if that helps any.


Thanks

J. Kusch

 

[tlbroadbent]

Jay,

Thanks. I tried the change. It didn't make any difference.

This may be a settings issue because I can now get the correct output from one SQL 2000 server instance but still get suppressed spaces from another instance. I'll keep you informed if I find anything.

Did you encounter the problem of suppressed sspaces when you ran the query before converting dbid to VARCHAR?

Terry L. Broadbent - DBA

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents. (Nathaniel Borenstein)

 

[tlbroadbent]

It appears that the problem is a defect in xp_sendmail. If the @separator parameter is specified as a single space, the space is suppressed in the output. If the parameter is omitted, the result contains a single space column separator.

Way to go Microsoft!

Terry L. Broadbent - DBA

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents. (Nathaniel Borenstein)