• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

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 '-'.

0
running32
Asked:
running32
  • 3
  • 2
1 Solution
 
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
 
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
 
running32Author Commented:
WONDERFUL..... THANK YOU
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now