Problem to create a view with SHEMABINDING OPTION

Question

Problem to create a view with SHEMABINDING OPTION

ERIC CRUDELI

SSCommitted

Points: 1786
More actions
September 24, 2013 at 1:09 am

#280021

Hello,
I met a problem when I tried to create a view with option SCHEMABINDING. I need to do that because I would to create Index.
When I ues the following script, it works :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [forms].[VUE_DOMINHO_NOYAU_sFichesIdAutresMetier]
AS
SELECT FI.dispo_doc_id, FOD.fod_ordre_affichage AS ORDRE_PRESENTATION, FOD.fod_libelle AS LIBELLE, FI.fic_date_creation AS DATE_CREATION, FI.service_id,
CR.cr_libelle_court, FI.intervenant_id, INTER.int_nom_complet, FI.fic_intervenant_fonctionnel_id, RESP.int_nom_complet AS RESPONSABLE, FI.fic_resume,
PAT.pat_ancien_id, FOD.dossier_specialite_id
FROM forms.FICHE AS FI INNER JOIN
forms.FORMULAIRE_DESCRIPTION AS FOD ON FOD.formulaire_id = FI.formulaire_id AND FOD.dossier_specialite_id = FI.dossier_specialite_id INNER JOIN
NOYAU.PATIENT.PATIENT AS PAT ON PAT.pat_id = FI.patient_id INNER JOIN
NOYAU.COEUR.INTERVENANT AS INTER ON INTER.int_id = FI.intervenant_id INNER JOIN
NOYAU.COEUR.INTERVENANT AS RESP ON RESP.int_id = FI.fic_intervenant_fonctionnel_id INNER JOIN
NOYAU.COEUR.CENTRE_RESPONSABILITE AS CR ON CR.cr_code = FI.service_id
But when I add the option WITH SCHEMABINDING I got a error. I think I can't create aa view with schemabinding option because I try to make a view between two databases but I'm not sure.
Do you have a idea ?
Thanks for your help,
EC

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

What error you are getting?

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ERIC CRUDELI SSCommitted Points: 1786 More actions · Answer 2

Msg 4512, Level 16, State 3, Procedure VUE_DOMINHO_NOYAU_sFichesIdAutresMetierTEST, Line 5

Cannot schema bind view 'forms.VUE_DOMINHO_NOYAU_sFichesIdAutresMetierTEST' because name 'NOYAU.PATIENT.PATIENT' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

HowardW One Orange Chip Points: 29109 More actions · Answer 3

But when I add the option WITH SCHEMABINDING I got a error. I think I can't create aa view with schemabinding option because I try to make a view between two databases but I'm not sure.

You cannot create a view with SCHEMABINDING across two databases.