Powershell - 带有属性的变量的 SQL 查询结果 [英] Powershell - SQL query result to variable with properties

问题描述

为什么 SQLCMD 的存储输出只有 Length 属性而不是 column names?.是否无法使用其属性存储 sqlcmd 输出?

Why the stored output of SQLCMD has only Length property instead of column names?. Is it not possible to store sqlcmd output with its properties?

Invoke-sqlcmd 正确存储它但 Invoke-SQLcmd 需要更长的时间来处理所以我试图让它与 SQLcmd 一起工作，因为这个方法将是一部分计划每分钟运行一次的不同脚本，每小时运行一次等，

Invoke-sqlcmd stores it correctly but Invoke-SQLcmd takes a bit longer to process so I'm trying to make it work with SQLcmd as this method will be part of different scripts that are scheduled to run every minute, once ever hour etc.,

知道这是否可行或有什么问题吗?

Any idea if this is possible or what the issue is?

存储输出并回显 $var:

Store output and echo $var:

     PS C:> $var=(SQLCMD -S 'x.x.x.x' -U 'user' -P 'password' -i "C:\query.sql" -W -m 1) 

     PS C:> $var
     job_id name
     ------ ----
     12345-aaaa-1234-5678-000000000000000 Clear DB entries
     12345-bbbb-1234-5678-000000000000000 TempLog DB

Echo $var[0,1,2] 不显示属性名称.

Echo $var[0,1,2] which doesn't show property names.

     PS C:> $var[0]
     job_id name
     PS C:> $var[1]
     ------ ----
     PS C:> $var[2]
     12345-aaaa-1234-5678-000000000000000 Clear DB entries

显示 $var 属性

     PS C:> $var | select *
     Length
     ------
     11
     53

显示 $var 类型

     PS C:> $var.GetType()

     IsPublic IsSerial Name                                     BaseType
     -------- -------- ----                                     --------
     True     True     Object[]                                 System.Array

推荐答案

$var=(SQLCMD -S 'x.x.x.x' -U 'user' -P 'password' -i "C:\query.sql" -W -m 1) 

您正在调用 sqlcmd.exe，它不知道 .Net 对象是什么，更不用说如何将它们传递给 PowerShell.就 PowerShell 而言，该命令输出字符串.您需要自己将字符串转换为对象.

You're calling sqlcmd.exe, which has no concept of what .Net objects are let alone how to pass them to PowerShell. As far as PowerShell is concerned, that command outputs strings. You will need to convert the strings to objects yourself.

如果你必须使用sqlcmd.exe，我会建议这样的:

If you have to use sqlcmd.exe, I would suggest something like this:

$Delimiter = "`t"
$var = SQLCMD -S 'x.x.x.x' -U 'user' -P 'password' -i "C:\query.sql" -W -m 1 -s $Delimiter |
    ConvertFrom-Csv -Delimiter $Delimiter |
    Select-Object -Skip 1

我使用制表符作为字段分隔符.如果您的数据包含制表符，则需要不同的分隔符.如果您的数据包含双引号，您也可能会遇到问题.Select-Object -Skip 1 是跳过 sqlcmd 总是在标题下方创建的下划线行.

I'm using tab as the field separator. If your data contains tabs, you'll need a different separator. You could also run into problems if your data contains double quotes. The Select-Object -Skip 1 is to skip the underline row that sqlcmd always creates below the header.

另请注意，您应该在 sqlcmd 上使用 -w 参数来防止任何不正确的包装.还要注意空值总是作为文字字符串输出NULL.

Also be aware that you should use the -w parameter on sqlcmd to prevent any incorrect wrapping. Also beware that null values are always output as a literal string NULL.

也就是说，我可能仍会坚持使用 Invoke-Sqlcmd.它更不容易出错，而且更容易预测.如果我真的需要性能，我可能会使用直接的 .Net 方法或 SSIS.

That said, I would still probably stick with Invoke-Sqlcmd. It's much less error prone and much more predictable. If I really needed performance, I'd probably use direct .Net methods or SSIS.

这篇关于Powershell - 带有属性的变量的 SQL 查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！