Powershell Day 17

 Day 17 Advance Mini Shell

 Mini shell is a good and can be used as a replacement of management studio, in fact you can get lot of detailed information.

For example in day 16 we discussed that


PS: SQLSERVER\…\databases\master:\>Invoke-sqlcmd –Query “select * from sys.databases”

Be default in windows Powershell the output is format-table means some limited number of columns will be displayed.  But here default format is format-list, which gives complete detailed information, in this case detailed information about databases.

Sql Powershell introduce following cmdlets


  • Invoke-Sqlcmd
  • Invoke-PolicyEvaluation
  • Encode-SqlName
  • Decode-SqlName
  • Convert-UrnToPath
    1. Invoke-sqlcmd: as stated earlier, this cmdlet is used for calling the sqlcmd commands.
    2. Invoke-policyEvalution:  it uses sql server 2008 onwords policy bases management system.
    3. Encode-sqlname: sql server variants are having its own names which is not easy to understand so this cmdlet is used to encode the variant
    4. Decode-sqlname: it is opposite to encode-sqlname
    5. Convert0URNtoPath: SMO uses URN and we can understand paths for directory.

Encode-SqlName “Table:Test” returns the string “Table%3ATest”.

Decode-SqlName “Table%3ATest” returns “Table:Test”.



And this is the URN to the same object:

Server[@Name=’MyComputer’]\Database[@Name=’AdventureWorks2008R2′]\Table[@Name=’Address’ and @Schema=’Person’]

Adding the SQL Server Snap-ins to Windows PowerShell :

Ok this looks interesting ,but as we discussed. When we run sqlps it interns registering sql servers default span ins

Add-PSSnapin SqlServerCmdletSnapin100

Add-PSSnapin SqlServerProviderSnapin100

So in the same way if we want to use sqlps from our Powershell we can manually add those spanins. As

Add-PSSnapin SqlServerCmdletSnapin100

Add-PSSnapin SqlServerProviderSnapin100

This will be same as running sqlps.

Take an example.

Run the psdrive at windows ps: you will not see “SqlSErver” drive.

Add those snapins and run the psdrive again…. Yahoo… we got “SQLSERVER” drive. Similar to what we get when we run sqlps.

With this on windows Powershell we can add other custom/thirdparty snapins. Also add the assembly.

*Remember only sql server snapins (mini shell) is supported by Microsoft.

This entry was posted in Powershell and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.