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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Linked Server and DIfferent Databases
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bholmstrom
Yak Posting Veteran

USA
69 Posts

Posted - 02/28/2013 :  09:18:11  Show Profile  Reply with Quote
Here is the scenario I need to get working:
I have 2 servers that are linked. 2 Different SQL databases.
Server1 is the server I am doing the query on.
Server2 is the linked server.
Server1 Database=”sql1”
Server2 Database = “sql2”
Server1 Table = “client”
Server2 table = “account”

SQL that fails:
USE SQL1
SELECT * FROM SQL1.DBO.CLIENTS
LEFT OUTER JOIN OPENQUERY([SERVER2],'SELECT account,userfield10’) where
[SERVER2].SQL2.sysdba.account.userfield10 = SQL1.DBO.CLIENTS."CLTNUM"

Error:
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "RL-SLX01.SalesLogix.sysdba.account.userfield10" could not be bound.



Bryan Holmstrom

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/28/2013 :  10:07:51  Show Profile  Reply with Quote

USE SQL1
SELECT * FROM SQL1.DBO.CLIENTS c
LEFT OUTER JOIN OPENQUERY([SERVER2],'SELECT account,userfield10 FROM [SERVER2].SQL2.sysdba.account’) t
ON  t.userfield10 = c."CLTNUM"


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bholmstrom
Yak Posting Veteran

USA
69 Posts

Posted - 02/28/2013 :  11:29:23  Show Profile  Reply with Quote
quote:
Originally posted by visakh16


USE SQL1
SELECT * FROM SQL1.DBO.CLIENTS c
LEFT OUTER JOIN OPENQUERY([SERVER2],'SELECT account,userfield10 FROM [SERVER2].SQL2.sysdba.account’) t
ON  t.userfield10 = c."CLTNUM"


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





Thats was quick and totally correct, Thank you.

The SQL1.DBO.CLIENTS table has 6 entries with the cltnum of '1600186' and sql2sysdba.account has 2 records that have '1600186' in the userfield10 field.

The query is returing 12 rows?

Any ideas?


Bryan Holmstrom

Edited by - bholmstrom on 02/28/2013 12:42:39
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/28/2013 :  13:07:42  Show Profile  Reply with Quote
quote:
Originally posted by bholmstrom

quote:
Originally posted by visakh16


USE SQL1
SELECT * FROM SQL1.DBO.CLIENTS c
LEFT OUTER JOIN OPENQUERY([SERVER2],'SELECT account,userfield10 FROM [SERVER2].SQL2.sysdba.account’) t
ON  t.userfield10 = c."CLTNUM"


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





Thats was quick and totally correct, Thank you.

The SQL1.DBO.CLIENTS table has 6 entries with the cltnum of '1600186' and sql2sysdba.account has 2 records that have '1600186' in the userfield10 field.

The query is returing 12 rows?

Any ideas?


Bryan Holmstrom


thats the expected behaviour. If you just relate on cltnum column then it will return 6 * 2 = 12 records as it matches 2 records of second table for each of 6 records in first table
If you're looking at something different you need to specify additional conditions on the join to make relationship one to one

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bholmstrom
Yak Posting Veteran

USA
69 Posts

Posted - 02/28/2013 :  14:18:38  Show Profile  Reply with Quote
Thank you again for the quick response. I really only want to see the 6 records from Clients that have a cltnum of 'x'

Also:

This code looks like it should work but it fails with Invalid column name 'ClientName'

USE SalesLogix_Training
SELECT account,accountid,userfield10,cltnum,clteng,ClientName FROM SalesLogix_Training.SYSDBA.Account B
INNER JOIN OPENQUERY([RL-SQL02],'SELECT cltnum,clteng,cltname AS ClientName FROM [RL-SQL02].VPM_Test.dbo.clients') t
on left(t.cltnum,15)=left(b.userfield10,15)
where t.cltnum='0804685'

UPDATE SalesLogix_Training.sysdba.Account
SET SalesLogix_Training.sysdba.ACCOUNT."account"=ClientName
WHERE SalesLogix_Training.sysdba.ACCOUNT."Userfield10" = '0804685'

Bryan Holmstrom

Edited by - bholmstrom on 02/28/2013 15:42:35
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/28/2013 :  23:38:47  Show Profile  Reply with Quote
merge them onto same statement

USE SalesLogix_Training
UPDATE B
SET A.account = t.ClientName 
FROM SalesLogix_Training.SYSDBA.Account B
INNER JOIN OPENQUERY([RL-SQL02],'SELECT cltnum,clteng,cltname AS ClientName FROM [RL-SQL02].VPM_Test.dbo.clients') t
on left(t.cltnum,15)=left(b.userfield10,15)
where t.cltnum='0804685'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

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.06 seconds. Powered By: Snitz Forums 2000