Enable ad hoc updates to system catologs

Where is the option to enable ad hoc updates to system catologs in Enterprise manager??  Thanks.
pansophyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

danblakeCommented:
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
Scott PletcherSenior DBACommented:
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
pansophyAuthor Commented:
Out of curiousity, why is it a bad idea?

All I want to do is add field descriptions programatically.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
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
pansophyAuthor Commented:
Is there another way to do it without using the system tables?
0
Scott PletcherSenior DBACommented:
You can use QA or even OSQL.
0
danblakeCommented:
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
pansophyAuthor Commented:
danblake> How do I run this script in code?  Can I save it as a SQL statement and run docmd.execute(strSQL)?
0
ala_frostyCommented:
which is an MS-Access issue and not really a SQL statement. Specifically, what are you trying to accomplish anyway?
0
danblakeCommented:
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
pansophyAuthor Commented:
>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
danblakeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.