Error on linked server query

update upload set strmatch = 1
where (name in (select BC-DENTRIX.dbo.ddb_pat_base.lastname from BC-DENTRIX.dbo.ddb_pat_base))
and (dob in (select BC-DENTRIX.dbo.ddb_pat_base.birthdate from BC-DENTRIX.dbo.ddb_pat_base))
and (lname in (select BC-DENTRIX.dbo.ddb_pat_base.firstname from BC-DENTRIX.dbo.ddb_pat_base))

I am trying to run the above query on a linked server but I am getting the message below.  I would be greatful for any help on how to resolve this.  Thank you
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '-'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '-'.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '-'.

running32Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
i see... we need to add a "." for the 4-part-name, even if we don't need to specify the database name itself:

update upload
 set strmatch = 1
from upload u
join [BC-DENTRIX]..dbo.ddb_pat_base x
  on x.lastname = u.name
 and x.birthdatre = u.dob
 and x.firstname = u.lname

or, when you want to specify the database name:

update upload
 set strmatch = 1
from upload u
join [BC-DENTRIX].<database name here>.dbo.ddb_pat_base x
  on x.lastname = u.name
 and x.birthdatre = u.dob
 and x.firstname = u.lname
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
update upload set strmatch = 1
where (name in (select [BC-DENTRIX].dbo.ddb_pat_base.lastname from [BC-DENTRIX].dbo.ddb_pat_base))
and (dob in (select [BC-DENTRIX].dbo.ddb_pat_base.birthdate from [BC-DENTRIX].dbo.ddb_pat_base))
and (lname in (select [BC-DENTRIX].dbo.ddb_pat_base.firstname from [BC-DENTRIX].dbo.ddb_pat_base))
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
or more efficient coding:

update upload
 set strmatch = 1
from upload u
join [BC-DENTRIX].dbo.ddb_pat_base x
  on x.lastname = u.name
 and x.birthdatre = u.dob
 and x.firstname = u.lname
0
 
running32Author Commented:
It gives me the error Invalid object name 'BC-DENTRIX.dbo.ddb_pat_base'. but I see the linked server BC-Dentrix in my linked server list and can see the tables through enterprise manager.

thanks
0
 
running32Author Commented:
WONDERFUL..... THANK YOU
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.