4

I'm about to upgrade my SQL Server 2012 instance to SQL Server 2014.

I've cloned the host Windows VM and renamed it from foo-2012 to foo-2014. On restart, SQL Server instance noticed this as updated its own name, so I can now log into it as foo-2014. All good.

Unfortunately the (single) entry in sys.servers is still foo-2012 which means that running

SELECT * 
FROM [foo-2012].[barDB].[dbo].tFooBarTable

fails with:

Could not find server 'RW-DB-2014' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Fine.

I run EXEC sp_addlinkedserver 'foo-2014', 'SQL Server' and get an entry.

But now the new entry has isLinked=1 (whereas the existing entry has isLinked=0).

Documentation indicates that this setting is important (especially to my app which has very strong opinions on Distr.Trans. :( )

I can't edit/add to/modify sys.servers directly. Any attempt to do so gives:

Ad hoc updates to system catalogs are not allowed.

I remove the new entry (EXEC sp_dropserver 'foo-2014') which succeeds, and try using EXEC sp_addserver 'foo-2014', 'local' which reports

The server 'foo-2014' already exists

Immediately re-running the drop then reports

The server 'RW-DB-2014' does not exist. Use sp_helpserver to show available servers.

How do I fix this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Brondahl
  • 7,402
  • 5
  • 45
  • 74

1 Answers1

8

You need to first remove the old server foo-2012 so that you have no rows in sys.servers, and then do the add with 'local':

EXEC sp_dropserver 'foo-2012';
GO
EXEC sp_addserver 'foo-2014', 'local'; 
GO

Then you will need to restart the MSSQLSERVER service in order for that change to take effect, as per the documentation for sp_addserver :

The local definition takes effect only after the Database Engine is restarted.

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
  • We have a winner :) Note that you also need to actively enable DATA ACCESS after that: http://stackoverflow.com/questions/629677/sql-server-not-configured-for-data-access do you want to update your answer to be complete, then I'll accept it. – Brondahl Sep 22 '16 at 12:29
  • Also seemed to need a SQL restart, though that seems unsurprising. – Brondahl Sep 22 '16 at 13:18
  • Sure, will update when I get home. I don't remember the data access part but the reboot makes sense. – Solomon Rutzky Sep 22 '16 at 13:24
  • @Brondahl I just updated to include example code and a note about the restart part (that I forgot about, sorry, but it has been 9 years almost since I ran into this ;-). I did not add the part about enabling `DATA ACCESS` since I don't think that is actually required. My local server does not have it enabled, and I would think that `sp_addserver` would set it to enabled if it were necessary. I think it was the reboot that was needed. – Solomon Rutzky Sep 22 '16 at 14:35
  • @Brondahl That seems plausible. The comment's there for future users anyway, so they can check it if they want to. – Brondahl Sep 22 '16 at 16:17