Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 adding sysmessages across a wan

Author  Topic 

Ask SQLTeam Question

0 Posts

Posted - 2002-06-24 : 01:24:07
Tim writes "I'm using mssql 2000 with service pack 2 on windows 2000 server with the most recent service pack.

I was curious about synchronizing messages in the master database, sysmessages, with an external 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 (which prevents normal dts from working). I am assuming the only way of inserting messages into the sysmessages table is via sp_addmessage, and to synchronize the data one would have to loop through all the data in the development server and run sp_addmessage on the production server row by row. This does not have to be a stored proc, but just a script. In fact, I think it has to be a script because in order to execute the script I have to be dialed out as my company is behind a firewall and the sql port is not opened (thus I cannot use a linked server either). Here is what I was attempting:

Create PROCEDURE ExportSysMessages

declare UserMessages_Cursor cursor for
select error,
description from server1.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 ,

while @@fetch_status = 0
EXECUTE server2.master.dbo.sp_addmessage @msgnum,
@severity ,
@msgtext ,

fetch UserMessages_Cursor
into @msgnum,
@severity ,
close UserMessages_Cursor
deallocate UserMessages_Cursor

It works fine when server1 = server 2, but, that does me no good since it has to be over the Internet."

Most Valuable Yak

15732 Posts

Posted - 2002-06-24 : 08:37:53
You can export the user-defined messages to a text file using bcp or DTS and then import that file directly into sysmessages the same way. Something like:

bcp "select error, severity, description from master.dbo.sysmessages where error >=50000" queryout c:\msgs.txt -c -Uusername -Ppassword -Sserver1

You can then copy this text file to the hard drive on server2 and import in into a holding table with the following structure:

CREATE TABLE HoldMessages (Error int, Severity smallint, Description nvarchar(255))


BULK INSERT HoldMessages FROM 'C:\msgs.txt'

or bcp:

bcp dbo.HoldMessages in c:\msgs.txt -c -Uusername -Ppassword -Sserver2

And then run this INSERT statement:

INSERT INTO master.dbo.sysmessages (error, severity, description, dlevel)
SELECT *, 0 AS dlevel FROM HoldMessages

You *may* need to enable system table updates in order for this INSERT to work correctly. Try it without changing any settings. If you get an error that indicates it failed, use the following T-SQL:

EXECUTE master..sp_configure 'allow updates', '1'

...and try the import again. Once it's done you can change it back:

EXECUTE master..sp_configure 'allow updates', '0'

And if you don't have permissions to run sp_configure, then you can use your cursor stored procedure as before, just substitute HoldMessages for sysmessages in your cursor declaration.

Go to Top of Page

- Advertisement -