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

• kapil_kk

  SSC-Insane

  Points: 21316

  More actions

  September 24, 2013 at 1:17 am

  #1652581

  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

  September 24, 2013 at 1:26 am

  #1652583

  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

  September 24, 2013 at 1:50 am

  #1652587

  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.