Error with alert tokens

• Gabriela Nanau

  SSCommitted

  Points: 1967

  More actions

  May 30, 2005 at 1:19 pm

  #102559

  I setup an alert and I attached a job JOB1 as response to the alert. The alert will fire at error 15489 ('%s' dropped from role '%s'.) The job will store in a log file some alert tokens. The job is basically something like this:

  print N'A-DBN  = [A-DBN]'

  print N'A-SVR  = [A-SVR]'

  print N'A-ERR  = [A-ERR]'

  print N'A-SEV  = [A-SEV]'

  print N'date =[DATE] ; time = [TIME]'

  print N'[A-MSG]=[A-MSG]'

  I fire the alert. Ussually will be fired by removing some user from a role, but for the purpose of this question plase consider the following raiserror:

  raiserror (15489, 16,1,'db_user','db_Role')

  The alert fires OK, the event is correctly recorded in SQL error log and Windows application log. The notification displays the full message:

  'db_user' dropped from role 'db_Role'

  I would expect that the job will record the same string in respons to [A-MSG] token, but instead I get an error like:

  Msg 170, Sev 15: Line 5: Incorrect syntax near 'db_user'. [SQLSTATE 42000]

  Any ideea what may cause this error?

  I run it on SQL Server 2000 Standard edition, SP3.

  Gabriela

• philcart

  SSC-Forever

  Points: 47794

  More actions

  May 30, 2005 at 5:07 pm

  #561790

  It's because of the single quotes in the message string.

  PRINT N'A-MSG = ' + REPLACE("[A-MSG]", '''','')
  " = double quote
  ' = single quote

   

  --------------------
  Colt 45 - the original point and click interface

• Gabriela Nanau

  SSCommitted

  Points: 1967

  More actions

  May 31, 2005 at 7:10 am

  #561867

  Thanks, that was the problem. It works now.

  Gabriela