2

i have the below code to get the Biztalk applications in ServerB. works fine if i run it in Server B. How can i run the same script from ServerA and get the Biztalk applications from ServerB. Invoke-command remoting doesnt seem to work.

# Get local BizTalk DBName and DB Server from WMI
$btsSettings = get-wmiobject MSBTS_GroupSetting -namespace 'root\MicrosoftBizTalkServer'
$dbInstance = $btsSettings.MgmtDbServerName
$dbName = $btsSettings.MgmtDbName

# Load BizTalk ExplorerOM
[void] [System.reflection.Assembly]::LoadWithPartialName("Microsoft.BizTalk.ExplorerOM")
$BizTalkOM = New-Object Microsoft.BizTalk.ExplorerOM.BtsCatalogExplorer
$BizTalkOM.ConnectionString = "SERVER=$dbInstance;DATABASE=$dbName;Integrated Security=SSPI"

$apps = $BizTalkOM.Applications | Select-Object Name,Status | Where-Object {$_.name -in 'BT.Order.CustomerOrder','BT.Order.FControl'}
$apps
medinibster
  • 57
  • 2
  • 11

3 Answers3

2

If you don't have the BizTalk WMI/ExplorerOM installed or can't (think about it - you're trying to explicitly load the Microsoft.BizTalk.ExplorerOM dll in there), you could get by with just an Invoke-Sqlcmd - but note that this is more fragile than using the OM, could break with new versions, should be used with extreme caution in production, etc... It also might not work if there are dynamic send ports at play (I don't have any deployed currently to test with). 

$sql = "SELECT app.nvcName as Name,
    CASE
        WHEN SUM(o.nOrchestrationStatus) IS NULL AND SUM(r.Disabled) IS NULL AND SUM(sprt.nPortStatus) IS NULL  THEN 'N/A'
        WHEN SUM(CASE WHEN o.nOrchestrationStatus IS NULL THEN 3 ELSE o.nOrchestrationStatus END) = COUNT(*) * 3 AND SUM(CASE WHEN r.Disabled IS NULL THEN 0 ELSE r.Disabled END) = 0          AND sum(CASE WHEN sprt.nPortStatus IS NULL THEN 3 ELSE sprt.nPortStatus END) = count(sprt.nPortStatus) * 3  THEN 'Started' 
        WHEN SUM(CASE WHEN o.nOrchestrationStatus IS NULL THEN 1 ELSE o.nOrchestrationStatus END) = count(*)     AND SUM(CASE WHEN r.Disabled IS NULL THEN -1 ELSE r.Disabled END) = -COUNT(*) AND sum(CASE WHEN sprt.nPortStatus IS NULL THEN 1 ELSE sprt.nPortStatus END) = count(*) THEN 'Stopped'       
        ELSE 'Partially Started' 
    END as Status
FROM bts_application app WITH(NOLOCK)
LEFT JOIN (
    SELECT asm.nApplicationID, orch.nvcName, orch.nOrchestrationStatus FROM bts_assembly asm WITH(NOLOCK)
    inner join bts_orchestration orch WITH(NOLOCK)
    on asm.nid = orch.nAssemblyID) o
ON o.nApplicationID = app.nID

LEFT JOIN bts_sendport sprt with(nolock)
ON sprt.nApplicationID = app.nID

LEFT JOIN (
    select rprt.nApplicationID, rprt.nvcName as rprtName, rloc.Name as rlocName, rloc.Disabled from bts_receiveport rprt with(nolock)
    INNER JOIN  adm_ReceiveLocation rloc with(nolock)
    on rprt.nID = rloc.ReceivePortId) r
on r.nApplicationID = app.nid

--WHERE app.nvcName IN ('BT.Order.CustomerOrder','BT.Order.FControl')
GROUP BY app.nvcName"

Invoke-Sqlcmd -ServerInstance 'localhost' -Database 'BizTalkMgmtDb' -Query $sql | Where-Object { $_.Name -in 'BT.Order.CustomerOrder','BT.Order.FControl'}

Note that you could limit the results at the SQL level as well by uncommenting the WHERE clause in the sql string.

And note that this query can be vastly simplified if you really just want applications and don't care about their statuses - it'd just be something like Select nvcName from bts_application.

Dan Field
  • 20,885
  • 5
  • 55
  • 71
  • hi Dan, i ran this in Server A and now i got the below error: Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) At line:27 char:1 – medinibster Apr 18 '16 at 06:07
  • Make sure the SQL Server you're trying to connect to allows remote connections and that you have privileges to connect (and you have the server/instance name correct). – Dan Field Apr 18 '16 at 12:34
  • See http://stackoverflow.com/questions/1975780/sql-server-enable-remote-connections-without-ssms – Dan Field Apr 18 '16 at 12:35
  • thanks Dan, this is now resolved. the approach worked. – medinibster Apr 22 '16 at 12:09
  • Gladto hear - consider marking this as the answer if it helped solve your problem. – Dan Field Apr 22 '16 at 13:05
1

To run that script on any computer, the BizTalk WMI components must be installed. The WMI components are installed with the BizTalk Server Administration Tools so you would have at least install those.

Then, just change the connection string to point to whatever Management Database you need.

Johns-305
  • 10,908
  • 12
  • 21
0

Remoting should work. Are you sure you don't run into the "double hop" issue? You will if your SQL Server is on another machine than ServerB (you will get a login failed for an anonymous logon instead of the user account you are working with). Look at https://technet.microsoft.com/en-us/library/hh849872.aspx to make sure you delegate your credentials to the SQL server. You should allow your client to sent the credentials and allow the server to receive the credentials. By adding your credentials to the Invoke-Command, it should work.

EvertWS
  • 1