CTE with JOIN to Linked Server table

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 2008 Forums

Transact-SQL (2008)

CTE with JOIN to Linked Server table

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-06-17 : 17:38:25

This CTE query works fine without the linked server fields and the join. The select (commented out below) from the linked server works fine also, separately. When I join them with or without aliases, I get this error on all 4 columns:

Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "SMSSMNMIRPT011.CM_007._sde.v_HR_NSV.EMPLID" could not be bound.


;WITH CTE AS (
SELECT 
 lastname
,firstname
,[email address] email
,authstatus
,CASE WHEN ISNUMERIC(RIGHT(emplid,len(emplid)-1))=1 THEN dbo.PaddedEmpID(RIGHT(EMPLID,LEN(EMPLID)-1)) ELSE RIGHT(EMPLID,LEN(EMPLID)-1) END EmpID
,SMSSMNMIRPT011.CM_007._sde.v_HR_NSV.EMPLID
,SMSSMNMIRPT011.CM_007._sde.v_HR_NSV.LAST_NAME
,SMSSMNMIRPT011.CM_007._sde.v_HR_NSV.FIRST_NAME
,SMSSMNMIRPT011.CM_007._sde.v_HR_NSV.EMAIL_ID

 FROM XLTEST_SP2...Sheet1$
)
SELECT lastname
,firstname
,email
,empid
FROM CTE
LEFT JOIN SMSSMNMIRPT011.CM_007._sde.v_HR_NSV
ON CTE.EmpID = SMSSMNMIRPT011.CM_007._sde.v_HR_NSV.Emplid

--select top 1000 * from SMSSMNMIRPT011.CM_007._sde.v_HR_NSV

Thanks for any help with this.

Duane

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-17 : 17:56:53

You've got too many "."'s in the column reference. You'll need to put [ ] around each element so it can resolve the names properly.

It would be better to pull all the data from the linked server into a temp table and join locally. JOINing over a linked server connection will have poor performance due to excessive network overhead.

~~It looks like you're joining everything to the same server and database, so OPENQUERY might be a better option for performance.~~ Actually I can't tell how this is joining.

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-06-18 : 09:16:43

Someone pointed out to me that I can't refer to the second linked server inside the CTE. That was correct because I only wanted the XLTEST_SP2 in there and join on both in the outer query. I wasn't thinking. I couldn't put the other linked server in the CTE becasue it has millions of rows whereas the CTE only has 2300. So I grab the 2300 and then join to the millions. This revised query actually worked fine:

;WITH CTE AS ( 
SELECT  
lastname 
,firstname 
,[email address] email 
,authstatus 
,CASE WHEN ISNUMERIC(RIGHT(emplid,len(emplid)-1))=1 THEN dbo.PaddedEmpID(RIGHT(EMPLID,LEN(EMPLID)-1)) ELSE RIGHT(EMPLID,LEN(EMPLID)-1) END EmpID 
FROM XLTEST_SP2...Sheet1$ 
) 
SELECT lastname 
,c1.firstname 
,c1.email 
,c1.empid 
,cm7.EMPLID 
,cm7.LAST_NAME 
,cm7.FIRST_NAME 
,cm7.EMAIL_ID 
FROM CTE c1 
LEFT JOIN SMSSMNMIRPT011.CM_007._sde.v_HR_NSV cm7 
ON c1.EmpID = cm7.Emplid

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources