Categories
PowerShell

Alternative for Invoke-SqlCmd

Invoke-SqlCmd ist a great cmdlet for PowerShell for querying SQL databases. But sometimes you don’t want to install the extra requirement SQL Server Management Objects on the server.

Invoke-SqlCmd -ServerInstance $Server -Database $Database -Query "SELECT * FROM Staff"

There’s an simple alternative. Use the .NET classes in the System.Data.SqlClient namespace which are available on every computer which has the .NET framework installed.

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"

3 replies on “Alternative for Invoke-SqlCmd”

Hi there,

This was brilliant, except I wasted some time because of extra ” surrounding “System.Data.DataTable” that were causing errors.

I was getting an error saying “Cannot find type [System.Data.Table]” until I removed the ” so that line of code looks like:
$table = new-object “System.Data.DataTable”
and it ran perfectly.
Thought I’d give you a heads up. Using Powershell 4.

Thanks.

Great post Peter especially because this would work no matter what the version of Powershell your machines has. It is quite a pain to install packages in Powershell especially in the lower versions of windows. Furthermore syntactically this is close to how a connection is created in Java and other object oriented languages.

Why they dont’t take this as a relacement for Invoke-SqlCmd in dotnet core which is still not working?
Thanks for this, works perfectly!

Leave a Reply

Your email address will not be published. Required fields are marked *