adding sysmessages across a wan

Please start any new threads on our new site at https://forums.sqlteam.com. 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

AskSQLTeam
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
AS

declare UserMessages_Cursor cursor for
select error,
severity,
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 ,
@msgtext

while @@fetch_status = 0
begin
EXECUTE server2.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 server1 = server 2, but, that does me no good since it has to be over the Internet."

robvolk
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))

And use BULK INSERT:

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'
RECONFIGURE WITH OVERRIDE

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

EXECUTE master..sp_configure 'allow updates', '0'
RECONFIGURE WITH OVERRIDE

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources