|
|
Error Message
Server: Msg 470, Level 16, State 1, Line 1
The synonym "<Synonym Name>" referenced synonym
"<Synonym Name>". Synonym chaining is not allowed.
Causes
A synonym is a database object that servers the purpose of providing an alternative name for another database object, referred to as the base object, that can exist on a local or remote server as well as the purpose of providing a layer of abstraction that protects a client application from changes made to the name or location of the base object.
|
A synonym belongs to a schema, and like other objects in a schema, the name of the synonym must be unique. References to synonyms are not schema-bound. Therefore, a synonym can be dropped at any time. However, dropping a synonym runs the risk of leaving dangling references to the synonym that was dropped. These references will only be found at run time.
One restriction when creating a synonym is that a synonym cannot be the base for another synonym, otherwise this error message will be generated.
To illustrate on how this error message can be encountered, here’s a script that creates a synonym on the current database referencing a table, the base object, from one of the tables on another database:
CREATE SYNONYM [dbo].[OldStudents] FOR [SchoolArchive].[dbo].[Students]
GO
Here’s another synonym that uses the previously defined synonym as its base object.
CREATE SYNONYM [dbo].[Alumni] FOR [dbo].[OldStudents]
GO
No error is generated yet as SQL Server will allow the creation of the synonym. The error will be encountered upon the use of the synonym because the binding between a synonym and its base object is by name only. All existence, type, and permissions checking on the base object is deferred until run time. Therefore, the base object can be modified, dropped or dropped and replaced by another object that has the same name as the original base object. Upon issuing a SELECT statement on the chained synonym, this error is generated:
SELECT * FROM [dbo].[Alumni]
Msg 470, Level 16, State 1, Line 1
The synonym "dbo.Alumni" referenced synonym "dbo.OldStudents". Synonym chaining is not allowed.
Solution / Work Around:
As mentioned earlier, one restriction when creating a synonym is that a synonym cannot be the base for another synonym. There are a few ways of overcoming this restriction. The first method is by creating a view instead of a synonym. This work around will only work if the base object of the synonym is a table or view and not a function or stored procedure. In the case of the second synonym defined earlier ([dbo].[Alumni]), instead of defining a synonym that uses the first synonym ([dbo].[OldStudents]), a view can be created instead:
CREATE VIEW [dbo].[Alumni]
AS
SELECT * FROM [dbo].[OldStudents]
GO
If a synonym is really required instead of a view, the second method of overcoming this error is to use the same base object used by the first synonym ([dbo].[OldStudents]) when creating the second synonym ([dbo].[Alumni]).
CREATE SYNONYM [dbo].[Alumni] FOR [SchoolArchive].[dbo].[Students]
GO
Lastly, the third method of overcoming this restriction and still be able to define a synonym that references another synonym is with the use of a view, as can be seen from the following script:
CREATE VIEW [dbo].[OldStudents2]
AS
SELECT * FROM [dbo].[OldStudents]
GO
CREATE SYNONYM [dbo].[Alumni] FOR [dbo].[OldStudents2]
GO
SELECT * FROM [dbo].[Alumni]
As mentioned earlier, this work around will only work if the base object used in the synonym is either a table or a view.
|