Inner join weirdness in DTS

I am trying to import data from Access 2000 in SQL Server 2000 using
DTS. One of the tasks requires a multi-table join but I am getting
syntax errors if I generate the query with Build Query.

With just a single join like this it works fine:

FROM         Tracker INNER JOIN
bdmanager ON Tracker.bdmanager = bdmanager.name,
country

But as soon as I get it to generate an extra join, e.g.

FROM         Tracker INNER JOIN
bdmanager ON Tracker.bdmanager = bdmanager.name
INNER JOIN
country ON Tracker.country = country.country

... I get "sytax error (missing operator)". The weird thing is that it
generated the syntax itself!
I can paste the query into Access and it works fine.

Why is this happening and what's the best workaround?

Thanks

Andy

On 17 Nov 2005 02:48:04 -0800, Andy Kent wrote:

>I am trying to import data from Access 2000 in SQL Server 2000 using
>DTS. One of the tasks requires a multi-table join but I am getting
>syntax errors if I generate the query with Build Query.
>
>With just a single join like this it works fine:
>
>FROM         Tracker INNER JOIN
>                      bdmanager ON Tracker.bdmanager = bdmanager.name,
>country
>
>But as soon as I get it to generate an extra join, e.g.
>
>FROM         Tracker INNER JOIN
>                      bdmanager ON Tracker.bdmanager = bdmanager.name
>INNER JOIN
>                      country ON Tracker.country = country.country
>
>... I get "sytax error (missing operator)". The weird thing is that it
>generated the syntax itself!
>I can paste the query into Access and it works fine.
>
>Why is this happening and what's the best workaround?

Hi Andy,

Can you copy and paste the complete query instead of just a snippet?

Thanks!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

There's not much to add except SELECT <list-of-cols>. It happens
whether I select three or four columns or the whole twenty or so.

Used a WHERE clause to define the joins for now but I'm still curious
why SQL Server generates code that it then throws out.

Andy

On 18 Nov 2005 01:50:35 -0800, Andy Kent wrote:

>There's not much to add except SELECT <list-of-cols>. It happens
>whether I select three or four columns or the whole twenty or so.

Hi Andy,

I typed "SELECT something" in Query Analyzer and added a copy of the
first (partial) query from your first post - the error I got was about
missing invalid object name (figures - I don't have your tables), so the
syntax check passed alright.

I then type "SELECT something", pasted the second (partial) query and
submitted the query - and I got the same error.

Conclusiion: none of these queries result in "sytax error (missing
operator)" on my database.

Did you use copy and paste to get the code in the message, or did you
copy it by hand? If the latter is the case, then please select the
query, hit copy and paste it in a new message - maybe that'll help me
catch the bugger.


>Used a WHERE clause to define the joins for now but I'm still curious
>why SQL Server generates code that it then throws out.

I'm curious too.

Some more questions:

- What tool do you use to generate the code? Access, DTS, Enterprise
Manager, other?
- Where are the tables that you get the data from (Access, SQL Server)?
- Where is the query executed (Access, SQL Server)?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)