SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Writing text files as Unicode


Writing text files as Unicode

Author
Message
sqldriver
sqldriver
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2168 Visits: 2536
Hi,

I have this stored procedure (attached), that writes a semi colon delimited text file with column headers. It's pretty handy, but it writes files in ANSI, and I need them in Unicode. Is there a way to edit any part of this procedure to write them as Unicode?

Is it even possible, in general?

Thanks
Attachments
writefilewithheader.txt (19 views, 2.00 KB)
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39344 Visits: 14411
It saddens me a bit that the code is accessing the command prompt and the file system from within T-SQL, especially as part of an applicaiton design, but from your other thread I know the situation and know you are locked into this design. That said, if you ever have the chance to rewrite things SSIS would be my preference to accomplish what is being done.

That aside, if you swap out the -c switch for a -w switch in your bcp command I think you will get where you want to go.

bcp Utility - SQL Server 2005

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
sqldriver
sqldriver
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2168 Visits: 2536
opc.three (4/12/2013)
It saddens me a bit that the code is accessing the command prompt and the file system from within T-SQL, especially as part of an applicaiton design, but from your other thread I know the situation and know you are locked into this design. That said, if you ever have the chance to rewrite things SSIS would be my preference to accomplish what is being done.

That aside, if you swap out the -c switch for a -w switch in your bcp command I think you will get where you want to go.

bcp Utility - SQL Server 2005


Thanks. I was just reading a bit about that.

I know, Google. But do you have a favorite resource for how to do things like that with SSIS? I tried reading the series of articles on here about automating file drop folders to import, but couldn't do much with it...

I don't want to waste your time; I don't have access to a lot of server-side set up. It's kind of a situation where if I can do it and get it set up and use it and it works, it's fine, but getting other people to change things is difficult (sys admins, etc.). No one wants to 'waste' their time making changes for me to test things.

EDIT: That didn't work so well:

http://i.imgur.com/Nv3ij9i.png
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39344 Visits: 14411
Hmmm, -w works for me. Please check the command line you are issuing to xp_cmdshell.

You can try this simple command line as a proof-of-concept.

bcp "select top 10 object_id, name from master.sys.tables;" queryout "C:\1.txt" -t";" -T -w


Re: SSIS, I would recommend anything from Brian Knight. You are on 2005 so here is a good book:

Professional SQL Server 2005 Integration Services. I am actually reading the reprise of the same book geared towards 2012.

Even though you are on 2005 I think it's worth learning 2012. Most of the concepts are the same so will still be backward compatible into 2005 development. Here is a good training book to go along with the textbook-style book above:

Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
sqldriver
sqldriver
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2168 Visits: 2536
I'm running these:


set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+ @filePath + @file_name+'" -w -t";" -T '''
exec(@sql)

set @sql='exec master..xp_cmdshell ''bcp " select ' + @columns1 + ' from '+@db_name+'..'+@TableName+' where statusflag = 0 and reps >= '+ @StartRep + ' and reps <= ' + @EndRep + ' " queryout "' + @data_file+'" -w -t";" -T '''
--print @sql
exec(@sql)



When I run your code, I get this error:


Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'queryout'.


I'm going to check those books out, for sure.

Thanks
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39344 Visits: 14411
Hmm, I tested my query on a 2005 instance and it worked fine:



PS And to confirm, the file looks good:



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Attachments
xp.jpg (143 views, 44.00 KB)
file.jpg (147 views, 19.00 KB)
sqldriver
sqldriver
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2168 Visits: 2536
Yeah, I boneheaded that one. It works now, and the file output is correct.

So, I took it and applied it to the table I want to write as a file, and it works fine there as well. I guess it's something else in the process making it go woogy. Maybe it's when the files get combined?


set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> '+@filepath+@file_name+''''


Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39344 Visits: 14411
By default TYPE is not going to preserve Unicode data for you and I do not know of a way to setup the environment or call it where it will.

You could try building a COPY command:

copy File1+File2+FileN DestinationDataFile /Y /B


Example:

copy C:\1.txt+C:\2.txt+C:\3.txt C:\DataFile.txt /Y /B


In your code the loop is appending to the file for each iteration using TYPE. In using COPY you'll want to change it so the loop just builds the command with all the files and pluses, and then just make one call to xp_cmdshell after the loop is done to append all the files together.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
sqldriver
sqldriver
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2168 Visits: 2536
opc.three (4/12/2013)
By default TYPE is not going to preserve Unicode data for you and I do not know of a way to setup the environment or call it where it will.

You could try building a COPY command:

copy File1+File2+FileN DestinationDataFile /Y /B


Example:

copy C:\1.txt+C:\2.txt+C:\3.txt C:\DataFile.txt /Y /B


In your code the loop is appending to the file for each iteration using TYPE. In using COPY you'll want to change it so the loop just builds the command with all the files and pluses, and then just make one call to xp_cmdshell after the loop is done to append all the files together.



Does this make sense to you?


set @sql= 'exec master..xp_cmdshell ''COPY \\server\SurveyComputing\Sample\SentToSPSS\'+@data_file+' + \\server\SurveyComputing\Sample\SentToSPSS\'+@file_name+''' \\server\SurveyComputing\Sample\SentToSPSS\'+''@file_name'+''_copied' + '/Y /B''''


Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39344 Visits: 14411
SET @sql = 'EXEC master.sys.xp_cmdshell ' +
'''COPY \\server\SurveyComputing\Sample\SentToSPSS\' + @data_file +
'+\\server\SurveyComputing\Sample\SentToSPSS\' + @file_name +
' \\server\SurveyComputing\Sample\SentToSPSS\' + @file_name + '_copied /Y /B''';



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search