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

French characters into SQL Server Expand / Collapse
Author
Message
Posted Thursday, September 20, 2012 9:14 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 5:34 AM
Points: 439, Visits: 1,014
Hi all,

I use Powershell to deploy DB scripts. In one script file, I have French accented characters.
When I call Invoke-sqlcmd and pass the script to the -inputfile, the French characters get mangled.

In the bad old days, the workaround was to save the files as unicode, and sqlcmd would handle them OK.
How do I go about this in this brave new world?

Thanks!

Paul



Post #1362060
Posted Thursday, September 20, 2012 9:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,140, Visits: 12,763
Cmdlet Out-File has a -Encoding option.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1362097
Posted Monday, September 24, 2012 5:58 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 5:34 AM
Points: 439, Visits: 1,014
Forgive me, maybe it's too early on Monday, but I'm not sure how that helps me?

The source file is fine, it's getting the accents into SQL Server that's problematic.



Post #1363436
Posted Monday, September 24, 2012 8:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,140, Visits: 12,763
Sorry I misunderstood the scenario. Try changing the input file encoding from UTF-16LE (what Microsoft calls "Unicode") to UTF-32 and see if passing that file works. It's a workaround but I think you need it for Invoke-SqlCmd to get the job done. It's an open issue with the file handling in the Cmdlet.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1363534
Posted Tuesday, September 25, 2012 9:05 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 5:34 AM
Points: 439, Visits: 1,014
I was able to Set-Content of the source files to Unicode, and they appeared OK when opened in SSMS.
I had a test version working fine; but when those files were passed to Invoke-Sqlcmd, the accented chars were again mangled.

So I've reverted to using sqlcmd.

Thanks for taking the time to respond.

P



Post #1364109
Posted Tuesday, September 25, 2012 10:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,140, Visits: 12,763
You're welcome. Happy you got it sorted.

As I said though, "Unicode" = UTF-16LE. I was saying to try the input file in UTF-32 format. Something like this would have created it:

"Hello world!" | Out-File -FilePath C:\InputFile.txt -Encoding UTF32


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1364155
Posted Tuesday, April 16, 2013 2:33 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Thursday, October 2, 2014 9:02 AM
Points: 464, Visits: 321
Using Powershell 3, I had the same problem but ended up doing this:

$UnicodeFileName =  $ScriptPath + "\UnicodeFileContent.sql"

$FileContent = Get-Content $FileName

$FileContent | Out-File -FilePath $UnicodeFileName -Encoding Unicode

Invoke-Sqlcmd -ServerInstance $SqlServer -Database $Database -InputFile $UnicodeFileName -AbortOnError -Verbose



___________________________________
I love you but you're standing on my foot.
Post #1443013
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse