Umlauts in my TSQL script are turning to "?" when called from a powershell script

Question

Umlauts in my TSQL script are turning to "?" when called from a powershell script

Ian_McCann

SSCertifiable

Points: 5163
More actions
January 21, 2015 at 1:58 am

#397908

I am using a powershell script to manage releases over multiple servers & databases.
when the tsql runs, umlauts are changing to question marks.
an example:
the powershell
clear-host
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') | Out-Null
$filepath = [Microsoft.VisualBasic.Interaction]::InputBox("Enter a Path name", "Release Tool 1.0", "C:\Temp\mysqlfile.sql")
ForEach($item in (Get-ChildItem $filepath)) {
Write-Output $item.FullName
}
invoke-sqlcmd -inputfile $filepath -serverinstance "<ServerName>" -database "tempdb"
the tsql script
use tempdb
go
if exists (select name from sys.objects where object_id = object_id('PowershellTest'))
begin
drop table dbo.PowershellTest
end
go
create table dbo.PowershellTest (StringValue varchar(64))
go
insert into dbo.PowershellTest(StringValue)
select 'Gültig'
go
the results
select StringValue from dbo.PowershellTest
StringValue
G?ltig
Any help would be greatly appreciated
Thanks
Ian

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

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

Phil Parkin SSC Guru Points: 246963 More actions · Answer 1

I presume that you've checked that the T-SQL script works fine if you run it in SSMS? (Works fine on my system, so I am guessing yes.)

You could perhaps try running it with SQLCMD to see whether it's definitely a Powershell-related thing.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 2

Thanks, good call .... but

EXEC xp_cmdshell 'sqlcmd -S "<ServerName>" -i "C:\Temp\mysqlfile.sql"'

results

select StringValue from dbo.PowershellTest

StringValue

Gültig

running sqlcmd direct from the cmd window gives the same results

Phil Parkin SSC Guru Points: 246963 More actions · Answer 3

Well, that narrowed the problem down well.

I think that the solution may be found in this article.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 4

Hmmmm

I've fixed the script by reading the contents of the tsql file and then invoking sqlcmd -Query instead of invoke sqlcmd -inputfile

clear-host

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') | Out-Null

$filepath = [Microsoft.VisualBasic.Interaction]::InputBox("Enter a Path name", "Release Tool 1.0", "C:\Temp\mysqlfile.sql")

ForEach($item in (Get-ChildItem $filepath)) {

Write-Output $item.FullName

}

$Query = Get-Content $filepath

invoke-sqlcmd -Query "$Query" -serverinstance "<ServerName>" -database "tempdb"

Why that is, I don't know

Phil Parkin SSC Guru Points: 246963 More actions · Answer 5

Out of interest, did you also try saving the input file with UTF-8 as suggested in the link?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 6

kind of 😉

openning the script in notepad++ and saving it as utf8 encoded and then reading it back with -Encoded UFT8 did work,

( there is always a but)

but

It took away from the automated nature of the task.

The scripts I am running are created in Management studio and then saved into FTS, as far as I can tell they are all ansi.

Thanks very much for your input

Phil Parkin SSC Guru Points: 246963 More actions · Answer 7

Ian_McCann (1/21/2015)
kind of 😉
openning the script in notepad++ and saving it as utf8 encoded and then reading it back with -Encoded UFT8 did work,
( there is always a but)
but
It took away from the automated nature of the task.
The scripts I am running are created in Management studio and then saved into FTS, as far as I can tell they are all ansi.
Thanks very much for your input

Np. In my original link, there is a section which explains how to force an existing file's encoding to UTF-8. So automation should still be possible this way.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin