Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Enable ad hoc updates to system catologs

Posted on 2004-10-22
Medium Priority
Last Modified: 2012-08-13
Where is the option to enable ad hoc updates to system catologs in Enterprise manager??  Thanks.
Question by:pansophy
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +1
LVL 13

Expert Comment

ID: 12382301
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.
LVL 70

Expert Comment

by:Scott Pletcher
ID: 12382616
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.

Author Comment

ID: 12383043
Out of curiousity, why is it a bad idea?

All I want to do is add field descriptions programatically.
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

LVL 70

Expert Comment

by:Scott Pletcher
ID: 12383396
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.

Author Comment

ID: 12383461
Is there another way to do it without using the system tables?
LVL 70

Expert Comment

by:Scott Pletcher
ID: 12383588
You can use QA or even OSQL.
LVL 13

Expert Comment

ID: 12392526
You can add descriptions to tables, using the following extended sp:

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:
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'

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.

Author Comment

ID: 12400302
danblake> How do I run this script in code?  Can I save it as a SQL statement and run docmd.execute(strSQL)?

Expert Comment

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

Expert Comment

ID: 12409255
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.

Author Comment

ID: 12410441
>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.
LVL 13

Accepted Solution

danblake earned 2000 total points
ID: 12413094
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
/*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)

/*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
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*/
where table_name = @table_name
and column_name = @column_name

/*End of loop*/

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

604 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