• Articolo

Risoluzione dei problemi relativi alle dipendenze SQL

In questo argomento vengono descritti i problemi comuni relativi alle dipendenze tra gli oggetti e le relative soluzioni.

La funzione a gestione dinamica sys.dm_sql_referenced_entities non restituisce le dipendenze a livello di colonna

La funzione di sistema sys.dm_sql_referenced_entities restituirà tutte le dipendenze a livello di colonna per i riferimenti associati a schema. La funzione restituirà ad esempio tutte le dipendenze a livello di colonna per una vista indicizzata poiché una vista indicizzata richiede l'associazione allo schema. Tuttavia, quando l'entità a cui si fa riferimento non è associata a schema, le dipendenze della colonna vengono restituite solo quando è possibile associare tutte le istruzioni in cui si fa riferimento alle colonne. Le istruzioni possono essere associate correttamente solo se tutti gli oggetti esistono al momento dell'analisi delle istruzioni. Se un'istruzione definita nell'entità non viene associata, le dipendenze della colonna non verranno restituite e la colonna referenced_minor_id restituirà 0. Quando le dipendenze della colonna non possono essere risolte, viene generato l'errore 2020. Questo errore non impedisce alla query di restituire le dipendenze a livello di oggetto.

Soluzione

Correggere gli errori identificati nel messaggio prima dell'errore 2020. Nell'esempio di codice seguente viene definita la vista Production.ApprovedDocuments nelle colonne Title, ChangeNumber e Status della tabella Production.Document. Viene eseguita una query sulla funzione di sistema sys.dm_sql_referenced_entities per gli oggetti e le colonne da cui dipende la vista ApprovedDocuments. Poiché la vista non viene creata utilizzando la clausola WITH SCHEMA_BINDING, è possibile modificare le colonne con riferimenti nella vista della tabella a cui si fa riferimento. Nell'esempio viene modificata la colonna ChangeNumber della tabella Production.Document rinominandola con TrackingNumber. Viene eseguita di nuovo una query sulla vista del catalogo per la vista ApprovedDocuments. Non è però possibile eseguire l'associazione a tutte le colonne definite nella vista. Vengono restituiti gli errori 207 e 2020 identificando il problema. Per risolvere il problema, è necessario modificare la vista in modo da riflettere il nuovo nome della colonna.

USE AdventureWorks;
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 query restituisce i messaggi di errore seguenti:

Messaggio 207, livello 16, stato 1, procedura ApprovedDocuments, riga 3

Il nome di colonna 'ChangeNumber' non è valido.

Messaggio 2020, livello 16, stato 1, riga 1

Le dipendenze restituite per l'entità "Production.ApprovedDocuments" non includono riferimenti a colonne. L'entità fa riferimento a un oggetto che non esiste oppure si è verificato un errore in una o più istruzioni dell'entità. Prima di eseguire la query, assicurarsi che l'entità non contenga errori e che tutti gli oggetti a cui fa riferimento esistano.

Nell'esempio seguente viene corretto il nome della colonna nella vista.

USE AdventureWorks;
GO
ALTER VIEW Production.ApprovedDocuments
AS
    SELECT Title,TrackingNumber, Status
    FROM Production.Document
    WHERE Status = 2;
GO

La colonna is_ambiguous restituisce valori incoerenti per le funzioni definite dall'utente

È possibile che il valore restituito nella colonna is_ambiguous sia incoerente per le funzioni definite dall'utente. La colonna is_ambiguous nella vista del catalogo sys.sql_expression_dependencies e nella funzione a gestione dinamica sys.dm_sql_referenced_entities indica che il riferimento all'entità è ambiguo, ovvero l'entità può essere risolta in fase di esecuzione in una funzione definita dall'utente, in un tipo definito dall'utente (UDT) o in un riferimento XQuery a una colonna di tipo xml. A seconda di come viene fatto riferimento alla funzione definita dall'utente, il tipo di entità può o non può essere chiaro, facendo sì che la colonna is_ambiguous venga impostata su 1 (true) in un caso e su 0 (false) nell'altro. Si consideri ad esempio la seguente stored procedure.

CREATE PROCEDURE dbo.p1 
AS
    SELECT Sales.GetOrder() FROM t1;
    SELECT Sales.GetOrder();

Nella prima istruzione SELECT non è chiaro se Sales.GetOrder() sia una funzione definita dall'utente nello schema Sales o una colonna denominata Sales di tipo definito dall'utente con un metodo denominato GetOrder(). In questo caso, la colonna is_ambiguous verrà impostata su 1 per l'entità Sales.GetOrder() a cui si fa riferimento. Nella seconda istruzione SELECT il riferimento a Sales.GetOrder() è chiaro. In base alla sintassi, può essere solo un riferimento a una funzione definita dall'utente. In questo caso, la colonna is_ambiguous viene impostata su 0. Questo comportamento può far sembrare che il valore restituito nella colonna is_ambiguous sia incoerente. Capire il modo in cui viene determinato il valore della colonna is_ambiguous può chiarire i valori restituiti.

La colonna is_ambiguous viene impostata su 0 (false) quando:

  • Risulta chiaro che il riferimento è a una funzione definita dall'utente, ovvero la query viene associata a una funzione definita dall'utente e un metodo UDT della colonna o una colonna di tipo xml con tale nome non esiste.

  • Risulta chiaro che il riferimento è a un metodo UDT della colonna, ovvero una colonna con tale metodo UDT esiste e una funzione definita dall'utente o una colonna di tipo xml con tale nome non esiste.

