Linked Server and DIfferent 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

SQL Server 2005 Forums

Transact-SQL (2005)

Linked Server and DIfferent Databases

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-02-28 : 09:18:11

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

52326 Posts

Posted - 2013-02-28 : 10:07:51

[code]
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"
[/code]

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

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-02-28 : 11:29:23

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-28 : 13:07:42

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/

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-02-28 : 14:18:38

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-28 : 23:38:47

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources