Hey guys,
I am trying to run a remote query against a MSSQL database and want to use a user provided parameter value as part of the query; e.g., CmdLet asks for a document number when CmdLet executed and that value is in the WHERE clause. I was hoping there was something like the $Using feature but I have not found anything.
Thanks in advance.
Function Search-TestDocumentNumber {
[CmdletBinding()]
param(
[Parameter(Mandatory = $True, ValueFromPipeline = $True)]
[String]$SqlServer,
[Parameter(Mandatory = $True, ValueFromPipeline = $True)]
[String]$Database,
[Parameter(Mandatory = $True, ValueFromPipeline = $True)]
$DocNumber
)
BEGIN {
[int]$ConnectionTimeout = 30
[int]$QueryTimeout = 120
$TSQL = @'
SELECT * FROM myTable WHERE docnum = $DocNumber
'@
$SqlConnection = new-object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $SqlServer, $Database, $ConnectionTimeout
$SqlConnection.ConnectionString = $ConnectionString
$SqlConnection.Open()
$cmd = new-object system.Data.SqlClient.SqlCommand($Tsql, $SqlConnection)
$cmd.CommandTimeout = $QueryTimeout
}
PROCESS {
$ds = New-Object system.Data.DataSet
$da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$Results = $ds.Tables[0].Rows
}
END {
$SqlConnection.Close()
$Results | Format-Table -AutoSize
}
}
I think your real variable needs to be mafe with @" … "@ in order for the variable to be evaluated. Once you make that change, I think you’ll have to put $docnumber like so, ‘$docnumber’.
Let me know how that works for you.
That seems to do the trick. Obviously I was thinking single quotes do not expand variable values so didn’t try this. Looks to be a SQL thing.
Thank you.
Updated code for reference:
Function Search-TestDocumentNumber {
[CmdletBinding()]
param(
[Parameter(Mandatory = $True, ValueFromPipeline = $True)]
[String]$SqlServer,
[Parameter(Mandatory = $True, ValueFromPipeline = $True)]
[String]$Database,
[Parameter(Mandatory = $True, ValueFromPipeline = $True)]
$DocNumber
)
BEGIN {
[int]$ConnectionTimeout = 30
[int]$QueryTimeout = 120
$TSQL = @"
SELECT * FROM myTable WHERE docnum = '$DocNumber'
"@
$SqlConnection = new-object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $SqlServer, $Database, $ConnectionTimeout
$SqlConnection.ConnectionString = $ConnectionString
$SqlConnection.Open()
$cmd = new-object system.Data.SqlClient.SqlCommand($Tsql, $SqlConnection)
$cmd.CommandTimeout = $QueryTimeout
}
PROCESS {
$ds = New-Object system.Data.DataSet
$da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$Results = $ds.Tables[0].Rows
}
END {
$SqlConnection.Close()
$Results | Format-Table -AutoSize
}
}
Glad that worked for you!
I would recommend using TSQL params instead in order to avoid SQL injection and I think it simplifies the overall since your $TSQL would change to:
$TSQL = "SELECT * FROM myTable WHERE docnum = @docnum"
And then your SQLCommand would be formed as
$cmd.Parameters.AddWithValue("@docnum", $Doc number)
Between lines 21 and 22