Syntax problem running query on remote db using linked server

Question

Hi I have the following query(have commented most of it out to simplify) I can do a straight select from debtor4 table on remote server using select * from [SQL1].TitaniumPractice.dbo.DEBTOR4 the problem comes when I join another table

Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "SQL1.TitaniumPractice.dbo.DEBTOR4.RecordNum" could not be bound

SELECT appt6.RecordNum as AppointmentID,
--dbo.fnSpark_GetAppointmentStatus(APPT6.wStatus,APPT6.dtDate) AS ApptStatus,
APPT6.wStatus,
APPT6.dtDate,
DEBTOR4.Code AS PCodeAppt,
DEBTOR4.IDNumber as NHI,
DEBTOR4.firstName as Fname,
DEBTOR4.lastName as Lname,
DEBTOR4.mobilePhone as Mobile,
        APPT6.location as ApptLocation,
       --school.name as SchName,
APPT6.dentist as Therapist,           
                        APPT6.RecordNum AS ApptRecordNum,
                        dateadd(day,2,APPT6.dtDate+ appt6.tmTime) AS VisitDateTime,            
                        appt6.tmTime as visittime,
                       appt6.dtdate as visitdate,
                        --DENTIST4.name,
                       Debtor4.dtDOB


FROM [SQL1].TitaniumPractice.dbo.DEBTOR4 LEFT OUTER JOIN [SQL1].TitaniumPractice.dbo.APPT6 ON ([SQL1].TitaniumPractice.dbo.DEBTOR4.RecordNum = [SQL1].TitaniumPractice.dbo.APPT6.ridPatient 
AND [SQL1].TitaniumPractice.dbo.APPT6.deleted =0)

-- LEFT OUTER JOIN [SQL1].TitaniumPractice.dbo.SCHOOL ON ([SQL1].TitaniumPractice.dbo.SCHOOL.code = [SQL1].TitaniumPractice.dbo.DEBTOR4.school AND [SQL1].TitaniumPractice.dbo.SCHOOL.Deleted = 0)
   --INNER JOIN [SQL1].TitaniumPractice.dbo.DENTIST4 on ([SQL1].TitaniumPractice.dbo.DENTIST4.code = ---[SQL1].TitaniumPractice.dbo.appt6.dentist)

WHERE --dbo.fnSpark_GetAge(Debtor4.dtDOB,APPT6.dtDate) <=18 
 APPT6.dtDate >=  getdate()
and appt6.bAuditAppt =0
AND APPT6.Deleted = 0 
and DEBTOR4.Deleted = 0
and debtor4.mobilePhone !=''

                                                        

Answer 1

The following link ma be helpful.

http://aartemiou.blogspot.com.au/2009/01/multi-part-identifier-could-not-be.html

Does the RecordNum column exists in DEBTOR4 table??

---

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 2

SQL1.TitaniumPractice.dbo.DEBTOR4.RecordNum is five part naming. You can't refer a column in that way. define an alias for four part naming and use that in where clause.

SELECT count(*)
  FROM [SQLMAN].[MyDB].[dbo].[T1]
  where I = 'B'
GO
-- works

SELECT count(*)  
  FROM [SQLMAN].[MyDB].[dbo].[T1]
  where [SQLMAN].[MyDB].[dbo].[T1].I = 'B'
GO

-- error
/*
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "SQLMAN.MyDB.dbo.T1.I" could not be bound.
*/

SELECT count(*)
  FROM [SQLMAN].[MyDB].[dbo].[T1] as Table_T1_On_SQLMAN
  where Table_T1_On_SQLMAN.I = 'A'
GO
-- works

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter

Answer 3

Thanks for the replies, had discovered that aliasing was the solution and logged in to update. Will give answer to Balmukund.

thanks again

Answer 4

Thanks for the replies, had discovered that aliasing was the solution and logged in to update. Will give answer to Balmukund.

thanks again

Thanks for providing update.

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter