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
- Invoke-sqlcmd: as stated earlier, this cmdlet is used for calling the sqlcmd commands.
- Invoke-policyEvalution: it uses sql server 2008 onwords policy bases management system.
- 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
- Decode-sqlname: it is opposite to encode-sqlname
- 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”.
PATH:
SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks2008R2\Tables\Person.Address
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.
