FIX: xp_sendmail Generates UNICODE CSV File Format

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.

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.

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:

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

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

Steps to Reproduce Behavior

1. 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'
   					

2. After you receive the test.csv file, try to open it in Excel or Word; you will receive an unrecognizable file format warning.
3. 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.