Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Writing text files as Unicode Expand / Collapse
Author
Message
Posted Friday, April 12, 2013 8:51 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:31 PM
Points: 492, Visits: 2,035
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



  Post Attachments 
writefilewithheader.txt (5 views, 2.27 KB)
Post #1441752
Posted Friday, April 12, 2013 9:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,139, Visits: 12,762
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
Post #1441777
Posted Friday, April 12, 2013 10:11 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:31 PM
Points: 492, Visits: 2,035
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


Post #1441786
Posted Friday, April 12, 2013 10:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,139, Visits: 12,762
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
Post #1441796
Posted Friday, April 12, 2013 11:13 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:31 PM
Points: 492, Visits: 2,035
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
Post #1441802
Posted Friday, April 12, 2013 11:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,139, Visits: 12,762
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


  Post Attachments 
xp.jpg (58 views, 44.30 KB)
file.jpg (55 views, 19.31 KB)
Post #1441807
Posted Friday, April 12, 2013 11:41 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:31 PM
Points: 492, Visits: 2,035
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+''''

Post #1441824
Posted Friday, April 12, 2013 12:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,139, Visits: 12,762
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
Post #1441836
Posted Friday, April 12, 2013 12:57 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:31 PM
Points: 492, Visits: 2,035
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''''

Post #1441857
Posted Friday, April 12, 2013 2:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,139, Visits: 12,762
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
Post #1441922
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse