Executing a PowerShell script from SSMS to operate on a database used in a Timextender Classic Project¶

Installing PowerShell¶

To install the SqlServer module from the PowerShell Gallery, start a PowerShell session as an administrator. For details, review this Microsoft documentation.

Scenario: Process a Single table in an Azure Analysis Services Tabular model¶

Consider the following PowerShell script:

$secpasswd = ConvertTo-SecureString "***Pass***" -AsPlainText -Force;

$mycreds = New-Object System.Management.Automation.PSCredential ('****UID****’, $secpasswd);

Invoke-ProcessTable -TableName "<tablename>" -Database "<DatabaseName>"   -RefreshType Full   -Server "asazure://centralus.asazure.windows.net/<AS_serverName>" -Credential $mycreds

1. Execute the script in a PowerShell command line to verify it works OK. In SQL Server Management Studio, Right-click an on-prem SQL Server database and select the option "Start PowerShell", then copy-paste the above script to execute. You may see the following output:

   Impact XmlaResults
   ------ -----------
   Microsoft.AnalysisServices.Tabular.ObjectImpact {Microsoft.AnalysisServices.XmlaResult}
   

2. In SQL Server Management Studio, enable your MDW database to run xp_cmdshell.

   EXEC sp_configure 'show advanced options', 1;
   GO
   Reconfigure;
   GO
   
   EXEC sp_configure 'xp_cmdshell',1
   GO
   Reconfigure
   GO
   

3. Create a .ps1 file with the PowerShell script and save it as C:\Temp\PS_Script_Proces_Table.ps1

4. In SQL Server Management Studio, invoke the PowerShell script with xp_cmdshell. It spawns a Windows command shell and executes PowerShell script from the given file.

   xp_cmdshell 'powershell.exe -File C:\Temp\PS_Script_Proces_Table.ps1'
   

5. Verify in SSMS that the above command executed successfully. Review table properties in the Azure Analysis Services Tabular model to verify it is refreshed.