CODEWITHSUNDEEP
sqlsql-serversql-server-2005
user1086159
user1086159

Reputation: 1055

SQL View creation error

I am trying to copy a view from one database to another and get the error :

Msg 4121, Level 16, State 1, Procedure vwPAProjects_Summary_New_2 Line 2
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.GetContractName", or the name is ambiguous

The view I am copying over is:

USE [GRAUD]
GO
/****** Object:  View [dbo].[vwPAProjects_Summary_New_2]    Script Date: 11/07/2012 11:57:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwPAProjects_Summary_New_2] AS 
select
 dbo.GetContractName(PAPROJNUMBER) 'CONTRACT',
PACONTNUMBER,
isnull(dbo.GetEmployeeName(PABusMgrID),dbo.GetContractPartner(PACONTNUMBER)) 'Project Manager ID',
case PASTAT
when 1 then 'Open'
else 'Closed'
end 'Status',
case PAProjectType
when 3 then 'Fixed Price'
else 'Time and Materials'
end 'Project Type',
PAProject_Fee_Amount 'Project Fee Amount',
PAFTotalCost 'Forecast Total Cost',
(PAProject_Fee_Amount - PAFTotalCost) 'Forcast Profit',
case
    when PAProject_Fee_Amount = 0 then '0.00%'
    else cast(cast(((PAProject_Fee_Amount - PAFTotalCost)/PAProject_Fee_Amount)*100 as decimal(19,2)) as varchar(21)) + '%'
end 'Forcast %',
PAPostedBillingsN 'Actual Billings',
PAPostedTotalCostN 'Actual Total Cost',
(PAPostedBillingsN - PAPostedTotalCostN) 'Current Actual Profit',
case
    when PAPostedBillingsN = 0 then '0.00%'
    else cast(cast(((PAPostedBillingsN - PAPostedTotalCostN)/PAPostedBillingsN)*100 as decimal(19,2)) as varchar(21)) + '%'
end 'Current %'

from PA01201
where PASTAT = 1

Can anybody advise on how I can get this to copy over? It has worked on other databases after I have copied it over but I cannot see any differences that would stop it this time?

Upvotes: 0

Views: 113

Answers (1)

Steve Mayne
Steve Mayne

Reputation: 22818

Copy over the function dbo.GetContractName first, then you should find the view copies too.

Upvotes: 2

Related Questions