Dépannage de dépendances SQL
Cette rubrique décrit les problèmes courants de dépendance objet et leurs solutions.
La fonction de gestion dynamique sys.dm_sql_referenced_entities ne retourne pas de dépendances au niveau des colonnes
La fonction système sys.dm_sql_referenced_entities signale toute dépendance au niveau des colonnes pour les références liées au schéma. Par exemple, la fonction signale toutes les dépendances au niveau des colonnes pour une vue indexée car une vue indexée requiert une liaison de schéma. Toutefois, lorsque l'entité référencée n'est pas liée au schéma, les dépendances de colonne sont signalées uniquement lorsque toutes les instructions dans lesquelles les colonnes sont référencées peuvent être liées. Les instructions ne peuvent être correctement liées que si tous les objets existent au moment de l'analyse des instructions. Si la liaison échoue dans une instruction définie dans l'entité, les dépendances de colonne ne sont pas signalées et la colonne referenced_minor_id retourne 0. Lorsque les dépendances de colonne ne peuvent pas être résolues, l'erreur 2020 est générée. Cette erreur n'empêche pas la requête de retourner des dépendances au niveau des objets.
Solution
Corrigez toutes les erreurs identifiées dans le message avant l'erreur 2020. Par exemple, dans l'exemple de code suivant, la vue Production.ApprovedDocuments est définie dans les colonnes Title, ChangeNumber et Status de la table Production.Document. La fonction système sys.dm_sql_referenced_entities est interrogée pour les objets et les colonnes dont dépend la vue ApprovedDocuments. Étant donné que la vue n'est pas créée à l'aide de la clause WITH SCHEMA_BINDING, les colonnes référencées dans la vue peuvent être modifiées dans la table référencée. L'exemple modifie la colonne ChangeNumber de la table Production.Document en la renommant TrackingNumber. L'affichage catalogue est interrogé de nouveau pour la vue ApprovedDocuments ; toutefois, il ne peut pas être lié à toutes les colonnes définies dans la vue. Les erreurs 207 et 2020 sont retournées pour identifier le problème. Pour résoudre le problème, il est nécessaire de modifier la vue de façon à refléter le nouveau nom de la colonne.
USE AdventureWorks2008R2;
GO
CREATE VIEW Production.ApprovedDocuments
AS
SELECT Title, ChangeNumber, Status
FROM Production.Document
WHERE Status = 2;
GO
SELECT referenced_schema_name AS schema_name
,referenced_entity_name AS table_name
,referenced_minor_name AS referenced_column
FROM sys.dm_sql_referenced_entities ('Production.ApprovedDocuments', 'OBJECT');
GO
EXEC sp_rename 'Production.Document.ChangeNumber', 'TrackingNumber', 'COLUMN';
GO
SELECT referenced_schema_name AS schema_name
,referenced_entity_name AS table_name
,referenced_minor_name AS referenced_column
FROM sys.dm_sql_referenced_entities ('Production.ApprovedDocuments', 'OBJECT');
GO
La requête retourne les messages d'erreur suivants.
Msg 207, Level 16, State 1, Procedure ApprovedDocuments, Line 3
Invalid column name 'ChangeNumber'.
Msg 2020, Level 16, State 1, Line 1
The dependencies reported for entity "Production.ApprovedDocuments" do not include references to columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity. Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.
L'exemple suivant corrige le nom de colonne dans la vue.
USE AdventureWorks2008R2;
GO
ALTER VIEW Production.ApprovedDocuments
AS
SELECT Title,TrackingNumber, Status
FROM Production.Document
WHERE Status = 2;
GO
Les dépendances au niveau des colonnes ne seront pas retournées pour les instructions dans les procédures stockées qui contiennent des jointures aux tables temporaires. Pour les procédures stockées composées de plusieurs instructions, les dépendances au niveau des colonnes sont retournées pour les instructions qui n'ont pas de jointures à une table temporaire. Les instructions qui joignent une table temporaire n'auront pas de fonctionnalités de création de rapports sur les dépendances au niveau des colonnes.
La colonne is_ambiguous signale des valeurs incohérentes pour des fonctions définies par l'utilisateur
La valeur signalée dans la colonne is_ambiguous peut sembler incohérente pour des fonctions définies par l'utilisateur. La colonne is_ambiguous dans l'affichage catalogue sys.sql_expression_dependencies et la fonction dynamique sys.dm_sql_referenced_entities indiquent que la référence à l'entité est ambiguë. En d'autres termes, l'entité peut être convertie au moment de l'exécution en une fonction définie par l'utilisateur, un type défini par l'utilisateur ou une référence XQuery à une colonne de type xml. En fonction de la manière dont la fonction définie par l'utilisateur est référencée, le type d'entité peut ou non être clair. La colonne is_ambiguous peut alors prendre la valeur 1 (vrai) dans un cas et la valeur 0 (faux) dans un autre. Prenons l'exemple de la procédure stockée ci-dessous.
CREATE PROCEDURE dbo.p1
AS
SELECT Sales.GetOrder() FROM t1;
SELECT Sales.GetOrder();
Dans la première instruction SELECT, il n'est pas facile de déterminer si Sales.GetOrder() est une fonction définie par l'utilisateur dans le schéma Sales ou une colonne nommée Sales de type UDT avec une méthode nommée GetOrder(). Dans ce cas, la colonne is_ambiguous prend la valeur 1 pour l'entité référencée Sales.GetOrder(). Dans la deuxième instruction SELECT, la référence à Sales.GetOrder() est claire ; d'après la syntaxe, il peut s'agir uniquement d'une référence à une fonction définie par l'utilisateur. Dans ce cas, la colonne is_ambiguous prend la valeur 0. Ce comportement peut faire apparaître que la valeur signalée dans la colonne is_ambiguous est incohérente. Comprendre comment est déterminée la valeur de la colonne is_ambiguous permet de clarifier les valeurs signalées.
La colonne is_ambiguous prend la valeur 0 (faux) dans les cas suivants :
Il est clair qu'il est fait référence à une fonction définie par l'utilisateur. En d'autres termes, la requête est liée à une fonction définie par l'utilisateur et une méthode de type UDT de colonne ou une colonne de type xml portant ce nom n'existe pas.
Il est clair qu'il est fait référence à une méthode UDT de colonne. Autrement dit, une colonne avec cette méthode UDT existe et une fonction définie par l'utilisateur ou colonne de type xml portant ce nom n'existe pas.
La colonne is_ambiguous prend la valeur 1 (vrai) lorsque :
Une fonction définie par l'utilisateur, une méthode UDT de colonne ou une colonne de type xml portant le nom référencé n'existe pas.
Le nom référencé existe pour plusieurs entités. Par exemple, une fonction définie par l'utilisateur et une méthode UDT de colonne ont le même nom.
Pour les entités qui sont ambiguës par essence, il est possible que les colonnes referenced_schema_name et referenced_database_name ne soient pas valides. Considérons par exemple la fonction définie par l'utilisateur suivante :
CREATE FUNCTION GetNextEmpHierarchyId (@empname varchar(25))
RETURNS hierarchyid
AS
BEGIN
RETURN
(
SELECT h.empid.GetDescendant((SELECT MAX(h1.empid)
FROM dbo.Employees AS h1
WHERE h1.empid.GetAncestor(1) = h.empid), NULL)
FROM dbo.Employees AS h
WHERE h.empname = @empname
)
END;
Les colonnes referenced_database_name et referenced_schema_name ne sont pas valides pour la fonction en raison des appels de méthode UDT hierarchyid. Il n'est pas facile de déterminer si les références à h.empid.GetDescendant et à h1.empid.GetAncestor sont des références à une entité qui utilise un nom en trois parties (database.schema.object) ou à une méthode UDT (table.column.method).
Solution
Aucune intervention de l'utilisateur n'est requise.
La colonne referenced_id n'est pas signalée pour les dépendances de bases de données croisées
La colonne referenced_id n'est jamais résolue pour les références des base de données croisées dans l'affichage catalogue sys.sql_expression_dependencies. Le nom de la base de données et le nom du schéma sont enregistrés uniquement lorsque le nom est explicitement spécifié. Par exemple, lorsque MyDB.MySchema.MyTable est spécifié, les noms de la base de données et du schéma sont enregistrés. Toutefois, lorsque MyDB..MyTable est spécifié, seul le nom de la base de données est enregistré.
referenced_id est signalé pour les références de bases de données croisées dans la fonction système sys.dm_sql_referenced_entities uniquement lorsque l'entité référencée peut être liée. La liaison peut échouer pour plusieurs raisons, notamment les suivantes :
La base de données est hors connexion.
L'entité référencée n'existe pas dans la base de données.
Solution
Vérifiez que la base de données est en ligne et que l'entité référencée y figure.
La colonne referenced_id a la valeur Null pour les entités référencées dans la base de données
La fonction système sys.dm_sql_referenced_entities et la vue système sys.sql_expression_dependencies signalent l'ID de toute entité référencée liée à un schéma. Toutefois, la colonne referenced_id a la valeur NULL pour les références non liées au schéma dans la base de données lorsque l'ID de l'entité référencée ne peut pas être déterminé. Cela peut se produire dans les cas suivants :
L'entité référencée n'existe pas dans la base de données.
La résolution de noms dépend de l'appelant. Dans ce cas, la colonne is_caller_dependent prend la valeur 1.
Solution
Vérifiez que l'entité référencée existe dans la base de données. Créez l'entité si elle est introuvable ou, si l'entité existe, assurez-vous que les conditions suivantes sont remplies :
Le nom de l'entité référencée est correctement orthographié.
Le nom spécifié respecte les spécifications de classement de la base de données. Si la base de données utilise un classement qui respecte la casse, le nom spécifié doit correspondre à la casse exacte du nom d'objet. Par exemple, l'ID d'un objet nommé SalesHistory n'est pas trouvé dans une base de données avec un classement qui respecte la casse s'il est spécifié sous la forme saleshistory.
Le nom de schéma de l'objet est spécifié. Un nom en deux parties (schema_name.object_name) est requis si l'objet n'est pas dans le schéma par défaut de l'appelant, le schéma sys ou le schéma dbo.
Modifiez la définition de l'entité de référence de sorte qu'elle réponde aux exigences ci-dessus.
Si l'entité référencée dépend de l'appelant, modifiez la définition de l'entité de référence en spécifiant un nom en deux parties pour l'entité référencée. Pour plus d'informations sur les références dépendantes de l'appelant, consultez Signalement des dépendances SQL.
Les informations de dépendance ne sont pas signalées pour les objets dans la base de données MASTER
Les dépendances SQL sur des entités définies par l'utilisateur créées dans la base de données master sont créées et conservées. Si les dépendances SQL pour une entité ne sont pas signalées, procédez comme suit :
Assurez-vous que l'entité est d'un type valide pour le suivi des dépendances
Les informations de dépendance ne font pas l'objet d'un suivi pour tous les objets utilisateur. Pour obtenir une liste des types d'entité pour lesquels des informations de dépendance sont créées et conservées, consultez Description des dépendances SQL.
Assurez-vous que l'entité n'est pas marquée comme objet système.
Interrogez la colonne is_ms_shipped pour l'entité dans l'affichage catalogue sys.objects. Si cette colonne a la valeur 1, l'entité est soit un objet système fourni avec SQL Server, soit un objet défini par l'utilisateur qui est modifié pour imiter un objet système en affectant manuellement la valeur 1 à cette colonne.
Solution
Si l'objet est d'un type non pris en charge, les informations de dépendance ne sont pas disponibles.
Les dépendances sur les objets système ne sont pas suivies. Si l'entité est définie par l'utilisateur, la colonne is_ms_shipped column doit être réinitialisée à la valeur 0 si vous souhaitez que SQL Server crée et conserve les dépendances sur l'entité.