2

I'm attempting to run some SQL through Powershell, using a wrapper function in PS to run the SQL command. As well, my SQL is in its own independent file and not hard-coded inline to the Powershell script, which would make what I'm trying to do trivial. What I need is to be able to have SQL Server do is to access the Powershell variable and use it in the query I'm sending. xp_cmdshell is coming up as a potential option to do this with, but I'd sooner not expose myself to the security vulnerabilities there if I don't have to.

Here's some pseudocode to get my idea across easier:

PSScript.ps1:

$var = 1
$query = query.sql
$con = connection(server_connection_params)
$command = New-Object System.Data.SqlClient.SqlCommand $sqlCommand,$connection
$command.ExecuteScalar()

Query.sql:

SELECT * FROM table WHERE field = $var;

Is there any way I could access $var in this fashion given how I'm calling the SQL in Powershell?

3

I expect the sample query you gave is basic, so my offer is basic but I think should work for you even with complex script files that require multiple variables to be passed/replaced.

The key here is you need to have a place holder in your script files that you can easily search for in PowerShell, then just replace it with that value.

An example query:

SELECT * FROM sys.databases WHERE name = 'var1'

In this example I am going to pull that query in from a file and then search for the var1 value, replacing it with the database name I want to query for...

$var = 'MyDatabase'
$query = Get-Content .\Sample.sql | foreach {$_.replace("var1",$var)}

Example usage:

enter image description here

1

This is easily solved using sqlcmd.exe and scripting variables, if you can call an external utility instead of using System.Data.SqlClient.SqlCommand

You'd just have this in your Query.sql file:

SELECT * FROM table WHERE field = '$(var)';

And then you'd call sqlcmd.exe like this:

sqlcmd -S <your server name> -b -i Query.sql -v var=$var

  • -S names the server to connect to.
  • -b turns on "batch mode", where any error aborts the subsequent commands.
  • -i runs the SQL from the named file
  • -v names a variable to substitute

You can provide multiple -v flags if you need more than one variable to be substituted.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.