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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 adding sysmessages across a wan
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 06/24/2002 :  01:24:07  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

USA
15732 Posts

Posted - 06/24/2002 :  08:37:53  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000