Error on table rename referencing view with wildcard
fabianc2k
Posts: 8
Using latest version, SQL Refactor generates this change script (A) for this view (B) when renaming a base table:
(A) The change script
ALTER VIEW dbo.R_MonthlyReceivingTotalsWithNames
AS
SELECT dbo.Disciplines.Name AS DisciplineName, PayrollCostCentres.HealthServiceName, PayrollCostCentres.MajorGroupName AS PayrollMajorGroupName,
PayrollCostCentres.MinorGroupName AS PayrollMinorGroupName, PayrollCostCentres.CostCentreName AS PayrollCostCentreName,
ReceivingCostCentres.MajorGroupName AS ReceivingMajorGroupName, ReceivingCostCentres.MinorGroupName AS ReceivingMinorGroupName,
ReceivingCostCentres.CostCentreName AS ReceivingCostCentreName, dbo.R_MonthlyReceivingTotals
FROM dbo.R_MonthlyReceivingTotals INNER JOIN
dbo.Disciplines ON dbo.R_MonthlyReceivingTotals.ProductCategoryID = dbo.Disciplines.ProductCategoryID AND
dbo.R_MonthlyReceivingTotals.DisciplineID = dbo.Disciplines.DisciplineID INNER JOIN
dbo.CostCentresWithNames AS PayrollCostCentres ON
dbo.R_MonthlyReceivingTotals.HealthServiceID = PayrollCostCentres.HealthServiceID AND
dbo.R_MonthlyReceivingTotals.PayrollMajorGroupID = PayrollCostCentres.MajorGroupID AND
dbo.R_MonthlyReceivingTotals.PayrollMinorGroupID = PayrollCostCentres.MinorGroupID AND
dbo.R_MonthlyReceivingTotals.PayrollCostCentreID = PayrollCostCentres.CostCentreID INNER JOIN
dbo.CostCentresWithNames AS ReceivingCostCentres ON
dbo.R_MonthlyReceivingTotals.HealthServiceID = ReceivingCostCentres.HealthServiceID AND
dbo.R_MonthlyReceivingTotals.ReceivingMajorGroupID = ReceivingCostCentres.MajorGroupID AND
dbo.R_MonthlyReceivingTotals.ReceivingMinorGroupID = ReceivingCostCentres.MinorGroupID AND
dbo.R_MonthlyReceivingTotals.ReceivingCostCentreID = ReceivingCostCentres.CostCentreID
(B) The view being changed
CREATE VIEW [dbo].[R_MonthlyReceivingTotalsWithNames]
AS SELECT dbo.Disciplines.Name AS DisciplineName,
PayrollCostCentres.HealthServiceName,
PayrollCostCentres.MajorGroupName AS PayrollMajorGroupName,
PayrollCostCentres.MinorGroupName AS PayrollMinorGroupName,
PayrollCostCentres.CostCentreName AS PayrollCostCentreName,
ReceivingCostCentres.MajorGroupName AS ReceivingMajorGroupName,
ReceivingCostCentres.MinorGroupName AS ReceivingMinorGroupName,
ReceivingCostCentres.CostCentreName AS ReceivingCostCentreName,
dbo.ReportingMonthlyReceivingTotals.*
FROM dbo.ReportingMonthlyReceivingTotals
INNER JOIN dbo.Disciplines
ON dbo.ReportingMonthlyReceivingTotals.ProductCategoryID = dbo.Disciplines.ProductCategoryID
AND dbo.ReportingMonthlyReceivingTotals.DisciplineID = dbo.Disciplines.DisciplineID
INNER JOIN dbo.CostCentresWithNames AS PayrollCostCentres
ON dbo.ReportingMonthlyReceivingTotals.HealthServiceID = PayrollCostCentres.HealthServiceID
AND dbo.ReportingMonthlyReceivingTotals.PayrollMajorGroupID = PayrollCostCentres.MajorGroupID
AND dbo.ReportingMonthlyReceivingTotals.PayrollMinorGroupID = PayrollCostCentres.MinorGroupID
AND dbo.ReportingMonthlyReceivingTotals.PayrollCostCentreID = PayrollCostCentres.CostCentreID
INNER JOIN dbo.CostCentresWithNames AS ReceivingCostCentres
ON dbo.ReportingMonthlyReceivingTotals.HealthServiceID = ReceivingCostCentres.HealthServiceID
AND dbo.ReportingMonthlyReceivingTotals.ReceivingMajorGroupID = ReceivingCostCentres.MajorGroupID
AND dbo.ReportingMonthlyReceivingTotals.ReceivingMinorGroupID = ReceivingCostCentres.MinorGroupID
AND dbo.ReportingMonthlyReceivingTotals.ReceivingCostCentreID = ReceivingCostCentres.CostCentreID
(A) The change script
ALTER VIEW dbo.R_MonthlyReceivingTotalsWithNames
AS
SELECT dbo.Disciplines.Name AS DisciplineName, PayrollCostCentres.HealthServiceName, PayrollCostCentres.MajorGroupName AS PayrollMajorGroupName,
PayrollCostCentres.MinorGroupName AS PayrollMinorGroupName, PayrollCostCentres.CostCentreName AS PayrollCostCentreName,
ReceivingCostCentres.MajorGroupName AS ReceivingMajorGroupName, ReceivingCostCentres.MinorGroupName AS ReceivingMinorGroupName,
ReceivingCostCentres.CostCentreName AS ReceivingCostCentreName, dbo.R_MonthlyReceivingTotals
FROM dbo.R_MonthlyReceivingTotals INNER JOIN
dbo.Disciplines ON dbo.R_MonthlyReceivingTotals.ProductCategoryID = dbo.Disciplines.ProductCategoryID AND
dbo.R_MonthlyReceivingTotals.DisciplineID = dbo.Disciplines.DisciplineID INNER JOIN
dbo.CostCentresWithNames AS PayrollCostCentres ON
dbo.R_MonthlyReceivingTotals.HealthServiceID = PayrollCostCentres.HealthServiceID AND
dbo.R_MonthlyReceivingTotals.PayrollMajorGroupID = PayrollCostCentres.MajorGroupID AND
dbo.R_MonthlyReceivingTotals.PayrollMinorGroupID = PayrollCostCentres.MinorGroupID AND
dbo.R_MonthlyReceivingTotals.PayrollCostCentreID = PayrollCostCentres.CostCentreID INNER JOIN
dbo.CostCentresWithNames AS ReceivingCostCentres ON
dbo.R_MonthlyReceivingTotals.HealthServiceID = ReceivingCostCentres.HealthServiceID AND
dbo.R_MonthlyReceivingTotals.ReceivingMajorGroupID = ReceivingCostCentres.MajorGroupID AND
dbo.R_MonthlyReceivingTotals.ReceivingMinorGroupID = ReceivingCostCentres.MinorGroupID AND
dbo.R_MonthlyReceivingTotals.ReceivingCostCentreID = ReceivingCostCentres.CostCentreID
(B) The view being changed
CREATE VIEW [dbo].[R_MonthlyReceivingTotalsWithNames]
AS SELECT dbo.Disciplines.Name AS DisciplineName,
PayrollCostCentres.HealthServiceName,
PayrollCostCentres.MajorGroupName AS PayrollMajorGroupName,
PayrollCostCentres.MinorGroupName AS PayrollMinorGroupName,
PayrollCostCentres.CostCentreName AS PayrollCostCentreName,
ReceivingCostCentres.MajorGroupName AS ReceivingMajorGroupName,
ReceivingCostCentres.MinorGroupName AS ReceivingMinorGroupName,
ReceivingCostCentres.CostCentreName AS ReceivingCostCentreName,
dbo.ReportingMonthlyReceivingTotals.*
FROM dbo.ReportingMonthlyReceivingTotals
INNER JOIN dbo.Disciplines
ON dbo.ReportingMonthlyReceivingTotals.ProductCategoryID = dbo.Disciplines.ProductCategoryID
AND dbo.ReportingMonthlyReceivingTotals.DisciplineID = dbo.Disciplines.DisciplineID
INNER JOIN dbo.CostCentresWithNames AS PayrollCostCentres
ON dbo.ReportingMonthlyReceivingTotals.HealthServiceID = PayrollCostCentres.HealthServiceID
AND dbo.ReportingMonthlyReceivingTotals.PayrollMajorGroupID = PayrollCostCentres.MajorGroupID
AND dbo.ReportingMonthlyReceivingTotals.PayrollMinorGroupID = PayrollCostCentres.MinorGroupID
AND dbo.ReportingMonthlyReceivingTotals.PayrollCostCentreID = PayrollCostCentres.CostCentreID
INNER JOIN dbo.CostCentresWithNames AS ReceivingCostCentres
ON dbo.ReportingMonthlyReceivingTotals.HealthServiceID = ReceivingCostCentres.HealthServiceID
AND dbo.ReportingMonthlyReceivingTotals.ReceivingMajorGroupID = ReceivingCostCentres.MajorGroupID
AND dbo.ReportingMonthlyReceivingTotals.ReceivingMinorGroupID = ReceivingCostCentres.MinorGroupID
AND dbo.ReportingMonthlyReceivingTotals.ReceivingCostCentreID = ReceivingCostCentres.CostCentreID
Comments
Sorry that you are getting this error.
I know of one other occurence of this error.
I handled a support call a few weeks ago with a similar error when re-naming a Stored Procedure. Where SQL Refactor omitted the '.*' from an object name.
I will add your post to the issue I created in our bug tracking system reporting the error.
Eddie Davis
Red Gate Software Ltd
Technical Support Engineer
E-Mail: support@red-gate.com
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com