Solved

Enable ad hoc updates to system catologs

Posted on 2004-10-22
859 Views
Last Modified: 2012-08-13
Where is the option to enable ad hoc updates to system catologs in Enterprise manager??  Thanks.
0
Question by:pansophy
    12 Comments
     
    LVL 13

    Expert Comment

    by:danblake
    Open EM, Establish the connection to the server that you wish to modify.  
    Go to Tools, Click SQL Server Properties--> this opens a dialog box, Click the server settings tab.

    The option is within the server behaviour box on this tab.
    0
     
    LVL 68

    Expert Comment

    by:ScottPletcher
    It's a bad idea to do system catalog updates -- or anything else too sophisticated -- in EM.  You really should use Query Analyzer for those types of tasks.
    0
     

    Author Comment

    by:pansophy
    Out of curiousity, why is it a bad idea?

    All I want to do is add field descriptions programatically.
    0
     
    LVL 68

    Expert Comment

    by:ScottPletcher
    EM is very flaky; sometimes it gets "confused".  Just be aware that at some point you might corrupt your system tables or cause other errors.
    0
     

    Author Comment

    by:pansophy
    Is there another way to do it without using the system tables?
    0
     
    LVL 68

    Expert Comment

    by:ScottPletcher
    You can use QA or even OSQL.
    0
     
    LVL 13

    Expert Comment

    by:danblake
    You can add descriptions to tables, using the following extended sp:
    sp_addextendedproperty

    For example, if you wish to check out how to do this, open EM, add a description to a table then choose save script to get a demo script.
    Alternativly, look at this one:
    BEGIN TRANSACTION
    DECLARE @v sql_variant
    SET @v = N'The Change Identifiying Record Number'
    EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'Changes', N'column', N'Change_Id'
    GO
    COMMIT

    This is adding a description to the table Changes, on column Change_ID.  You can see the authentication/object type and the description being added in @v.
    This does not require permission to modify the system tables, sometimes updates are performed to modify the system tables and EM is one of the easiest ways of getting there.  I hope this has answered your question.
    0
     

    Author Comment

    by:pansophy
    danblake> How do I run this script in code?  Can I save it as a SQL statement and run docmd.execute(strSQL)?
    0
     
    LVL 7

    Expert Comment

    by:ala_frosty
    which is an MS-Access issue and not really a SQL statement. Specifically, what are you trying to accomplish anyway?
    0
     
    LVL 13

    Expert Comment

    by:danblake
    As mentioned before OSQL would be most suitable to run this as it run SQL Commands directly from the command line.
    I do not get why, the different questions as docmd.execute(strSQL) which is VBA may run the strSQL statement, try it and find out.
    But you would need a link directly to the sql-server as a ADP or some other form to ensure you were running the sql-command on the sql-server and not the local access database system.
    0
     

    Author Comment

    by:pansophy
    >Specifically, what are you trying to accomplish anyway?

    I upgraded an Access database to a SQL database.  Unfortunately, as part of the upload/import process the field descriptions do not transfer.  As such, I exported all the field descriptions using a schema dump and now want to programatically import all the field descriptions.  

    Updating the system catalogs seems like the easiest way to do that.
    0
     
    LVL 13

    Accepted Solution

    by:
    This should do the trick for you as given below...
    (I understand that you would like to touch the system tables directly -- but its not really a good idea unless you are instructed my MS PSS -- things tend to go wrong and not all upgrades to the data in these tables always work -- so here is a safer way of doing it.)

    So if you have all the records stored in a table... it would be:

    /*Create Temporary Table to handle delete loop for SP*/
    select * into #SCHEMATABLEDUMP
    from SCHEMTABLE
    /*This should really be a defined table without using into and a select * -- please ensure that fieldnames for the temp table are changed for your table struct*/

    /*Begin loop -- test to see if any records requiring adding the description from our temp table*/
    while exists (select top 1 table_name from #SCHEMATABLEDUMP)
    begin


    /*Var declaration begin*/
    declare @table_name Nvarchar(255)
    ,@column_name Nvarchar(255)
    ,@owner Nvarchar(255)
    ,@description Nvarchar(255)
    /*Var declaration end*/

    /*Var value assignment begin*/
    select top 1 @table_name = table_name
                      ,@column_name = column_name
                      ,@owner = owner
                      ,@description = description
    from #SCHEMATABLEDUMP
    order by table_name,column_name
    /*Var value assignment end*/

    /*Execute adding the description -- as rqd*/
    EXECUTE sp_addextendedproperty N'MS_Description', @description, N'user', @owner, N'table', @table_name, N'column', @column_name

    /*Delete record from temporary table that we just added*/
    delete #SCHEMATABLEDUMP
    where table_name = @table_name
    and column_name = @column_name

    end
    /*End of loop*/
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Learn The Basics of Ethical Hacking & Pen Testing

    Computer and network security is one of the fastest growing and most essential industries in technology, meaning companies will pay big bucks for ethical hackers. This is the perfect course to leap into this lucrative career, learning how to use ethical hacking to reveal ...

    Suggested Solutions

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how the fundamental information of how to create a table.

    846 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    5 Experts available now in Live!

    Get 1:1 Help Now