SQL Server Performance Forum – Threads Archive

SP_NOTIFY_OPERATOR

Hi, My Space_Alert stored proc is not able to invoke sp_notify_operator system proc dynamically as follows: @Message variable gets populated if server drives fall below 5GB. ******************
DECLARE @CMD varchar(2000), @retstats int, @Message varchar(1000) set @CMD = ‘msdb.dbo.sp_notify_operator @profile_name = ”SQLAgentMail” , ‘ +
‘@name = ”Sameer Gujar” , @subject = ”Space Alert of VSCHISQL20051 ”, ‘ +
‘@body = ”’ + @Message + ” EXECUTE @retstats = @CMD
**************** Error I receive states: The name ‘msdb.dbo.sp_notify_operator @profile_name = ‘SQLAgentMail’ , @name = ‘Operator1’ , @subject = ‘Space Alert ‘,
@body = ‘
C: ————- 4GB D: ————- 4.5GB E: ————- 2GB F: ————- 3GB’ is not a valid identifier.
********* The above statement within the error message works fine if i run it from a Query window. EXECUTE statement is as per book online example, but it fails.. Appreciate any help …

Is this first time executed or used to work fine before? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

First time executed. Thanks!

I don’t see any reason to use dynamic sql in this case… Check the following…
declare @Message Varchar(1000)
select @Message = ‘message….’
exec msdb.dbo.sp_notify_operator
@profile_name = ‘SQLAgentMail’,
@name = ‘Sameer Gujar’ , @subject = ‘Space Alert of VSCHISQL20051 ‘,
@body = @Message
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>