|Product Name||SQL Server|
|Message Text||Column '%ls.%.*ls' cannot be referenced in the OUTPUT clause because the column definition contains a subquery or references a function that performs user or system data access. A function is assumed by default to perform data access if it is not schemabound. Consider removing the subquery or function from the column definition or removing the column from the OUTPUT clause.|
To prevent nondeterministic behavior, the OUTPUT clause cannot reference a column from a view or inline table-valued function when that column is defined by one of the following methods:
A user-defined function that performs user or system data access, or is assumed to perform such access.
A computed column that contains a user-defined function that performs user or system data access in its definition.
View Column Defined by a Subquery
The following example creates a view that uses a subquery in the select list to define the column
State. An UPDATE statement then references the
State column in the OUTPUT clause and fails because ob the subquery in the select list.
USE AdventureWorks2012; GO CREATE VIEW dbo.V1 AS SELECT City, -- subquery to return the State name (SELECT Name FROM Person.StateProvince AS sp WHERE sp.StateProvinceID = a.StateProvinceID) AS State FROM Person.Address AS a; GO --Reference the State column in the OUTPUT clause of an UPDATE statement UPDATE dbo.V1 SET City = City + 'Test' OUTPUT deleted.City, deleted.State, inserted.City, inserted.State WHERE State = 'Texas'; GO
View Column Defined by a Function
The following example creates a view that uses the data accessing, scalar function
dbo.ufnGetStock in the select list to define the column
CurrentInventory. An UPDATE statement then references the
CurrentInventory column in the OUTPUT clause .
USE AdventureWorks2012; GO CREATE VIEW Production.ReorderLevels AS SELECT ProductID, ProductModelID, ReorderPoint, dbo.ufnGetStock(ProductID) AS CurrentInventory FROM Production.Product; GO UPDATE Production.ReorderLevels SET ReorderPoint += CurrentInventory OUTPUT deleted.ReorderPoint, deleted.CurrentInventory, inserted.ReorderPoint, inserted.CurrentInventory WHERE ProductModelID BETWEEN 75 and 80;
Error 4186 can be corrected in one of the following ways:
Use joins instead of subqueries to define the column in the view or function. For example, you can rewrite the view
USE AdventureWorks2012; GO CREATE VIEW dbo.V1 AS SELECT City, sp.Name AS State FROM Person.Address AS a JOIN Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID;
Examine the definition of the user-defined function. If the function does not perform user or system data access, alter the function to include the WITH SCHEMABINDING clause.
Remove the column from the OUTPUT clause.