The below script will check for that and send a mail
alerting me about the status, not very fancy but it does the trick
-- Query if any messages are suspended
declare @Status int
declare @messages varchar(256)declare @msgbody varchar(max)
select @messages = COUNT(*) from [dbo].[InstancesSuspended]
select @msgbody = 'There are ' + @messages + ' suspended messages' +
CHAR(13)
if @messages != 0
begin
exec sp_send_dbmail @profile_name
='ProfileName'
,@recipients
= 'name@company.com'
,@subject ='Error in BizTalk
application, Suspended messages'
,@body = @msgbody
end
-- Query for any receive ports that are disabled
declare @Status2 int
declare @ports varchar(256)
declare @msgbody2 varchar(max)
select @msgbody2 = 'The following receive
ports are disabled:' + CHAR(13)
select @Status2 = COUNT(*) from [dbo].[adm_ReceiveLocation] where
[Disabled] != 0
if @Status2 != 0
begin
declare
port_cursor cursor FOR
select name from [dbo].[adm_ReceiveLocation]
where [Disabled] !=
0;
open
port_cursor
fetch next from port_cursor
into @ports
while
@@FETCH_STATUS =
0
begin
select
@msgbody2 = @msgbody2 +
@ports + CHAR(13)
fetch
next from
port_cursor into @ports;
end
close
port_cursor;
deallocate
port_cursor;
begin
exec sp_send_dbmail @profile_name
='ProfileName'
,@recipients
= 'name@company.com'
,@subject ='Error in BizTalk
application, receive port(s) are disabled'
,@body = @msgbody2
end
end
