May 23, 2013 at 4:17 am
if not exists (select * from [master].dbo.sysprocesses where left(program_name,8)='SQLAgent')
Begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='Database Mail',
@recipients ='email@id.com',
@body ='Please look into the issue; Scheduled Jobs will not run if the SQL Server Agent Service remains stopped.',
@subject ='SQL Agent Stopped,Please Check' ;
End
I am using above script to find out whether SQL Agent Stopped is stoped or Running , I want to add IP address and Server name in subject line.
And I also want to monitor it for 40+ servers please help me for how above script can be used for monitoring the same .
May 23, 2013 at 4:49 am
Something like this to get server name and IP
declare @subject nvarchar (100)
declare @v-2 nvarchar(50)
set @v-2 = convert (nvarchar (50),case when CONNECTIONPROPERTY('local_net_address') is null then '' else CONNECTIONPROPERTY('local_net_address') end)
print @v-2
set @subject = 'SQL Agent Stopped, Please Check ' + @@SERVERNAME
print @subject
Probably a better way of doing it, this just just off the top of my head
May 23, 2013 at 5:08 am
Thanks for your reply it worked on sql server 2008, but
on sql server 2005 it is giving me following error
Msg 195, Level 15, State 10, Line 4
'CONNECTIONPROPERTY' is not a recognized built-in function name.
May 23, 2013 at 5:21 am
Use the local_net_address column of sys.dm_exec_connections
May 23, 2013 at 5:37 am
Thanks for your reply ,:-)
August 27, 2015 at 1:53 am
While I am executing below query on machine which is accessing from sql agent from out of LAN:
SELECT
CONNECTIONPROPERTY('net_transport') AS net_transport,
CONNECTIONPROPERTY('protocol_type') AS protocol_type,
CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
CONNECTIONPROPERTY('local_net_address') AS local_net_address,
CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
CONNECTIONPROPERTY('client_net_address') AS client_net_address
Mahesh D. Deore
Nasik
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply