Sending queries to SQL Server

Querying is a typical task we do with SQL Server. Normally we would open SQL Server Management Studio (SSMS) and type and execute our queries from there. If we are using PowerShell, that routine needs to be slightly adjusted. The few ways we can send queries to SQL Server using PowerShell are as follows:

• SQL Server Management Objects (SMO)

• Invoke-Sqlcmd

• ADO.NET

• Invoke-Expression

SQL Server Management Objects

We have been using SQL Server Management Objects (SMO) for a few chapters now. Although it's indirect, when we create SMO objects, use properties, and invoke methods, we are technically sending queries to SQL Server. Let us take the following snippet, for example:

$servername = "ROGUE"   # or localhost
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.
Server -ArgumentList $servername

$dbname = "TestDB"
$db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.
Database($server, $dbname)
$db.Create()

What we are really doing here is connecting...