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 theMicrosoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
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
Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.
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.
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.
For default instance, navigate to HKLM\Software\Microsoft\MSSQLSERVER. For named instance, navigate to HKLM\Software\Microsoft SQL Server\InstanceNameHere.
Create a new key called SQLMAPI.
Leave the class for the key blank.
Create a new value called ansi_attachment.
Set the type of the value to REG_DWORD.
Set the value to 1 for ANSI attachments, 0 for Unicode attachments.