Sql-server – Synonym with same name as linked server

linked-serversql serversynonyms

I have a database that is used for reporting services. This database was created by a contractor who is no longer around. Buried all throughout the database are references to a single table on a linked server. The content on that linked server is being moved, and the server being retired.

Rather than hunting around for all of the linked server references and updating them to point at the new linked server, or changing them to use a synonym name, would it be possible (or acceptable) to create the new linked server, then add a synonym that uses the same name as the current fully qualified linked server reference, but points to the new linked server? This would allow me to leave all database code intact and minimize disruption to business users.

Here's what I'm thinking. Current linked server is SRAPP. All queries using the linked server point to OPub.dbo.ADE. 

 exec sp_addlinkedserver @server=SQLProd1

Then

CREATE SYNONYM [SRAPP.OPub.dbo.ADE]
FOR SQLProd1.OPub.dbo.ADE
GO

Finally,

exec sp_dropserver 'SRAPP'

Will SQL Server get confused by a synonym name that looks identical to a linked server reference?

Best Answer

Did you try your idea? What were your results? Yes, you can create a synonym as:

CREATE SYNONYM [SRAPP.OPub.dbo.ADE]
FOR SQLProd1.OPub.dbo.ADE
GO

But you cannot use a synonym for a linked server. Also, as of SQL Server 2016 the following limitation exists:

Four-part names for function base objects are not supported.

Recommendation: Drop and Recreate the Linked Server 'SRAPP'. You can help yourself by scripting out the existing definition. Drop the existing linked server and recreate it using the new server name.

As of SQL Server 2016 synonyms can only be defined for:

  • Assembly (CLR) Stored Procedure
  • Assembly (CLR) Table-valued Function
  • Assembly (CLR) Scalar Function
  • Assembly Aggregate (CLR) Aggregate
  • Functions Replication-filter-procedure
  • Extended Stored Procedure SQL
  • Scalar Function
  • SQL Table-valued Function
  • SQL Inline-table-valued Function
  • SQL Stored Procedure
  • View
  • Table (User-defined)

Yes, there are many things that we can conceive about synonyms, but they do not yet exist.

Related Question