| Author |
 Topic  |
|
|
bholmstrom
Starting Member
USA
32 Posts |
 Posted - 02/28/2013 : 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
India
47173 Posts |
Posted - 02/28/2013 : 10:07:51
|
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/
|
 |
|
|
bholmstrom
Starting Member
USA
32 Posts |
Posted - 02/28/2013 : 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 |
Edited by - bholmstrom on 02/28/2013 12:42:39 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/28/2013 : 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
Starting Member
USA
32 Posts |
Posted - 02/28/2013 : 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 |
Edited by - bholmstrom on 02/28/2013 15:42:35 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/28/2013 : 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/
|
|
|
| |
Topic |
|
Linked Server and DIfferent Databases
