Skip to main content

"Ad hoc updates to system catalogs are not allowed" - SQL Server

While working with linked server as it was filed and giving error as linked server was failing. I think it may be the issue with the username or password on which the linked server communicate. To troubleshoot an issue I have tried to change the password, I got best common error.

That's it :  "Ad hoc updates to system catalogs are not allowed"


Now I was going to change the "Allow updates" from the configuration as follows, But another error was waiting for me.
"Ad hoc updates to system catalogs are not supported"



You can see here when I was running the first script, it has raised error, but after changing the script with Override option the statement was executed. What you think? Have you received any this type of error?

Comments

  1. This appears to be a bug in SQL Server Management Studio.

    http://connect.microsoft.com/SQLServer/feedback/details/444560/editing-linked-server-properties-thorws-ad-hoc-updates-error

    Instead of using the GUI to create your linked server, use sp_addlinkedserver and sp_addlinkedsrvlogin

    ReplyDelete
  2. This does not look like a bug in the SSMS. Instead, it is most likely an incorrectly configured SQL Server instance.
    SQL Server has a configuration option - "Allow updates", which is deprecated since SQL 2005. The value of this option is set to 0 by default (which is correct). It looks like someone has set this to 1 on your test isntance.

    You can read about it in my post here: http://beyondrelational.com/blogs/nakul/archive/2011/02/14/ad-hoc-update-to-system-catalogs-is-not-supported-when-using-reconfigure.aspx

    I had also raised a Connect case, which you can read about here: http://beyondrelational.com/blogs/nakul/archive/2011/06/16/microsoft-connect-case-sp-configure-allow-updates-should-be-an-advanced-option-add-your-vote-to-fix.aspx

    ReplyDelete

Post a Comment