(T-Sql)-Problem including instance with VIEW 2

[markajem]

MS-SQL2000

I am attempting to access a different database from a database on the same server. No matter what combination I use I am getting invalid columns etc. I am new at this and it is probably just some simple syntax I am missing.

Also this is test code. Once this code goes live it will be going cross servers. What extra code would I have to add in order to accomplish this.

Thanks so much for your help.

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE       VIEW dbo.VIEW_Phy_count_NEW
AS

SELECT  TOP 100 PERCENT 
	CAST(PRODMSTR.PRODUCT AS CHAR (31)) AS prodmstr_product, 
	isnull(BINLOCAT.PRODUCT,PRODMSTR.PRODUCT) AS phy_Product, 
        isnull(SUM(BINLOCAT.QUANTITY * BINLOCAT.PACKSIZE),0) AS phy_Total_Pcs

FROM         databasename.dbo.BINLOCAT  RIGHT OUTER JOIN
                      databasename.dbo.PRODMSTR  ON databasename.dbo.BINLOCAT.PRODUCT = databasename.dbo.PRODMSTR.PRODUCT

GROUP BY BINLOCAT.PRODUCT, PRODMSTR.PRODUCT

ORDER BY PRODMSTR.PRODUCT

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

 

[gmmastros]

When using multiple databases, I always recommend using aliases, it makes the job a lot simpler.

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE       VIEW dbo.VIEW_Phy_count_NEW
AS

SELECT  TOP 100 PERCENT 
    CAST(PRODMSTR.PRODUCT AS CHAR (31)) AS prodmstr_product, 
    isnull(BINLOCAT.PRODUCT,PRODMSTR.PRODUCT) AS phy_Product, 
        isnull(SUM(BINLOCAT.QUANTITY * BINLOCAT.PACKSIZE),0) AS phy_Total_Pcs

FROM         databasename.dbo.BINLOCAT [red]As BinLocat[/red] RIGHT OUTER JOIN
                      databasename.dbo.PRODMSTR [red]As PRODMSTR[/red] ON BINLOCAT.PRODUCT = PRODMSTR.PRODUCT

GROUP BY BINLOCAT.PRODUCT, PRODMSTR.PRODUCT

ORDER BY PRODMSTR.PRODUCT

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[markajem]

Thanks George, that did the trick.

Now, if I wanted to go cross servers would I do this?

Thanks
Mark

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE       VIEW dbo.VIEW_Phy_count_NEW
AS

SELECT  TOP 100 PERCENT 
    CAST(PRODMSTR.PRODUCT AS CHAR (31)) AS prodmstr_product, 
    isnull(BINLOCAT.PRODUCT,PRODMSTR.PRODUCT) AS phy_Product, 
        isnull(SUM(BINLOCAT.QUANTITY * BINLOCAT.PACKSIZE),0) AS phy_Total_Pcs

FROM         [COLOR=blue]otherserver.[/color]databasename.dbo.BINLOCAT As BinLocat RIGHT OUTER JOIN
                      [COLOR=blue]otherserver.[/color]databasename.dbo.PRODMSTR As PRODMSTR ON BINLOCAT.PRODUCT = PRODMSTR.PRODUCT

GROUP BY BINLOCAT.PRODUCT, PRODMSTR.PRODUCT

ORDER BY PRODMSTR.PRODUCT

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

 

[SQLDenis]

yes same

CREATE       VIEW dbo.VIEW_Phy_count_NEW
AS

SELECT  TOP 100 PERCENT 
    CAST(p.PRODUCT AS CHAR (31)) AS prodmstr_product, 
    isnull(b.PRODUCT,p.PRODUCT) AS phy_Product, 
        isnull(SUM(b.QUANTITY * b.PACKSIZE),0) AS phy_Total_Pcs

FROM         otherserver.databasename.dbo.BINLOCAT b  RIGHT OUTER JOIN
                      otherserver.databasename.dbo.PRODMSTR p  ON b.PRODUCT = p.PRODUCT

GROUP BY b.PRODUCT, p.PRODUCT

ORDER BY p.PRODUCT

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[markajem]

Thanks Denis