| Author |
Topic |
|
ranvir_2k
Posting Yak Master
180 Posts |
 Posted - 2013-12-18 : 05:12:39
|
| 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 allowedDoes 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
Master Smack Fu Yak Hacker
2242 Posts |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
|
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2013-12-18 : 05:38:43
|
| allow updates is set to 1 under run_value but I still have this issue. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-18 : 06:31:22
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
alexsts
Starting Member
13 Posts |
Posted - 2014-02-11 : 17:48:25
|
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 2012Alex |
|
|
|
alexsts
Starting Member
13 Posts |
Posted - 2014-02-11 : 18:05:48
|
quote:
Originally posted by visakh16
may be this?http://support.microsoft.com/kb/2733673------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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.ThanksAlex |
|
|
|
alexsts
Starting Member
13 Posts |
Posted - 2014-02-12 : 10:03:51
|
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.ThanksAlex |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2014-02-13 : 19:23:40
|
| 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) |
|
|
|
alexsts
Starting Member
13 Posts |
Posted - 2014-02-14 : 17:51:36
|
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 |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2014-02-24 : 18:46:02
|
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) |
|
|
|
alexsts
Starting Member
13 Posts |
Posted - 2014-03-10 : 09:42:37
|
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 |
|
|
|
alexsts
Starting Member
13 Posts |
Posted - 2014-03-10 : 09:47:33
|
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 |
|
|
|
|
Linked Server - Ad hoc updates not allowed