Follow

Executing a PowerShell script from SSMS to operate on a database used in a TimeXtender Project

Installing PowerShell 

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

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.

 

References

SQL Server PowerShell

Process tabular metadata table

Was this article helpful?
0 out of 0 found this helpful

0 Comments

Please sign in to leave a comment.