SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Linked Server - Ad hoc updates not allowed
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ranvir_2k
Posting Yak Master

United Kingdom
173 Posts

Posted - 12/18/2013 :  05:12:39  Show Profile  Reply with Quote
Hi all,

I have linked servers on SQL Server 2008, 2008 R2 and 2012.

Whenever I try to make a change to these linked servers in Management Studio I get the following error:

Ad hoc updates to system catalogs are not allowed

Does anyone know what the reason for this is and how to resolve it?

This does not seem to be a problem in SQL Server 2000 or 2005.

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 12/18/2013 :  05:24:54  Show Profile  Reply with Quote
Refer
http://www.mssqltips.com/sqlservertip/2875/how-to-allow-ad-hoc-updates-in-sql-server-system-catalogs/

--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 12/18/2013 :  05:24:54  Show Profile  Reply with Quote
Refer
http://sqljunkieshare.com/2012/02/22/ad-hoc-update-to-system-catalogs-is-not-supported/
--
Chandu

Edited by - bandi on 12/18/2013 05:28:06
Go to Top of Page

ranvir_2k
Posting Yak Master

United Kingdom
173 Posts

Posted - 12/18/2013 :  05:38:43  Show Profile  Reply with Quote
allow updates is set to 1 under run_value but I still have this issue.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/18/2013 :  06:31:22  Show Profile  Reply with Quote
quote:
Originally posted by ranvir_2k

allow updates is set to 1 under run_value but I still have this issue.


may be this?
http://support.microsoft.com/kb/2733673

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

alexsts
Starting Member

USA
13 Posts

Posted - 02/11/2014 :  17:48:25  Show Profile  Reply with Quote
quote:
Originally posted by ranvir_2k

allow updates is set to 1 under run_value but I still have this issue.<br></red>


only small problem with this article: it does not work on SQL 2012

Alex
Go to Top of Page

alexsts
Starting Member

USA
13 Posts

Posted - 02/11/2014 :  18:05:48  Show Profile  Reply with Quote
quote:
Originally posted by visakh16


may be this?
http://support.microsoft.com/kb/2733673

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



And how can anyone believe to what Microsoft said after opuses like the one in that:
quote:

Microsoft distributes Microsoft SQL Server 2008 R2 Service Pack 2 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 Service Pack 2 fix release.


Quote taken from the referenced article above.
Got latest release (file size about 3 MB) within seconds and then I spent almost half an hour downloading older release sized at 450MB!!! Because it turns out latest release does not contained all what previous did anyway.
Any idea how to really fix this issue?
Why would Microsoft break it is own product which worked fine by the way without this unnecessary change. I am sysadmin and DBO, I should be able to make necessary changes to any table/catalog.
Thanks

Alex
Go to Top of Page

alexsts
Starting Member

USA
13 Posts

Posted - 02/12/2014 :  10:03:51  Show Profile  Reply with Quote
quote:
Originally posted by ranvir_2k

allow updates is set to 1 under run_value but I still have this issue.


So, since this option does not work on SQL 2008 or SQL 2012 does anyone have better way or idea how to make it work?
Preferably without rolling back to SQL 2005...
Because I am at the point to put on hold upgrade from 2005 to any of those newest versions just for that issue. We have rather large databases heavily relying on ability to work with sys tables. And when I say work I meant to update, insert and not just select data from those tables.
Thanks

Alex

Edited by - alexsts on 02/12/2014 10:07:58
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1770 Posts

Posted - 02/13/2014 :  19:23:40  Show Profile  Reply with Quote
What are you actually trying to accomplish? Or is the issue that you want to generically modify any system table? Tell us what type of modification you want to make to the linked server and we can point you to the means of accomplishing the goal.

===============================================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber (1894-1961)
Go to Top of Page

alexsts
Starting Member

USA
13 Posts

Posted - 02/14/2014 :  17:51:36  Show Profile  Reply with Quote
quote:
Originally posted by Bustaz Kool

What are you actually trying to accomplish? Or is the issue that you want to generically modify any system table? Tell us what type of modification you want to make to the linked server and we can point you to the means of accomplishing the goal.

===============================================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber (1894-1961)


Generically I have old fashioned legacy application which is updating 3 systables. It worked fine on SQL 2000, 2005 and even to some extent on 2008. But now client want to upgrade from SQL 2005 to SQL 2012 and as I discovered updates to systables not allowed in general and for those 3 tables specifically. Nothing so far working to roll back that unwise change and allow us to continue to work with this newest SQL Server.

Alex

Edited by - alexsts on 02/14/2014 17:54:39
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1770 Posts

Posted - 02/24/2014 :  18:46:02  Show Profile  Reply with Quote
Which tables and what are you trying to update? There is probably some way to accomplish the same effect via another means. Without some more specific it is hard impossible to know how to proceed.

===============================================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber (1894-1961)
Go to Top of Page

alexsts
Starting Member

USA
13 Posts

Posted - 03/10/2014 :  09:42:37  Show Profile  Reply with Quote
quote:
Originally posted by Bustaz Kool

Which tables and what are you trying to update? There is probably some way to accomplish the same effect via another means. Without some more specific it is hard impossible to know how to proceed.



It is sysobjects and syscolumns.
Must do this because of certain specifics of the old application build in VB6.

Alex
Go to Top of Page

alexsts
Starting Member

USA
13 Posts

Posted - 03/10/2014 :  09:47:33  Show Profile  Reply with Quote
quote:
Originally posted by Bustaz Kool

Which tables and what are you trying to update? There is probably some way to accomplish the same effect via another means. Without some more specific it is hard impossible to know how to proceed.


And I would appreciate if you show me way to do same things without building new application. Company does not have resources to do complete revamp of main app and worse part that clients would not want to do it as well.They are happy with what they have right now. Only problem is SQL database. As all of us understand SQL 2005 will soon be out of support by Microsoft and how long it will work after that?
Thanks.

Alex
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000