将一个变量从Powershell传递到保存的SQL脚本
问题描述:
我有一个Powershell函数用于在SQL中运行多个查询并导出为CSV。每个查询都依赖于一个日期变量。有没有办法使用我当前的设置将这个日期变量从Powershell传递到这些SQL脚本(不是存储过程)?任何帮助深表感谢!将一个变量从Powershell传递到保存的SQL脚本
Function Run-Query
{
param([string[]]$queries,[string[]]$sheetnames)
Begin
{
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $Database; User ID = $uid; Password = $pwd;"
Write-host "Connection to database successful."
}#End Begin
Process
{
# Loop through each query
For($i = 0; $i -lt $queries.count; $i++)
{
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
# Use the current index ($i) to get the query
$SqlCmd.CommandText = $queries[$i]
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
# Use the current index ($i) to get the sheetname for the CSV
$DataSet.Tables[0] #| Export-Csv -NoTypeInformation -Path "C:\Users\mbaron\Downloads\$($sheetnames[$i]).csv"
}
}#End Process
End
{
$SqlConnection.Close()
}
}#End function run-query.
答
您可以添加在查询标记在正在使用的数据,然后做一个有关日期,例如:
cls
$date = '1/1/2016'
$query = 'some $$marker$$ script'
$query = $query.replace('$$marker$$', $date)
$query
你在哪里查询来自哪里更换?此功能不会生成或编辑查询? – Matt
阅读使用ADO.NET进行参数化查询 – alroc
@Matt我从计算机上的文件中提取查询 $ SqlQuery1 = get-content“C:\ Documents \ SQL Server Management Studio \ test \ test.sql”输出字符串; $ queries = @() $ queries + = @“ $ SqlQuery1 ”@ – mtb2434