La colonna is_ambiguous viene impostata su 1 (true) quando:

  • Una funzione definita dall'utente, un metodo UDT della colonna o una colonna di tipo xml con il nome a cui si fa riferimento non esiste.

  • Il nome a cui si fa riferimento esiste per più entità. Ad esempio, una funzione definita dall'utente e un metodo UDT della colonna hanno lo stesso nome.

Per le entità che sono ambigue in natura, è possibile che le colonne referenced_database_name e referenced_schema_name non siano valide. Si consideri ad esempio la seguente funzione definita dall'utente:

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;

Le colonne referenced_database_name e referenced_schema_name non saranno valide per la funzione a causa delle chiamate al metodo UDT hierarchyid. Non è chiaro che i riferimenti a h.empid.GetDescendant e h1.empid.GetAncestor siano riferimenti a un'entità mediante un nome composto da tre parti (database.schema.object) o a un metodo UDT (table.column.method).

Soluzione

Non è richiesto alcun intervento da parte dell'utente.

La colonna referenced_id non viene restituita per le dipendenze tra database

La colonna referenced_id non viene mai risolta per i riferimenti tra database nella vista del catalogo sys.sql_expression_dependencies. Il nome del database e il nome dello schema vengono registrati solo quando il nome viene specificato in modo esplicito. Se ad esempio si specifica MyDB.MySchema.MyTable vengono registrati il nome del database e il nome dello schema. Se invece si specifica MyDB..MyTable, viene registrato solo il nome del database.

La colonna referenced_id viene restituita per i riferimenti tra database nella funzione di sistema sys.dm_sql_referenced_entities solo quando è possibile associare correttamente l'entità a cui si fa riferimento. L'associazione potrebbe non riuscire per vari motivi che includono le cause seguenti:

  • Il database non è in linea.

  • L'entità a cui si fa riferimento non esiste nel database.

Soluzione

Verificare che il database sia in linea e che l'entità a cui si fa riferimento esista nel database.

La colonna referenced_id è NULL per le entità a cui si fa riferimento all'interno del database

La funzione di sistema sys.dm_sql_referenced_entities e la vista di sistema sys.sql_expression_dependencies restituiscono l'ID dell'entità associata a schema a cui si fa riferimento. Tuttavia, la colonna referenced_id sarà NULL per i riferimenti non associati a schema all'interno del database quando non è possibile determinare l'ID dell'entità a cui si fa riferimento. Questo può verificarsi nei seguenti casi:

  • L'entità a cui si fa riferimento non esiste nel database.

  • La risoluzione dei nomi è dipendente dal chiamante. In questo caso, la colonna is_caller_dependent viene impostata su 1.

Soluzione

Verificare che l'entità a cui si fa riferimento esista nel database. Creare l'entità se non viene trovata oppure, se l'entità esiste, verificare che vengano soddisfatti i requisiti seguenti:

  • L'ortografia del nome dell'entità a cui si fa riferimento è corretta.

  • Il nome specificato soddisfa i requisiti delle regole di confronto del database. Se nel database vengono utilizzate regole di confronto con distinzione tra maiuscole e minuscole, il nome specificato deve corrispondere all'esatta combinazione di maiuscole e minuscole del nome dell'oggetto. Ad esempio, l'ID di un oggetto denominato SalesHistory non verrà trovato in un database con regole di confronto con distinzione tra maiuscole e minuscole se si specifica saleshistory.

  • Venga specificato il nome dello schema dell'oggetto. È necessario specificare un nome in due parti (schema_name.object_name) se l'oggetto non è presente nello schema predefinito del chiamante, nello schema sys o nello schema dbo.

Modificare la definizione dell'entità di riferimento per soddisfare i requisiti precedenti.

Se l'entità a cui si fa riferimento è dipendente dal chiamante, modificare la definizione dell'entità di riferimento specificando un nome in due parti per l'entità a cui si fa riferimento. Per ulteriori informazioni sui riferimenti dipendenti dal chiamante, vedere Report sulle dipendenze SQL.

Le informazioni sulle dipendenze non vengono restituite per gli oggetti nel database master

Per le entità definite dall'utente create nel database master vengono create e gestite le dipendenze SQL. Se per un'entità non vengono restituite le dipendenze SQL, effettuare le operazioni seguenti:

  • Assicurarsi che l'entità sia un tipo valido per il rilevamento delle dipendenze

    Le informazioni sulle dipendenze non vengono rilevate per tutti gli oggetti utente. Per un elenco dei tipi di entità per i quali vengono create e gestite le informazioni sulle dipendenze, vedere Informazioni sulle dipendenze SQL.

  • Assicurarsi che l'entità non sia contrassegnata come oggetto di sistema.

    Eseguire una query sulla colonna is_ms_shipped per l'entità nella vista del catalogo sys.objects. Se questa colonna è impostata su 1, l'entità è un oggetto di sistema fornito con SQL Server o un oggetto definito dall'utente modificato per riprodurre un oggetto di sistema impostando manualmente questa colonna su 1.

Soluzione

Se l'oggetto è di un tipo che non è supportato, le informazioni sulle dipendenze non saranno disponibili.

Le dipendenze sugli oggetti di sistema non vengono rilevate. Se l'entità è definita dall'utente, reimpostare la colonna is_ms_shipped su 0 se si desidera creare e gestire le dipendenze per l'entità in SQL Server.