Sometimes SSMS gives you stale results and there you have the Refresh button to fix that.
In SQL PowerShell, the same situation can arise where the SQL Server PowerShell Provider is responding to your request with stale data. So how do you fix that? Pretty easily actually. Wherever you are just say Get-ChildItem “.” and wrap a “.Refresh()” method around it, like this:
(Get-ChildItem . ).Refresh()
PS SQLSERVER:SQLLOCALHOSTSQL2016Databases>
(Get-ChildItem . ).Refresh()
PS SQLSERVER:SQLLOCALHOSTSQL2016Databases>
If you want to refresh a different location than where you are in the provider right now, just replace the “.” with the path that you want refreshed.
(Get-ChildItem SQLSERVER:SQLLOCALHOSTSQL2016DatabasesAirStatstables).refresh()
The SQL PowerShell Provider for SSAS can suffer from the same situation (after all, so does working with SSAS in SSMS) but oddly, the fix is just slightly different. Different enough to drive you mad when you can’t find it! (Ask me how I know.)
(Get-Item SQLSERVER:SQLASLOCALHOSTSQL2016Databases).Refresh()
You can also just refresh your current location like you can with the relational provider, just using this slightly different command:
(Get-Item . ).Refresh()
As I work on demos to raise awareness for things you can do and things that need added to SSAS+PowerShell, I ran into this and wanted to share it just in case I can save you the frustration of having to figure it out yourself.
That’s it for today! J
The post Refreshing the SQL Server PowerShell Provider first appeared on SQLvariations: SQL Server, a little PowerShell, maybe some Power BI.
