Post reply

Any way Possible to Create 1 System Table

  • August 18, 2010 at 12:30 pm

    #141663

    Hello Everyone

    I hope that your day is going better than mine at the moment.

    I have been working on configuring Log Shipping on SQL 2005. I have configured and deleted Log Shipping numerous times on these two boxes, all because I cannot get the secondary configured correctly. On the last time of running the 3 stored procedures that remove Log Shipping from the primary and secondary boxes. I went back and started the wizard to configure LS again. But this time I received an odd error. I finally traced the error down to a hidden(undocumented) system stored procedure that checked the existence of a msdb.system table named msdb.dbo.log_shipping_monitor_alert

    I would like to be able to easily create this one table as a system table in the msdb database, as opposed to restoring the database from a backup. That would be a huge pain.

    Is there any way possible to create a table in the msdb database that is classed as a system table?

    Thanks in advance

    Andrew SQLDBA

  • August 18, 2010 at 12:39 pm

    #1208695

    I've done this with stored procedures so I don't know if it will work with a table, but expect it would.

    Once you create the object you mark it as a system object and this is what I had found to use:

    USE master

    GO

    EXEC sp_ms_marksystemobject '<object name>'

    GO

    /* to just check to show it is marked as a system object */

    SELECT name, is_ms_shipped

    FROM sys.objects

    WHERE name = '<object name>'

    GO

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • August 18, 2010 at 3:50 pm

    #1208807

    Thanks Dood

    That worked perfectly.

    I had created the table from the schema of another database, but it was showing up in the User table list in the MSDB database. I wanted to show or set the table so that it would show in the system table list.

    Your code did the trick.

    Learn something new everyday.

    Thanks

    Andrew SQLDBA

  • August 18, 2010 at 8:00 pm

    #1208858

    Glad I could help.

    I was tyring to remember where I came across this procedure...I know the issue I had was in SQL 2000 one of the procedures for I think mirroring or some system procedure that checked to see if some type of replication was setup got dropped. Seems to be the same area you had just with a table.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply