sql server notification services: I have a procedure which is used to notify some users if a replication job
failed. The way i am doing is querying on sysreplicationalert table for a
given date and use xp_sendmail to notify users. since i am using a dynamic
sql when getting the alert_error_text from the above table if there is a
msg like .....The process could not bulk copy into table '"xx_tbl"'
variable @msgtxt faile showing error as
Incorrect syntax near 'xxx_tbl'. Reason is in dynamic sql i have the
variable in quotes so when it finds quote within the txt it errors out. Is
there an easier way of finding a comma ina string and replace it with
blank. any help willl be appreaciated.
TIA

DECLARE alert_cursor CURSOR
FOR
SELECT alert_error_text,time FROM msdb..sysreplicationalerts
where time > isnull(@EnterDate,getdate())
OPEN alert_cursor
FETCH NEXT FROM alert_cursor INTO @MsgTxt, @MsgTime
WHILE (@@fetch_status <> -1)
BEGIN
select @MsgTxt
SELECT @SendMailsql = N'EXEC master..xp_sendmail @recipients =
'+'''xxx.com'',
@subject = '+'''Test Alert Messaging'',
@Message = '''+ @MsgTxt +''''
EXEC sp_executesql @SendMailsql

FETCH NEXT FROM alert_cursor INTO @MsgTxt, @MsgTime
END
DEALLOCATE alert_cursor
SET NOCOUNT OFF
GO

--

Please ignore the previous msg. I got it working I got rid of Dynamic sql
just doinga straight xp_sendmail...
Thanks

--