sp_notify_operator

• gujsa01

  SSC-Addicted

  Points: 408

  More actions

  June 13, 2007 at 2:35 pm

  #196404

  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 ...

• Jeff Moden

  SSC Guru

  Points: 1004318

  More actions

  June 14, 2007 at 9:47 pm

  #713114

  EXEC (@CMD)

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)