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?