Insert into sys.messages on SQL2005?

3 May 2007 1:56 PM

Chai

Hello:

On our current production server which is SQL 2000, we added a few rows into
sysmessages table in Master db for error handling in our in-house
application.

Now we plan to have a new production sever which will be SQL 2005, so I will
backup our current production db and restore it on our new server (then turn
off the SQL 2000).

In SQL 2005, now there is no sysmessages table in Master, I found
sys.messages view. Can I copy those few rows in sysmessgaes from our
soon-to-be old production sever (SQL2000) and insert into this sys.messages
view on our soon-to-be new production server (SQL2005)?

Thank you in advance for your responses and suggestions.

Chai

3 May 2007 2:17 PM

Russell Fields

Chai,

If there are just a few you can reenter them using the sp_addmessage stored
procedure.

If there are more, you could script out the sp_addmessage call with the
necessary parameters from your existing server. E.g.

SELECT 'EXEC sp_addmessage ' + CAST (error AS VARCHAR(5)) + ','
+ CAST (severity AS VARCHAR(5)) + ', N''' + description + '''''
FROM sysmessages
WHERE error >= 50000

RLF

Show quoteHide quote

"Chai" <c***@trs.state.il.us> wrote in message
news:u4PFmrYjHHA.1900@TK2MSFTNGP04.phx.gbl...
> Hello:
>
> On our current production server which is SQL 2000, we added a few rows
> into sysmessages table in Master db for error handling in our in-house
> application.
>
> Now we plan to have a new production sever which will be SQL 2005, so I
> will backup our current production db and restore it on our new server
> (then turn off the SQL 2000).
>
> In SQL 2005, now there is no sysmessages table in Master, I found
> sys.messages view. Can I copy those few rows in sysmessgaes from our
> soon-to-be old production sever (SQL2000) and insert into this
> sys.messages view on our soon-to-be new production server (SQL2005)?
>
> Thank you in advance for your responses and suggestions.
>
>
> Chai
>

3 May 2007 2:19 PM

Chris Wood

Chai,

Take a look at sp_altermessage in BOL (2005).

Chris

Show quoteHide quote

"Chai" <c***@trs.state.il.us> wrote in message
news:u4PFmrYjHHA.1900@TK2MSFTNGP04.phx.gbl...
> Hello:
>
> On our current production server which is SQL 2000, we added a few rows
> into sysmessages table in Master db for error handling in our in-house
> application.
>
> Now we plan to have a new production sever which will be SQL 2005, so I
> will backup our current production db and restore it on our new server
> (then turn off the SQL 2000).
>
> In SQL 2005, now there is no sysmessages table in Master, I found
> sys.messages view. Can I copy those few rows in sysmessgaes from our
> soon-to-be old production sever (SQL2000) and insert into this
> sys.messages view on our soon-to-be new production server (SQL2005)?
>
> Thank you in advance for your responses and suggestions.
>
>
> Chai
>