PowerShell调用SQL报错：无法绑定‘System.Data.DataRow’标识符

原脚本 #

$prioryear = "select convert(char,(SELECT DATEADD(yy, -1,DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)))), 23) as prioryear"

--Above runs OK

Declaring prioryear as varchar in the query

$SQLquery = "
declare @prioryear varchar(50) = $prioryear_day
declare @sql nvarchar(1000)
set @sql = 'select bp3.sedol, b.benchmark_name, b.description ,bp3.price as '' ' + @prioryear + ' ''
FROM
dbo.ds_benchmark_prices bp1
left join dbo.ds_benchmark_prices bp2 on bp1.sedol= bp2.sedol
left join dbo.ds_benchmark_prices bp3 on bp1.sedol= bp3.sedol
where convert(char,bp3.price_date,23)=$prioryear_day -- prior year 
order by b.sedol'
exec (@sql)
"
#Invokve statements are below - Running both below Invoke causes error. If I comment out second invoke statements, it runs OK. Seems an issue with the way I declared prioryear_day????

$prioryear_day=Invoke-Sqlcmd -Query $prioryear -ServerInstance "UKWAR01APWPD\WHERESCAPE" -Database RJISDWH

$SqlConnection=Invoke-Sqlcmd -Query $SQLquery -ServerInstance "UKWAR01APWPD\WHERESCAPE" -Database RJISDWH | Export-CSV $outputFile -NoTypeInformation

报错信息 #

Invoke-Sqlcmd : The multi-part identifier "System.Data.DataRow" could not be bound.
The multi-part identifier "System.Data.DataRow" could not be bound.
Msg 4104, Level 16, State 1, Procedure , Line 3.
At C:\Users\kbraxton\Desktop\Untitled8.ps1:56 char:16
... lConnection=Invoke-Sqlcmd -Query $SQLquery -ServerInstance "UKWAR01AP ...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

问题原因 #

Invoke-Sqlcmd返回的是DataRow对象，不是直接的字符串值。直接把$prioryear_day插入SQL语句中，PowerShell会将这个对象解析成System.Data.DataRow字符串，导致SQL无法识别该标识符，触发报错。

另外还有两个隐性问题：

1. 动态SQL里引用了表b但未在FROM/JOIN中关联，执行时会触发额外报错
2. 日期字符串作为查询条件时未加单引号，SQL会将其当作标识符而非字符串值

修复后的脚本 #

# 获取上一年度日期的字符串值
$prioryear = "select convert(char,(SELECT DATEADD(yy, -1,DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)))), 23) as prioryear"
$prioryear_day = (Invoke-Sqlcmd -Query $prioryear -ServerInstance "UKWAR01APWPD\WHERESCAPE" -Database RJISDWH).prioryear

# 构造SQL查询（修复表关联、字符串引号、变量引用问题）
$SQLquery = @"
declare @prioryear varchar(50) = '$prioryear_day'
declare @sql nvarchar(1000)
set @sql = N'select bp3.sedol, b.benchmark_name, b.description, bp3.price as ''' + @prioryear + '''
FROM dbo.ds_benchmark_prices bp3
JOIN dbo.ds_benchmarks b ON bp3.sedol = b.sedol  -- 新增关联表b的逻辑，可按需调整JOIN类型
where convert(char,bp3.price_date,23) = @prioryear
order by b.sedol'
exec sp_executesql @sql, N'@prioryear varchar(50)', @prioryear = @prioryear
"@

# 执行查询并导出CSV
Invoke-Sqlcmd -Query $SQLquery -ServerInstance "UKWAR01APWPD\WHERESCAPE" -Database RJISDWH | Export-CSV $outputFile -NoTypeInformation

关键修复点 #

1. 提取字符串值：通过.prioryear从DataRow对象中提取实际的日期字符串，避免对象直接插入SQL
2. 添加字符串引号：在SQL中给$prioryear_day包裹单引号，确保SQL将其识别为字符串值
3. 修复表关联：补充dbo.ds_benchmarks b的JOIN逻辑（原脚本用了b表但未关联，可按需调整JOIN类型）
4. 使用参数化动态SQL：改用sp_executesql传递参数，避免SQL注入风险，同时让语法更规范

内容的提问来源于stack exchange，提问作者David