WITH SCHEMABINDING - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

WITH SCHEMABINDING

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2010-04-29 : 10:14:10

I have the following View that when I try to add 'WITH SCHEMABINDING' I get the error message shown after the View...

USE [MercerDailyWorkTicket]
GO
/****** Object: View [dbo].[VW_WellService_PayrollView2] Script Date: 08/11/2009 11:57:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[VW_WellService_PayrollView2] WITH SCHEMABINDING
AS

SELECT
dtl.Date, dtl.WorkLocation, dtl.DailyWorkTicketNumber,dtl.EmployeeNumber, dtl.EmployeeName, dtl.JobTitle, dtl.ID,
CASE WHEN PayCategory IN ('VAC', 'HOL','SAFTY','SAFT','TRAVL','HAZ','BONJB') THEN 0 ELSE dtl.Hours END AS Hours,
CASE WHEN PayCategory IN ('VAC', 'HOL','SAFTY','SAFT','TRAVL','HAZ','BONJB') THEN dtl.Hours ELSE 0 END AS Hours1,
hdr.WorkLocation AS HomeLocation, dtl.CostCenter, dtl.PayCategory, dtl.LocationGLCode, hdr.TimeClockID, hdr.Status,
hdr.StartDate, hdr.Entity, hdr.GLType, hdr.PayGroup, hdr.PayGroupCode, dtl.SafetyEligible,
hdr.JobTitle AS JobTitHdr,
ISNULL(CDLDriver,0)CDLDriver,

CASE
WHEN dtl.PayCategory IN ('VAC','HOL') THEN hdr.HourlyPayRate
WHEN dtl.PayCategory IN ('SAFTY','TRAVL','HAZ','SAFT','BONJB') THEN 1
WHEN hdr.JobTitle IN ('Accounting Clerk IV','Accounting Clerk I') THEN 0
WHEN SUBSTRING(dtl.WorkLocation, 1, 3) = 'MWS' AND hdr.JobTitle IN ('Mechanic') THEN hdr.HourlyPayRate
WHEN SUBSTRING(dtl.WorkLocation, 1, 3) = 'TWS' AND hdr.JobTitle IN ('Mechanic','Driver') THEN hdr.HourlyPayRate
ELSE dtl.PayRate END AS HourlyPayRate

FROM MercerDailyWorkTicket..VW_WellService_PayrollView dtl
JOIN UP_TXD_IMPORT..VW_UP_WellService_PayrollHdrUnion hdr on dtl.EmployeeNumber = hdr.EmployeeNumber

Error:
Msg 4512, Level 16, State 3, Procedure VW_WellService_PayrollView2, Line 4
Cannot schema bind view 'dbo.VW_WellService_PayrollView2' because name 'MercerDailyWorkTicket..VW_WellService_PayrollView' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Can anyone tell me why I get this error? I tried in the FROM statement to add the 'dbo.' to VW_WellService_PayrollView but then I get a message saying it doesn't recognize the server in sys.servers. I can't figure this out. Any help would be appreciated. Thanks.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-04-29 : 10:29:10

The error message explains it fairly clearly: you must use a two part name.

'MercerDailyWorkTicket..VW_WellService_PayrollView' is a three part name, since it includes the database name.

What happened when you tried 'dbo.VW_WellService_PayrollView'?

CODO ERGO SUM

osupratt
Posting Yak Master

238 Posts

Posted - 2010-04-29 : 13:53:13

I tried in the FROM statement to add the 'dbo.' to VW_WellService_PayrollView but then I get a message saying it doesn't recognize the server in sys.servers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources