POWERSHELL: SQL Powershell script for table creation in database

[string] $Server= "database server name"
[string] $Database = "data base name"

[string] $cmdText = $("BEGIN TRY " +
"CREATE TABLE [dbo].[table_name]( [SAPID] [nvarchar](50) NULL, [Exception] [bit] NULL) " +
"END TRY  " +
"BEGIN CATCH  " +
      "print 'WARNING: ALREADY [table_name] TABLE EXIST'" +
"END CATCH  ")


$sqlConnection = new-object System.Data.SqlClient.SqlConnection $(“server={0};database={1};Integrated Security=sspi” -f $Server,$Database)


$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.connection = $sqlConnection
$Command.commandtext = $cmdText

$sqlConnection.Open()

$Command.executescalar()

$sqlConnection.Close()

POWERSHELL SCRIPT: STRING.FORMAT

[string] $Server = ""
[string] $Database = ""

$(“server={0};database={1};Integrated Security=sspi” -f $Server,$Database)

Biztalk SQL Queries: Get List of send ports details by send port uri ( location )

select

   app.nvcName as ApplicationName,
   sp.nvcName as SendPortName,
   sptp.nvcAddress as SPUri,
   sp.nvcFilter

from bts_sendport as  sp

inner join bts_sendport_transform as spt on  spt.nSendPortID =sp.nID
left join bts_sendport_transport  as sptp on sptp.nSendPortID = sp.nID
inner join bts_application as app on app.nID = sp.nApplicationID

where

sptp.nvcAddress like '%url%'


  

Biztalk SQL Queries: Get List of receive locations and receive ports details by receive location name

select
 
   app.nvcName as ApplicationName,
   rp.nvcName AS ReceivePortName,
   rl.Name as ReceiveLocationName,
   rl.InboundTransportURL

from bts_application as app

left join dbo.bts_receiveport as rp on rp.nApplicationID = app.nID
left join dbo.adm_ReceiveLocation as rl on rl.ReceivePortId = rp.nID
left join dbo.bts_receiveport_transform as rpt on rpt.nReceivePortID = rp.nID

where

rl.Name like '%invoice%'

Biztalk SQL Queries: Get List of receive locations and receive ports details by APPLICATION NAME

select
   app.nvcName as ApplicationName,
   rp.nvcName AS ReceivePortName,
   rl.Name as ReceiveLocationName,
   rl.InboundTransportURL

from bts_application as app

left join dbo.bts_receiveport as rp on rp.nApplicationID = app.nID
left join dbo.adm_ReceiveLocation as rl on rl.ReceivePortId = rp.nID
left join dbo.bts_receiveport_transform as rpt on rpt.nReceivePortID = rp.nID

where
app.nvcName like '%Application Name%'

Biztalk SQL Queries: Get List of receive locations and receive ports details by APPLICATION NAME

select
   app.nvcName as ApplicationName,
   rp.nvcName AS ReceivePortName,
   rl.Name as ReceiveLocationName,
   rl.InboundTransportURL
from bts_application as app

left join dbo.bts_receiveport as rp on rp.nApplicationID = app.nID
left join dbo.adm_ReceiveLocation as rl on rl.ReceivePortId = rp.nID
left join dbo.bts_receiveport_transform as rpt on rpt.nReceivePortID = rp.nID

where
app.nvcName like '%Application Name%'

Biztalk SQL Queries: Get List of send port details by APPLICATION NAME

select
   app.nvcName as ApplicationName,
   sp.nvcName as SendPortName,
   sptp.nvcAddress as SPUri,
   sp.nvcFilter
from bts_sendport as  sp
inner join bts_sendport_transform as spt on  spt.nSendPortID =sp.nID
left join bts_sendport_transport  as sptp on sptp.nSendPortID = sp.nID
inner join bts_application as app on app.nID = sp.nApplicationID

where
 app.nvcName like '%Application Name%'
and
sptp.nvcAddress <> ''