Lesson Learned #507:Error 4429 – View or function ‘XYZ’ contains a self-reference.

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

This last week, we worked on a service request where our customer got the following error message: "Msg 4429, Level 16, State 1, Procedure Viewd, Line 2 [Batch Start Line 9]
View or function 'Viewa' contains a self-reference. Views or functions cannot reference themselves directly or indirectly. Msg 4413, Level 16, State 1, Line 10 Could not use view or function 'viewB' because of binding errors.". Following I would like to share the lessons learned about this error.

 

This error means that a view or function has been defined in such a way that it references itself, either directly or indirectly. This can lead to infinite loops and performance issues, which SQL Server prevents by issuing this error.

 

For example, CREATE VIEW ViewA AS SELECT * FROM ViewA or indirectly using the following code. 

 

CREATE VIEW ViewA AS SELECT * FROM ViewB; CREATE VIEW ViewB AS SELECT * FROM ViewA;

 

In some situations, we can also observe a view (ViewA) that calls a function, and the definition of that function calls the same view (ViewA). Use these DMVs we could check for indirect references that might be causing a circular dependency. 

 

SELECT referencing_object_name = o1.name, referencing_object_type = o1.type_desc, referenced_object_name = o2.name, referenced_object_type = o2.type_desc FROM sys.sql_expression_dependencies sed JOIN sys.objects o1 ON sed.referencing_id = o1.object_id JOIN sys.objects o2 ON sed.referenced_id = o2.object_id WHERE o1.name = 'ViewA' OR o2.name = 'ViewA';

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.