Knowledge Base

FIX: xp_sendmail Generates UNICODE CSV File Format

Article ID: 280720

Article Last Modified on 10/17/2003

APPLIES TO

Microsoft SQL Server 2000 Standard Edition

This article was previously published under Q280720

BUG #: 351089 (SHILOH_BUGS)
BUG #: 351894 (SHILOH_BUGS)

SYMPTOMS

If you execute the xp_sendmail command with a file attachment in SQL Server 2000 and then try to open the .csv file with Microsoft Excel, Microsoft Word, or any other application (except Notepad), you may receive a warning that the file format is unrecognizable.

CAUSE

SQL Server 2000 generates files in xp_sendmail with a UNICODE format. In SQL Server 7.0, the files are generated with an ANSI format.

RESOLUTION

To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

WORKAROUND

The English version of this fix should have the following file attributes or later:

   Date           Version     Size     File name      Platform
   -------------------------------------------------------------
   Nov 21, 2000   8.00.0224   165 KB   Sqlmap70.dll   x86

STATUS

Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.

MORE INFORMATION

Steps to Reproduce Behavior

Run the following command:

execute master..xp_sendmail
    @recipients = 'user1',
        @message = 'Testing',
        @query = 'select * from sysobjects',
        @width = 256,
        @subject = 'testing',
        @attach_results = 'true',
        @no_header = 'false',
        @separator = ',',
        @attachments = 'test.csv'

After you receive the test.csv file, try to open it in Excel or Word; you will receive an unrecognizable file format warning.
Try the same steps in SQL Server 7.0 and note that no formatting is required.

After either the hotfix or the service pack is applied, the following two things are implemented:

An optional parameter, @ansi_attachment, is created for xp_sendmail. This parameter can be True (indicating an ANSI attachment file), False (indicating a UNICODE file), or not present. When this parameter is omitted, a registry value, ansi_attachment, is read.
The following new registry key is read by the Sqlmap70.dll to decide whether to send the attachments as ANSI or Unicode.

For default instance:

HKLM\Software\Microsoft\MSSQLSERVER\SQLMAPI\ansi_attachment

For named instance:

HKLM\Software\Microsoft\Microsoft SQL Server\InstanceNameHere\SQLMAPI\ansi_attachment
If the above registry key is missing, follow the steps below to create the registry key.WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.
1. Start Regedt32.exe.
2. For default instance, navigate to HKLM\Software\Microsoft\MSSQLSERVER.
  For named instance, navigate to HKLM\Software\Microsoft SQL Server\InstanceNameHere.
3. Create a new key called SQLMAPI.
4. Leave the class for the key blank.
5. Create a new value called ansi_attachment.
6. Set the type of the value to REG_DWORD.
7. Set the value to 1 for ANSI attachments, 0 for Unicode attachments.

Keywords: kbbug kbfix kbqfe kbsqlserv2000sp1fix KB280720

Microsoft KB Archive/280720

Contents