SQL query in Powershell

For security reasons I only had access to the BizTalk Server, not to the SQL Server. I know I can also install SQL Server Management Studio on the BizTalk Server, but there’s another way by using Powershell. First  you will have to install the PowerShell module for SQL Server, that is: SQLPS.  You can download the module via the Microsoft SQL Server 2016 Feature Pack: Download. Locate the PowerShellTools.msi and install on BizTalk Server.

Next, open PowerShell ISE (run as adminstrator):

Import-Module Sqlps -DisableNameChecking;
#Get-Module -ListAvailable -Name Sqlps

$_server = Get-WmiObject -Namespace “root\MicrosoftBizTalkServer” -Class “MSBTS_GroupSetting”
$SqlServerName = $_server.MgmtDbServerName
$bizTalkMgmtDbName = $_server.MgmtDbName

$sql = “SELECT [Name] FROM [$bizTalkMgmtDbName].[dbo].[adm_Server]”
$biztalkServers = Invoke-Sqlcmd -ServerInstance $SqlServerName -Database $bizTalkMgmtDbName -Query $sql

foreach ($i in $biztalkServers)
{
$bizTalkServer = $i.Item(0)
write-host $bizTalkServer
}

Note that I didn’t specify a path when importing the Sqlps module. That’s because I added an environment variable PSModulePath:

PSModulePath=C:\Windows\system32\WindowsPowerShell\v1.0\Modules\;C:\Program File
s (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *