How do i back up sysmessages for another computer? 1

[Netooi]

I have a database in SQL Server7.0 that has been upsized from Microsoft Access 97. The upsizing wizard created a bunch of new error messages in the master's sysmessages table. I need to be able to somehow transfer those new error messages that were generated when I make a backup for
a different computer. That different computer I restore the backup on did not upsize the database from Access so it doesnt have the new errormessages generated in its sysmessages table.

Any suggestions or solutions ??
Thanks,
Netooi

 

[tlbroadbent]

Run the following query in Query Analyzer on the first server. It will select the user defined error messages and generate a SQL Script to add the messages. Copy the results to the clipboard, login to the other server, paste the script into the Query Analyzer and execute.

Note the generated script will replace existing error messages on the second server that have the same error number. Follow the instructions below to modify this behavior. You will want to modify the criteria to only select the messages generated by Access. I don't know what that criteria is.

If multiple languages are installed on SQL Server, you may want to use the @lang option of sp_addmessage to specify the language of the messages.

----------------------------------------
Use master

Select
'exec sp_addmessage' +
' @msgnum = ' + ltrim(str(m.error)) + ',' +
' @severity = ' + ltrim(str(m.severity)) + ',' +
' @msgtext = ''' + m.description + ''',' +

--Change ''replace'' to null if you don't
--want to replace existing mesages with
--the same error number on the other server.
' @replace = ''replace''' +

char(10) + 'Go'

from master.dbo.sysmessages m
where error>=50000
----------------------------------------
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Netooi]

Thanks that is going to work great....
I've taken what you gave me and made it a stored procedure
called GetErrorMessages that enters the script into a table.

I then made another SP called UpdateErrorMessages to run on the new computer to retrieve the statements from the table and execute them thus putting the new ErrorMessages in the master's sysmessages table...

My next question... Is there anyway to execute UpdateErrorMessages to add the errormessages when I restore the database so i wont have to physically go in and call it after restoring...
This isnt that big of a deal at all.. I was just kindof wondering.

 

[tlbroadbent]

Unless you restore the master database, you shouldn't need to run the procedure more than once. Messages are only stored in the master DB not in user databases. Restoring user databases will not affect the master. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.