June 18, 2002 at 3:43 pm
I was curious if anyone knows how to add customer error messages to another server without adding a linked server. I figure one can transfer data from tables using dts, then there is bound to be a way of doing it on a table that is locked down with triggers. Here is what I was attempting:
Create PROCEDURE ExportSysMessages
AS
declare @source varchar(100)
declare @target varchar(100)
declare UserMessages_Cursor cursor for
select error,
severity,
description from developmentserver.master.dbo.sysmessages where error >=50000
declare@msgnum int,
@severity smallint ,
@msgtext nvarchar(255)
declare@replace varchar(7)
set @replace = 'replace'
open UserMessages_Cursor
fetch UserMessages_Cursor
into@msgnum,
@severity ,
@msgtext
while @@fetch_status = 0
begin
EXECUTE prodoctionserver.master.dbo.sp_addmessage @msgnum,
@severity ,
@msgtext ,
@replace='replace'
fetch UserMessages_Cursor
into@msgnum,
@severity ,
@msgtext
end
close UserMessages_Cursor
deallocate UserMessages_Cursor
It works fine when the two servers are the same, but, that does me no good. If anyone knows how to make this work, let me know. The basic problem is making the two servers connect over a WAN (preferably without making a linked server). Thanks
June 18, 2002 at 4:50 pm
Ok I am confused. You say you are trying to add a custom error message to two different servers by adding to the first then copying to the secound. However the easiest way to do this is to script the message to begin with then apply to each server. But you confused me with the linked server bit and works if same server??????
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 19, 2002 at 8:51 am
The only way I'm aware of to add messages to the sysmessages table in mssql 2000 for the master database is sp_addmessage because of the triggers and such that exist on it. I'm sure they could be disabled, but, that would defeat the purpose of them being there and the sp_addmessage system stored proc.
June 19, 2002 at 5:42 pm
Sorry you still are not making sense to me. Can you back up and explain the issue and what you need to happen?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 20, 2002 at 9:05 am
The problem is synchronizing the master.sysmessages table with an external master.sysmessages table on sql 2000.
June 20, 2002 at 9:37 am
I would create a temp table or a staging table of some sort on the external. Then transfer all custom messages (DTS, insert..select, whatever) to this table. Run a query on this table v the external sysmessages table. This would probably be an outer join that returns the differences. If this happens only periodically, then I'd use a cursor to roll through the differences and apply them using sp_addmessage. Or a combination of sp_dropmessage, sp_addmessage.
This wouldn't be hard to automate. If you are just periodically adding messages, then Antares suggestion of scripting the change and applying to all servers might be the easiest.
Note: I would create the temp table in some other database than master.
Steve Jones
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply