• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 591
  • Last Modified:

SQL2005 schema errors on view indexes

I'm trying to create indexes on a view but am getting a schema binding message.  If i use "with schemabinding" altering or creating the view I get the following message "Cannot schema bind view 'dbo.iv30300_WHIST' because name 'KG.DBO.IV30300' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself."

Please not that I'm trying to union tables from two different database tables.

Am I going to be able to generate the view with indexes?


alter VIEW [dbo].[iv30300_WHIST]  
AS
SELECT TRXSORCE
      ,DOCTYPE
      ,DOCNUMBR
      ,DOCDATE
      ,HSTMODUL
      ,CUSTNMBR
      ,ITEMNMBR
      ,LNSEQNBR
      ,UOFM
      ,TRXQTY
      ,UNITCOST
      ,EXTDCOST
      ,TRXLOCTN
      ,TRNSTLOC
      ,TRFQTYTY
      ,TRTQTYTY
      ,IVIVINDX
      ,IVIVOFIX
      ,DECPLCUR
      ,DECPLQTY
      ,QTYBSUOM
      ,DEX_ROW_ID
FROM kg.dbo.IV30300
UNION
SELECT TRXSORCE
      ,DOCTYPE
      ,DOCNUMBR
      ,DOCDATE
      ,HSTMODUL
      ,CUSTNMBR
      ,ITEMNMBR
      ,LNSEQNBR
      ,UOFM
      ,TRXQTY
      ,UNITCOST
      ,EXTDCOST
      ,TRXLOCTN
      ,TRNSTLOC
      ,TRFQTYTY
      ,TRTQTYTY
      ,IVIVINDX
      ,IVIVOFIX
      ,DECPLCUR
      ,DECPLQTY
      ,QTYBSUOM
      ,DEX_ROW_ID
FROM KHIST.DBO.IV30300
0
jdr0606
Asked:
jdr0606
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
No. You are probably better off using SSIS (or SQL Server Agent job) to run the output of the query into a physical table each night. You can index the table and simply TRUNCATE it before INSERT each night to ensure you have the most up-to-date information. In SQL 2008 and higher, you can use MERGE but the gist is the same.

MSDN:
hen you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.
http://msdn.microsoft.com/en-us/library/ms187956.aspx
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now