0

I am using the .NET classes in the System.Data.SqlClient namespace on a SQL 2019 Enterprise Edition and have successfully run TSQL commands via the creation of a function in PowerShell.

function SqlQuery($server, $database, $query)
{
 $connection = New-Object System.Data.SqlClient.SqlConnection
 $connection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True;"
 $connection.Open()
 $command = $connection.CreateCommand()
 $command.CommandText = $query
 $result = $command.ExecuteReader()
 $table = new-object “System.Data.DataTable”
 $table.Load($result)
 $connection.Close()
 return $table
}

SqlQuery $Server $Database "SELECT * FROM Staff"

I have some quite large pieces of T-SQL code stored in discrete files that I would like to execute against this instance. Is it possible to utilise the function to call a T-SQL script stored in a filesystem file?

0

1 Answer 1

4

possible to call a T-SQL script rather than include it in the main body of the PowerShell script

Of course. Just read the file contents with get-content and pass that to the function:

function SqlQuery($server, $database, $query)
{
 $connection = New-Object System.Data.SqlClient.SqlConnection
 $connection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True;"
 $connection.Open()
 $command = $connection.CreateCommand()
 $command.CommandText = $query
 $result = $command.ExecuteReader()
 $table = new-object “System.Data.DataTable”
 $table.Load($result)
 $connection.Close()
 return $table
}

$server = "localhost"
$database = "tempdb"
$sql = get-content "c:\test\foo.sql"

SqlQuery $Server $Database $sql

If the sql file contains multiple batches, you'll need to break it up yourself, or use use the Invoke-Sqlcmd cmdlet from the SqlServer module, which you can install with

install-module SqlServer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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