Write to text file in ASCII not UTF 16

Question

Write to text file in ASCII not UTF 16

jessica.stape

Mr or Mrs. 500

Points: 544
More actions
May 4, 2008 at 11:28 am

#103489

Hi,
I am using OLE automation stored procedures, in particular the sp_OAMethod, to write to a text file using SQL Server 2005. Here is the line of code:
execute @hr = sp_OAMethod @objTextStream, 'Write', Null, @String
@String is a varchar not a nvarchar.
However, the output file is in UTF-16, not ASCII as was desired.
Anyone know how to ensure that my output file is in ASCII? Also, if anyone knows of an environment to display the file in to discern whether it is ASCII or UTF-16, that is welcome too.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

jessica.stape Mr or Mrs. 500 Points: 544 More actions · Answer 1

I think I have discovered an answer:

"...OLE DB is based on COM, and thus all the strings are Unicode BSTRs (UTF-16 on Windows 2000, and UCS-2 on all other operating systems). For SQL Server, the provider is the Microsoft OLE DB Provider for SQL Server (SQLOLEDB). Data is converted to Unicode as needed, using the collation of the actual data."

http://technet.microsoft.com/en-us/library/aa902644(SQL.80).aspx

So, does anyone know a way to get around this or a something I could call in my stored procedure after creating my .txt file to convert it from UTF-16 to ASCII?

Thanks,

Jessica

harrymrh Valued Member Points: 71 More actions · Answer 2

I have the same problem. I tried

EXECUTE sp_OASetProperty @objTextStream, 'Charset', 'utf-8'

EXECUTE sp_OASetProperty @objTextStream, 'Charset', 'windows-1252'

but these did not change the output character set, I don't know if this is on the right path but it seemed to make sense.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Did anyone ever find an answer on this? If not, does anyone know the answer for this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jessica.stape Mr or Mrs. 500 Points: 544 More actions · Answer 4

Hi Jeff,

I couldn't find a way to get this to work, so I used the method described by Will in this post:

http://www.sqlservercentral.com/Forums/Topic450843-23-1.aspx

Jessica

ishirvani SSC Journeyman Points: 87 More actions · Answer 5

use at First Create

Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;

ok