Writing utf8 content to the disk file

Question

Writing utf8 content to the disk file

dipali.choudhary

Old Hand

Points: 315
More actions
November 17, 2010 at 5:57 am

#231346

Hello,
I want to do changes in existing stored procedure so that it should write regional content in utf8 fomat to the disk. Currently it is outputting as '?' for all characters. Database table shows proper text.
SP has following code
execute @hr = sp_OAMethod @objTextStream, 'Write', Null, @String
can you please tell me, are there any parameters to sp_OAMethod or any other setting required to do utf8 format write to the disk.
Thanks and Regards
dipali

Viewing 9 posts - 1 through 8 (of 8 total)

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

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 1

Dipali,

Does the workstation where you are opening the file have proper fonts installed? In other words, if you would do a SELECT FieldName FROM MyTable, copy the unicode data and paste it to Notepad, does it come up fine?

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

dipali.choudhary Old Hand Points: 315 More actions · Answer 2

No I don't have the fonts installed on my machine. Actually we insert single item rss xml file into one of the xml type column. Then combine xmls into one based on some criteria and output single RSS file.

I can see proper text when I do select column in database as well as when I see xml file in IE browser before inserting into database. When i try to see output file from the database in browser, I see '????'.

Hope this will clear the problem I am having.

Thanks and Regards

dipali

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 3

Can you post the code (with business specific items omitted) that you are using to write to the file?

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

dipali.choudhary Old Hand Points: 315 More actions · Answer 4

SP has following code for writing to the disk. Aprat from this, it has some error handling code.

Select @FileAndPath=@path--+'\'+@filename

if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'

if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'

, @objTextStream OUT, @FileAndPath,2,True

if @HR=0 Select @objErrorObject=@objTextStream,

@strErrorMessage='writing to the file "'+@FileAndPath+'"'

if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Write', Null, @String

if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'

if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 5

Hello!

Thank-you. I assume that @objTextStream is the value that contains the text to be written to the file.

If yes, is that value Unicode enabled? i.e. is that value NVARCHAR/NTEXT/NCHAR? If not, it should be.

Next, you will need fonts installed on the workstation where the file will be read (else although the file contains proper characters, it will come up as boxes or question marks).

I might be slow in responding in the next couple of hours, FYI. Request you to allow for the delay.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

dipali.choudhary Old Hand Points: 315 More actions · Answer 6

Thanks so much for your replies. I will try with NVARCHAR.

Regards

dipali

dipali.choudhary Old Hand Points: 315 More actions · Answer 7

dipali.choudhary

Old Hand

Points: 315

November 18, 2010 at 4:20 am

#1252380

It worked:-)

Thanks

dipali

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 8

That is good to know, Dipali. Have a good evening!

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins