SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Creating a view joining across databases		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dougbroadwell
Starting Member

USA
4 Posts
Posted - 02/05/2010 :  13:27:35  Show Profile  Reply with Quote
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

India
47069 Posts
Posted - 02/05/2010 :  13:32:18  Show Profile  Reply with Quote
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))
Go to Top of Page

dougbroadwell
Starting Member

USA
4 Posts
Posted - 02/05/2010 :  14:03:46  Show Profile  Reply with Quote
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).
Go to Top of Page

dougbroadwell
Starting Member

USA
4 Posts
Posted - 02/05/2010 :  16:55:31  Show Profile  Reply with Quote
Never mind, you were absolutely correct that I had an incorrect relation. Egg on me face.
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 02/06/2010 :  01:54:31  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47069 Posts
Posted - 02/06/2010 :  05:58:05  Show Profile  Reply with Quote
quote:
Originally posted by dougbroadwell

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

No worries.. It can happen to anybody anyday
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000