none
Error: Ad hoc updates to system catalog are not supported (when Setting up linked server)

    Question

  • Hi everyone,

    I am utilizing sql server 2008 to create a linked server to another sql server 2008 instance.

    I am using the following string to setup my linked server:

     

    EXEC master.dbo.sp_addlinkedserver 
    @server = N'Name of Linked Server', 
    @srvproduct=N'', 
    @provider=N'SQLNCLI', 
    @datasrc=N'Host\Insntance';

     When I go to set the login and username I get the following error 'ad hoc updates to system catalogs are not allowed'

    I ran sp_configure and saw that allow_updates was set to '1' so I'm not sure what the problem is here.

     Any help would be appreciated. Thanks!

    Thursday, November 10, 2011 8:42 PM

Answers

  • This could be a bug in SSMS.  Maybe try using sp_addlinkedsrvlogin e.g.

    EXEC

    master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'name', @locallogin = N'sa', @useself = N'false', @rmtuser = N'sa', @rmtpassword = N'password'

    GO

    Friday, November 11, 2011 1:52 AM