在 PowerShell 中对 Invoke-Sqlcmd 的 Unicode 支持 [英] Unicode support for Invoke-Sqlcmd in PowerShell
问题描述
PowerShell sqlps 模块为从 PowerShell 中访问 SQL Server 及其Invoke-Sqlcmd cmdlet 是其执行文字查询或 SQL 脚本文件(类似于非 PowerShell
我从这个简单的脚本文件(名为 unicode.sql)开始:
创建表#customers( [IdCust] int,[名字] nvarchar(25),[姓氏] nvarchar(25));INSERT INTO #customers VALUES (4, N'Hans', N'Grüßner')SELECT * FROM #customers;删除表#customers;
请注意,姓氏包含一些典型的 Unicode 字符,例如,您可能会在德语名称中找到这些字符.
<小时>结果
SQL Server Management Studio:在输出到网格或文本时正确呈现,例如
IdCust FirstName Surname—————————————————————————————————-----------4 汉斯·格鲁斯纳
sqlcmd 实用程序:无论是从 DOS shell 还是 PowerShell 运行,都能正确呈现,例如
C:\>sqlcmd -S .\SQLEXPRESS -i unicode.sqlIdCust FirstName 姓氏—————————————————————————————————-----------4 汉斯·格鲁斯纳
PowerShell Invoke-Sqlcmd: 渲染不正确(无论是作为如下所示的文本输出还是通过管道传输到 Out-Gridview):
PS>调用-Sqlcmd -Server .\sqlexpress -InputFile unicode.sqlIdCust FirstName 姓氏------ --------- -------4 Hans Gr??ner
Invoke-Sqlcmd 的 MSDN 文档只是顺便提到了 Unicode,比较了它的命令行开关与 sqlcmd 的那些,表明虽然后者具有用于输出 Unicode 的 -u
选项(在我上面的实验中甚至不需要),但 Invoke-Sqlcmd 具有 没有 等效项范围.
通过广泛的网络搜索,我一无所获,但我仍然希望这在某种程度上是我的用户错误.在 PowerShell 中使用 Invoke-Sqlcmd 检索输入数据时,有没有办法保留输入数据?
更新 我在另一台机器上测试了 invoke-sqlcmd 并且它可以工作,所以可能其余部分不适用...
更新 2 通过 -Query 参数 invoke-sqlcmd 执行时似乎只有 -inputfile 有问题.
据我所知,这与转换字符串时的 ADO.NET DataTable 有关.当您使用 ExecuteScaler 或 ExecuteReader 时,它可以正常工作.当然,这并不能修复 invoke-sqlcmd,但可以解释原因:
$server = "$env:computername\sql1"$database = "tempdb"$query = @"创建表#customers( [姓氏] nvarchar(25));INSERT INTO #customers VALUES (N'Grüßner')SELECT * FROM #customers;"@$connection=new-object System.Data.SqlClient.SQLConnection$connection.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $server,$database$command=new-object system.Data.SqlClient.SqlCommand($query,$connection)$connection.Open()$command.ExecuteScalar()$connection.Close()
更新 3文件的编码似乎是关键.查看 [System.IO.File]::ReadAllText,MSDN 文档指出它只会检测 UTF-8 或 UTF-32 编码.http://msdn.microsoft.com/en-us/library/ms143369(v=vs.90).aspx
如果我用 UTF-8 保存 .sql 文件,使用 -inputfile 参数就可以了.在 SSMS 中保存 .sql 文件时,您可以选择 UTF-8,但这里有一些 Powershell 代码也可以检查和更改编码.您需要从 http://poshcode.org/2075
获取 Get-FileEncoding.ps1<预><代码>..\Get-FileEncoding.ps1Get-FileEncoding -Path E:\bin\unicode.sql$query = 获取内容 E:\bin\unicode.sql$query= $query -join "`n"$查询|Out-File -FilePath e:\bin\unicode.sql -Encoding UTF8 -forceGet-FileEncoding -Path E:\bin\unicode.sqlThe PowerShell sqlps module provides core support for SQL Server access from within PowerShell and its Invoke-Sqlcmd cmdlet is its main workhorse for executing literal queries or SQL script files (analogous to the non-PowerShell sqlcmd utility). I recently tried some experiments to confirm that Invoke-Sqlcmd handles Unicode and had some surprising results.
I started with this simple script file (named unicode.sql):
CREATE TABLE #customers
( [IdCust] int,
[FirstName] nvarchar(25),
[SurName] nvarchar(25)
);
INSERT INTO #customers VALUES (4, N'Hans', N'Grüßner')
SELECT * FROM #customers;
DROP TABLE #customers;
Note that the surname has some typical Unicode characters one might find in a German name, for example.
Results
SQL Server Management Studio: Renders correctly when output to grid or to text, e.g.
IdCust FirstName Surname
----------- ------------------------- -------------------------
4 Hans Grüßner
sqlcmd utility: Renders correctly whether run from a DOS shell or a PowerShell, e.g.
C:\> sqlcmd -S .\SQLEXPRESS -i unicode.sql
IdCust FirstName Surname
----------- ------------------------- -------------------------
4 Hans Grüßner
PowerShell Invoke-Sqlcmd: Renders incorrectly (whether output as text as shown below or piped into Out-Gridview):
PS> Invoke-Sqlcmd -Server .\sqlexpress -InputFile unicode.sql
IdCust FirstName Surname
------ --------- -------
4 Hans Gr??ner
The MSDN documentation for Invoke-Sqlcmd mentions Unicode only in passing, comparing its command-line switches with those of sqlcmd, showing that while the latter has a -u
option for outputting Unicode (which was not even needed in my experiment above), Invoke-Sqlcmd has no equivalent parameter.
I have found nothing at all regarding this point through extensive web searching but I still hold out hope that this is in some way a user error on my part. Is there a way to preserve the input data when retrieving it with Invoke-Sqlcmd in PowerShell?
Update I tested invoke-sqlcmd on another machine and it works, so maybe the rest of this doesn't apply...
Update 2 Only seems to have issue with -inputfile when executing via -Query parameter invoke-sqlcmd works fine.
From what I can tell this has something to do with ADO.NET DataTable when converting a string. It works fine when you use an ExecuteScaler or ExecuteReader. Of course this doesn't fix invoke-sqlcmd, but does explain why:
$server = "$env:computername\sql1"
$database = "tempdb"
$query = @"
CREATE TABLE #customers
( [SurName] nvarchar(25)
);
INSERT INTO #customers VALUES (N'Grüßner')
SELECT * FROM #customers;
"@
$connection=new-object System.Data.SqlClient.SQLConnection
$connection.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $server,$database
$command=new-object system.Data.SqlClient.SqlCommand($query,$connection)
$connection.Open()
$command.ExecuteScalar()
$connection.Close()
Update 3 The encoding of the file seems to be the key. Looking at [System.IO.File]::ReadAllText, the MSDN doc states it will only detect UTF-8 or UTF-32 encoding. http://msdn.microsoft.com/en-us/library/ms143369(v=vs.90).aspx
If I save the .sql file with UTF-8, using the -inputfile param works. You can choose UTF-8 when saving .sql file in SSMS, but here's some Powershell code to check and change the encoding also. You'll need to grab Get-FileEncoding.ps1 from http://poshcode.org/2075
. .\Get-FileEncoding.ps1
Get-FileEncoding -Path E:\bin\unicode.sql
$query = get-content E:\bin\unicode.sql
$query= $query -join "`n"
$query | Out-File -FilePath e:\bin\unicode.sql -Encoding UTF8 -force
Get-FileEncoding -Path E:\bin\unicode.sql
这篇关于在 PowerShell 中对 Invoke-Sqlcmd 的 Unicode 支持的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!