Creating a view joining across databases

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

General SQL Server Forums

New to SQL Server Programming

Creating a view joining across databases

Author

Topic

dougbroadwell
Starting Member

4 Posts

Posted - 2010-02-05 : 13:27:35

I'm trying to create a view that is a join between two tables in two databases in the same SQL Server instance.

CREATE VIEW PartCostView
AS SELECT ItemNmb, CSECO.dbo.MIITEM.descr, CostEach, LeadTimeDays
FROM PartCost LEFT OUTER JOIN CSECO.dbo.MIITEM
ON ltrim(rtrim(ItemNmb)) = ltrim(rtrim(CSECO.dbo.MIITEM.descr))

This query returns NULL for the descr field, without the left outer join it returns no rows. Doing a query in Query Analyzer is the same. If I perform the query in an external application through an ODBC driver it works correctly. ???

Thanks
Doug

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 13:32:18

that means you dont have matching records in CSECO.dbo.MIITEM. are you sure relation you gave is correct?
ltrim(rtrim(ItemNmb)) = ltrim(rtrim(CSECO.dbo.MIITEM.descr))

dougbroadwell
Starting Member

4 Posts

Posted - 2010-02-05 : 14:03:46

Absolutely sure. As I mentioned, if I issue the query in an external program through an ODBC driver the query works correctly (and doesn't require the ltrim-rtrim).

dougbroadwell
Starting Member

4 Posts

Posted - 2010-02-05 : 16:55:31

Never mind, you were absolutely correct that I had an incorrect relation. Egg on me face.

Kristen
Test

22859 Posts

Posted - 2010-02-06 : 01:54:31

JOIN to remote server will usually be very slow. If you have that problem and are not familiar with the workarounds come back and ask