Wednesday, April 13, 2011

My first PowerShell Sql script

The first thing you need to do is initialize the Sql Server Provider.

So first create a script called “InitializeSqlServerProvider.ps1” and put the following code into it:

# Add the SQL Server Provider.

$ErrorActionPreference = "Stop"


if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
    throw "SQL Server Provider for Windows PowerShell is not installed."
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)

# Set mandatory variables for the SQL Server provider
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

# Load the snapins, type data, format data
cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Update-TypeData -PrependPath SQLProvider.Types.ps1xml
update-FormatData -prependpath SQLProvider.Format.ps1xml

Once you have that in a script, execute it.

Now you are ready to run commands against your sqlserver using the “Invoke-SqlCmd” cmdlet.

Invoke-sqlcmd -ServerInstance "." -Database "Master" -Query "exec sp_who"

The above command runs the “Sp_Who” stored proc against the local sqlServer instance.

No comments: