| Author |
Topic |
|
Carat
Yak Posting Veteran
92 Posts |
 Posted - 2008-10-03 : 06:53:31
|
| I have a trigger on the table sysreplicationalerts in the Msdb System Database. When a new record is inserted in this table the trigger fires and it will insert the field 'alert_error_text' into our logging table.The problem is that this field is of type nText and this type can't be used within the inserted table of a trigger. So I want to change the type to nvarchar(max). Is this possible? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-03 : 07:21:21
|
| use cast() to explicitly cast ntext value to nvarchar(max) |
 |
|
|
Carat
Yak Posting Veteran
92 Posts |
Posted - 2008-10-03 : 07:23:45
|
| I already tried the following but still got the error: declare @AlertSubject varchar(max), @MailProfile varchar(128), @UserName varchar(128) select @AlertSubject = convert(nvarchar(max), Alert_Error_Text) from inserted set @UserName = (select suser_name()) set @MailProfile = (select [Name] from Msdb.dbo.sysmail_profile) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-03 : 07:29:53
|
| whats the error you got? also you're assuming that inserted will contain only one record at a time which may not be true always.Better to create a temporary table and put value from inserted in it. |
|
|
|
Carat
Yak Posting Veteran
92 Posts |
Posted - 2008-10-03 : 07:58:25
|
| This is the error:Msg 311, Level 16, State 1, Procedure trig_LogReplicationAlert, Line 14Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.That is correct what you are saying but I'm just testing with it now.I will use: select convert(varchar(max), Alert_Error_Text) into #Temp from inserted |
|
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2008-10-03 : 08:31:29
|
| A long time ago (back when I worked on Sybase), I was told not to put a trigger on a system table.I've not questioned this since.Is it now a generally accepted practise?Just wondering... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-03 : 09:54:19
|
quote:
Originally posted by YellowBug
A long time ago (back when I worked on Sybase), I was told not to put a trigger on a system table.I've not questioned this since.Is it now a generally accepted practise?Just wondering...
not sure on that. I never used it and never would advise anybody to use it too. |
|
|
|
Carat
Yak Posting Veteran
92 Posts |
Posted - 2008-10-08 : 04:48:06
|
| Last year I put a Trigger on the Msdb table sysjobhistory.I did this because when you have for example 10 steps in a job and you choose the 'go to next step' whenever a jobstep failed, the job whould be completed succesfully also when there would be an error on a particular jobstep.This trigger sends me an alert email and logs it to our loggingtable.So putting a Trigger on system table did not have any impact on a production system. |
|
|
|
|
Alter System Table (Msdb)