September 17, 2013 at 6:47 am
Hi guys,
I want to add a SQLAgent check into my hourly job, alerting if it's down as long as its not XE. My procedure works outside of of openquery, works for servers where it is down but it does not work for servers where the agent is running.
Full error message: http://pastebin.com/hQLZ5rwb
So, let's say I have SRV01, SRV02, SRV03. 01 is XE, 02 has agent running, 03 has agent down.
It should have 2 rows of 'dird' (I thought there was an issue with null) and 1 saying 03 is down.
Instead I only get 03 record and I get errors for the other 2 (as shown above). Any idea how to address this compile/run issue? I don't see the problem between returning 'dird' or a server.
Code:
DECLARE @query varchar(max);
set @query = 'insert into dbo.DB_CHANGES (INSTANCE,DB_NAME,CHANGE_INFO,CHANGE_DATE)
select * from openquery(' + @server + ',''IF NOT EXISTS (SELECT 1
FROM MASTER.dbo.sysprocesses
WHERE program_name = N''''SQLAgent - Generic Refresher'''')
BEGIN
IF EXISTS (select 1 where convert(varchar(30), convert(sql_variant, SERVERPROPERTY(''''edition'''')))
not like ''''Express%'''')
BEGIN
SELECT @@SERVERNAME [INSTANCE], null [DB_NAME], ''''SQLAgent not running on '''' + @@SERVERNAME [CHANGE_INFO], GETDATE() [CHANGE_DATE]
END
ELSE
SELECT ''''dird'''' [INSTANCE],''''dird'''' [DB_NAME],''''dird'''' [CHANGE_INFO], getdate()[CHANGE_DATE]
END
ELSE
SELECT ''''dird'''' [INSTANCE],''''dird'''' [DB_NAME],''''dird'''' [CHANGE_INFO], getdate() [CHANGE_DATE]'')';
exec(@query);
September 17, 2013 at 8:45 am
Doesn't matter now, was able to change it into a single query:
SELECT @@SERVERNAME, null, ''SQLAgent not running on '' + @@SERVERNAME, GETDATE()
FROM MASTER.dbo.sysprocesses
WHERE program_name = N''SQLAgent - Generic Refresher''
having count(*)=0
and convert(varchar(30), convert(sql_variant, SERVERPROPERTY(''edition''))) not like ''%Express%''
'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply