Hi!
I’m currently upcycling the AdventureWorks model from the e-learning for the second time. During my first attempt, I encountered some error messages during the creation process. I ignored them back then because I mainly wanted to get the upcycler up and running.
I decided to take a closer look at the errors and came across errors in the views, procedures, and triggers. I added two examples below.
I’m not looking for updated/solved versions of the template. I’m mainly interested in understanding the root cause of these errors. Could they be related to the AdventureWorks model, to the Upcycler itself, or perhaps a combination of both?
Procedure
An unknown error occurred while executing '
/* Create or alter stored procedure uspGetManagerEmployees. */
create or alter procedure "uspGetManagerEmployees"
(
@BusinessEntityID "gen_15"
)
as
begin
-- Do not count affected rows for performance
SET NOCOUNT ON;
--control_proc_id: uspGetManagerEmployees
--template_id: uspGetManagerEmployees
--prog_object_item_id: uspGetManagerEmployees
--template_description: Definition for Stored Procedure uspGetManagerEmployees
SET NOCOUNT ON;
-- Use recursive query to list out all Employees required for a particular Manager
WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel]) -- CTE name and columns
AS (
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], 0 -- Get the initial list of Employees for Manager n
FROM [Employee] e
INNER JOIN [Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
WHERE e.[BusinessEntityID] = @BusinessEntityID
UNION ALL
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], [RecursionLevel] + 1 -- Join recursive member to anchor
FROM [Employee] e
INNER JOIN [EMP_cte]
ON e.[OrganizationNode].GetAncestor(1) = [EMP_cte].[OrganizationNode]
INNER JOIN [Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
)
-- Join back to Employee to return the manager name
SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[OrganizationNode].ToString() as [OrganizationNode], p.[FirstName] AS 'ManagerFirstName', p.[LastName] AS 'ManagerLastName',
[EMP_cte].[BusinessEntityID], [EMP_cte].[FirstName], [EMP_cte].[LastName] -- Outer select from the CTE
FROM [EMP_cte]
INNER JOIN [Employee] e
ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
INNER JOIN [Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
ORDER BY [RecursionLevel], [EMP_cte].[OrganizationNode].ToString()
OPTION (MAXRECURSION 25)
end
'.
Error: 'Cannot call methods on nvarchar.'.
Trigger
An unknown error occurred while executing '
/* Create or alter insert trigger SalesOrderDetail_ti to table 'SalesOrderDetail'. */
create or alter trigger "SalesOrderDetail_ti"
on "SalesOrderDetail"
for insert
as
begin
/* If no rows were modified exit trigger */
if @@rowcount = 0
return
if not exists(select 1 from inserted)
return
-- Do not count affected rows for performance
set nocount on;
--control_proc_id: SalesOrderDetail_ti
--template_id: iduSalesOrderDetail
--prog_object_item_id: iduSalesOrderDetail
--template_description: iduSalesOrderDetail
BEGIN
DECLARE @Count int;
SET @Count = @@ROWCOUNT;
IF @Count = 0
RETURN;
SET NOCOUNT ON;
BEGIN TRY
-- If inserting or updating these columns
IF UPDATE([ProductID]) OR UPDATE([OrderQty]) OR UPDATE([UnitPrice]) OR UPDATE([UnitPriceDiscount])
-- Insert record into TransactionHistory
BEGIN
INSERT INTO [TransactionHistory]
([ProductID]
,[ReferenceOrderID]
,[ReferenceOrderLineID]
,[TransactionType]
,[TransactionDate]
,[Quantity]
,[ActualCost])
SELECT
inserted.[ProductID]
,inserted.[SalesOrderID]
,inserted.[SalesOrderDetailID]
,'S'
,GETDATE()
,inserted.[OrderQty]
,inserted.[UnitPrice]
FROM inserted
INNER JOIN [SalesOrderHeader]
ON inserted.[SalesOrderID] = [SalesOrderHeader].[SalesOrderID];
UPDATE [Person]
SET [Demographics].modify('declare default element namespace
"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
replace value of (/IndividualSurvey/TotalPurchaseYTD)[1]
with data(/IndividualSurvey/TotalPurchaseYTD)[1] + sql:column ("inserted.LineTotal")')
FROM inserted
INNER JOIN [SalesOrderHeader] AS SOH
ON inserted.[SalesOrderID] = SOH.[SalesOrderID]
INNER JOIN [Customer] AS C
ON SOH.[CustomerID] = C.[CustomerID]
WHERE C.[PersonID] = [Person].[BusinessEntityID];
END;
-- Update SubTotal in SalesOrderHeader record. Note that this causes the
-- SalesOrderHeader trigger to fire which will update the RevisionNumber.
UPDATE [SalesOrderHeader]
SET [SalesOrderHeader].[SubTotal] =
(SELECT SUM([SalesOrderDetail].[LineTotal])
FROM [SalesOrderDetail]
WHERE [SalesOrderHeader].[SalesOrderID] = [SalesOrderDetail].[SalesOrderID])
WHERE [SalesOrderHeader].[SalesOrderID] IN (SELECT inserted.[SalesOrderID] FROM inserted);
UPDATE [Person]
SET [Demographics].modify('declare default element namespace
"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
replace value of (/IndividualSurvey/TotalPurchaseYTD)[1]
with data(/IndividualSurvey/TotalPurchaseYTD)[1] - sql:column("deleted.LineTotal")')
FROM deleted
INNER JOIN [SalesOrderHeader]
ON deleted.[SalesOrderID] = [SalesOrderHeader].[SalesOrderID]
INNER JOIN [Customer]
ON [Customer].[CustomerID] = [SalesOrderHeader].[CustomerID]
WHERE [Customer].[PersonID] = [Person].[BusinessEntityID];
END TRY
BEGIN CATCH
EXECUTE [dbo].[uspPrintError];
-- Rollback any active or uncommittable transactions before
-- inserting information in the ErrorLog
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE [dbo].[uspLogError];
END CATCH;
END;
end
'.
Error: 'XQuery [Person.Demographics.modify()]: The target of 'replace value of' must be a non-metadata attribute or an element with simple typed content, found 'element({http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey}:TotalPurchaseYTD,xdt:untyped) ?''.