Query with left outer join all of a sudden won't work?

[JenniferW]

I have a database with an Access frontend and a SQL 2000 backend. I moved
the data in the database from Access to SQL 6 months ago and it has worked
fine. Then someone went to run a query this week that has a left outer join
and all of a sudden it won't work. It gives an error of "ODBC call failed".
I at first thought maybe there was some bad data but I have a copy of this
database from March in our SQL 2005 server and it also doesn't work. So, it
doesn't seem like it could be a data problem when this query worked fine last
month with our current data but now won't work with current data or data that
hasn't been updated since March. So, this makes me think there has been some
Microsoft update that has happened in the last month and now is causing
problems. Has anyone else had a problem with left outer joins all of a
sudden not working?

Thanks for any help you can give.

 

[Michel Walsh]

Yes. Well, it happened to some users that I know, and few here on this
newsgroup, and in their case, it was implying the use of something like
(JET query):

FROM a LEFT JOIN b
ON (a.f1=b.f2 AND a.f3=cte1 AND b.f4=cte2)


If so, try to change it to:

FROM a LEFT JOIN (SELECT *
FROM b
WHERE f4=cte2) AS newb
ON a.f1=newb.f2
WHERE af.f3=cte1



Note that your case can be different, so that this 'correction' can be
totally useless for you.


If you don't use constraint implying only the unpreserved table ( like our
b.f4=cte2 ) in the ON clause, you are not obliged to create the virtual
table newb.


You may also want to check if you use NOT IN (SELECT ... ) constructions,
where the SELECT can return NULL. That is another thing that seems to have
changed, with Jet.





Vanderghast, Access MVP

 

[JenniferW]

Thanks for the reply. My SQL for the left join was like your first example.
I did try and change it to what you have in your second example but I keep
getting an error "Syntax error on FROM". This is what I changed my SQL to:

From table1 LEFT JOIN (SELECT * FROM table2 ON table1.field1=table2.field2)

So, I thought I had the "(" in the wrong place so I tried this:

From table1 LEFT JOIN (SELECT * FROM table2) ON table1.field1=table2.field2

and got the error "Syntax error in JOIN operator"

any ideas of what I have wrong?

Thanks!

 

[Michel Walsh]

If your comparison in the ON clause is between the two tables, and not one
table and one constant, there should be no change. Try:

FROM table1 LEFT JOIN table2 ON table1.field1 = table2.field2


If that does not work, can you post the whole SQL statement?


Vanderghast, Access MVP

 

[JenniferW]

What you have below is what I think I already have and it isn't working.
Here is a SQL statement that I did as a test and it doesn't work (didn't want
to give you the actual one since it is very long). It isn't just one query
that isn't working. All of the queries I have that have left outer joins in
them are not working.

SELECT [qryFarmer Contracts].Forward_Contract_Num, [qryFarmer
Contracts].Member_Name
FROM [qryFarmer Contracts] LEFT JOIN [qryFarmer Contracts-Forward-Market] ON
[qryFarmer Contracts].Forward_Contract_Num = [qryFarmer
Contracts-Forward-Market].Rider_Contract_Num;

This query is based on 2 other queries.

Thanks for all your help!

 

[Michel Walsh]

That query in itself should work unless one of the query involved in the
join is, in itself, with some problem (and the error message is to be
applied to it, rather than on the top most query calling it).

Does

SELECT * FROM [qryFarmer Contracts]

and

SELECT * FROM [qryFarmer Contracts-Forward-Market]

both work?




or, alternatively, does


SELECT [qryFarmer Contracts].Forward_Contract_Num, [qryFarmer
Contracts].Member_Name
FROM [qryFarmer Contracts] INNER JOIN [qryFarmer Contracts-Forward-Market]
ON
[qryFarmer Contracts].Forward_Contract_Num = [qryFarmer
Contracts-Forward-Market].Rider_Contract_Num;



(I changed the LEFT join for an INNER join) ?



Vanderghast, Access MVP

What you have below is what I think I already have and it isn't working.
Here is a SQL statement that I did as a test and it doesn't work (didn't
want
to give you the actual one since it is very long). It isn't just one
query
that isn't working. All of the queries I have that have left outer joins
in
them are not working.

SELECT [qryFarmer Contracts].Forward_Contract_Num, [qryFarmer
Contracts].Member_Name
FROM [qryFarmer Contracts] LEFT JOIN [qryFarmer Contracts-Forward-Market]
ON
[qryFarmer Contracts].Forward_Contract_Num = [qryFarmer
Contracts-Forward-Market].Rider_Contract_Num;

This query is based on 2 other queries.

Thanks for all your help!

If your comparison in the ON clause is between the two tables, and not
one
table and one constant, there should be no change. Try:

FROM table1 LEFT JOIN table2 ON table1.field1 = table2.field2


If that does not work, can you post the whole SQL statement?


Vanderghast, Access MVP

 

[JenniferW]

I did change the join between the queries to an inner join and it worked. It
just doesn't work if it is a left or right outer join.

Thanks

That query in itself should work unless one of the query involved in the
join is, in itself, with some problem (and the error message is to be
applied to it, rather than on the top most query calling it).

Does

SELECT * FROM [qryFarmer Contracts]

and

SELECT * FROM [qryFarmer Contracts-Forward-Market]

both work?




or, alternatively, does


SELECT [qryFarmer Contracts].Forward_Contract_Num, [qryFarmer
Contracts].Member_Name
FROM [qryFarmer Contracts] INNER JOIN [qryFarmer Contracts-Forward-Market]
ON
[qryFarmer Contracts].Forward_Contract_Num = [qryFarmer
Contracts-Forward-Market].Rider_Contract_Num;



(I changed the LEFT join for an INNER join) ?



Vanderghast, Access MVP

What you have below is what I think I already have and it isn't working.
Here is a SQL statement that I did as a test and it doesn't work (didn't
want
to give you the actual one since it is very long). It isn't just one
query
that isn't working. All of the queries I have that have left outer joins
in
them are not working.

SELECT [qryFarmer Contracts].Forward_Contract_Num, [qryFarmer
Contracts].Member_Name
FROM [qryFarmer Contracts] LEFT JOIN [qryFarmer Contracts-Forward-Market]
ON
[qryFarmer Contracts].Forward_Contract_Num = [qryFarmer
Contracts-Forward-Market].Rider_Contract_Num;

This query is based on 2 other queries.

Thanks for all your help!

If your comparison in the ON clause is between the two tables, and not
one
table and one constant, there should be no change. Try:

FROM table1 LEFT JOIN table2 ON table1.field1 = table2.field2


If that does not work, can you post the whole SQL statement?


Vanderghast, Access MVP





Thanks for the reply. My SQL for the left join was like your first
example.
I did try and change it to what you have in your second example but I
keep
getting an error "Syntax error on FROM". This is what I changed my SQL
to:

From table1 LEFT JOIN (SELECT * FROM table2 ON
table1.field1=table2.field2)

So, I thought I had the "(" in the wrong place so I tried this:

From table1 LEFT JOIN (SELECT * FROM table2) ON
table1.field1=table2.field2

and got the error "Syntax error in JOIN operator"

any ideas of what I have wrong?

Thanks!


:

Yes. Well, it happened to some users that I know, and few here on this
newsgroup, and in their case, it was implying the use of something
like
(JET query):

FROM a LEFT JOIN b
ON (a.f1=b.f2 AND a.f3=cte1 AND b.f4=cte2)


If so, try to change it to:

FROM a LEFT JOIN (SELECT *
FROM b
WHERE f4=cte2) AS newb
ON a.f1=newb.f2
WHERE af.f3=cte1



Note that your case can be different, so that this 'correction' can be
totally useless for you.


If you don't use constraint implying only the unpreserved table ( like
our
b.f4=cte2 ) in the ON clause, you are not obliged to create the
virtual
table newb.


You may also want to check if you use NOT IN (SELECT ... )
constructions,
where the SELECT can return NULL. That is another thing that seems to
have
changed, with Jet.





Vanderghast, Access MVP



I have a database with an Access frontend and a SQL 2000 backend. I
moved
the data in the database from Access to SQL 6 months ago and it has
worked
fine. Then someone went to run a query this week that has a left
outer
join
and all of a sudden it won't work. It gives an error of "ODBC call
failed".
I at first thought maybe there was some bad data but I have a copy
of
this
database from March in our SQL 2005 server and it also doesn't work.
So,
it
doesn't seem like it could be a data problem when this query worked
fine
last
month with our current data but now won't work with current data or
data
that
hasn't been updated since March. So, this makes me think there has
been
some
Microsoft update that has happened in the last month and now is
causing
problems. Has anyone else had a problem with left outer joins all
of a
sudden not working?

Thanks for any help you can give.

 

[Michel Walsh]

I don't see the problem, sorry.


Vanderghast, Access MVP

I did change the join between the queries to an inner join and it worked.
It
just doesn't work if it is a left or right outer join.

Thanks

That query in itself should work unless one of the query involved in the
join is, in itself, with some problem (and the error message is to be
applied to it, rather than on the top most query calling it).

Does

SELECT * FROM [qryFarmer Contracts]

and

SELECT * FROM [qryFarmer Contracts-Forward-Market]

both work?




or, alternatively, does


SELECT [qryFarmer Contracts].Forward_Contract_Num, [qryFarmer
Contracts].Member_Name
FROM [qryFarmer Contracts] INNER JOIN [qryFarmer
Contracts-Forward-Market]
ON
[qryFarmer Contracts].Forward_Contract_Num = [qryFarmer
Contracts-Forward-Market].Rider_Contract_Num;



(I changed the LEFT join for an INNER join) ?



Vanderghast, Access MVP

What you have below is what I think I already have and it isn't
working.
Here is a SQL statement that I did as a test and it doesn't work
(didn't
want
to give you the actual one since it is very long). It isn't just one
query
that isn't working. All of the queries I have that have left outer
joins
in
them are not working.

SELECT [qryFarmer Contracts].Forward_Contract_Num, [qryFarmer
Contracts].Member_Name
FROM [qryFarmer Contracts] LEFT JOIN [qryFarmer
Contracts-Forward-Market]
ON
[qryFarmer Contracts].Forward_Contract_Num = [qryFarmer
Contracts-Forward-Market].Rider_Contract_Num;

This query is based on 2 other queries.

Thanks for all your help!

:

If your comparison in the ON clause is between the two tables, and not
one
table and one constant, there should be no change. Try:

FROM table1 LEFT JOIN table2 ON table1.field1 = table2.field2


If that does not work, can you post the whole SQL statement?


Vanderghast, Access MVP





Thanks for the reply. My SQL for the left join was like your first
example.
I did try and change it to what you have in your second example but
I
keep
getting an error "Syntax error on FROM". This is what I changed my
SQL
to:

From table1 LEFT JOIN (SELECT * FROM table2 ON
table1.field1=table2.field2)

So, I thought I had the "(" in the wrong place so I tried this:

From table1 LEFT JOIN (SELECT * FROM table2) ON
table1.field1=table2.field2

and got the error "Syntax error in JOIN operator"

any ideas of what I have wrong?

Thanks!


:

Yes. Well, it happened to some users that I know, and few here on
this
newsgroup, and in their case, it was implying the use of something
like
(JET query):

FROM a LEFT JOIN b
ON (a.f1=b.f2 AND a.f3=cte1 AND b.f4=cte2)


If so, try to change it to:

FROM a LEFT JOIN (SELECT *
FROM b
WHERE f4=cte2) AS newb
ON a.f1=newb.f2
WHERE af.f3=cte1



Note that your case can be different, so that this 'correction' can
be
totally useless for you.


If you don't use constraint implying only the unpreserved table (
like
our
b.f4=cte2 ) in the ON clause, you are not obliged to create the
virtual
table newb.


You may also want to check if you use NOT IN (SELECT ... )
constructions,
where the SELECT can return NULL. That is another thing that seems
to
have
changed, with Jet.





Vanderghast, Access MVP



I have a database with an Access frontend and a SQL 2000 backend.
I
moved
the data in the database from Access to SQL 6 months ago and it
has
worked
fine. Then someone went to run a query this week that has a left
outer
join
and all of a sudden it won't work. It gives an error of "ODBC
call
failed".
I at first thought maybe there was some bad data but I have a
copy
of
this
database from March in our SQL 2005 server and it also doesn't
work.
So,
it
doesn't seem like it could be a data problem when this query
worked
fine
last
month with our current data but now won't work with current data
or
data
that
hasn't been updated since March. So, this makes me think there
has
been
some
Microsoft update that has happened in the last month and now is
causing
problems. Has anyone else had a problem with left outer joins
all
of a
sudden not working?

Thanks for any help you can give.

 

[Stefan Hoffmann]

hi Jennifer,

I have a database with an Access frontend and a SQL 2000 backend. I moved
the data in the database from Access to SQL 6 months ago and it has worked
fine. [..] our SQL 2005 server and it also doesn't work.

Do you really have these three SQL Server versions in use?

One problem I remember: The drivers for SQL Server 6.5 installed version
depended different procedures on the server. These procedures failed
then with the other drivers.

Had the SQL Server 6 a profiler tool? Try to run SQL Profiler against
your SQL Server 2000 or 2005.


mfG
--> stefan <--

 

[JenniferW]

Hi Stefan

To answer your question, we still use SQL 2000 and are slowly moving all of
our databases to SQL 2005. Almost there but not quite done yet. I think you
think we still use SQL 6 because I said we moved the data from Access to SQL
6 months ago. I meant we moved the data 6 months ago, not that we moved it
to SQL 6. The data was moved to SQL 2000 from Access.

Anyways, I have come to the conculsion that something has gotten updated in
the past month with Access 2003 or one of the things it uses because this
database works perfectly when I use it in Access 2007. Not sure what got
changed that now SQL doesn't like any Access 2003 query that has an outer
join in it. So, it looks like we will be upgrading to 2007 since that is an
easier fix then trying to find a work around for Access 2003.

Thanks
Jennifer

hi Jennifer,

I have a database with an Access frontend and a SQL 2000 backend. I moved
the data in the database from Access to SQL 6 months ago and it has worked
fine. [..] our SQL 2005 server and it also doesn't work.

Do you really have these three SQL Server versions in use?

One problem I remember: The drivers for SQL Server 6.5 installed version
depended different procedures on the server. These procedures failed
then with the other drivers.

Had the SQL Server 6 a profiler tool? Try to run SQL Profiler against
your SQL Server 2000 or 2005.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Jennifer,

I meant we moved the data 6 months ago, not that we moved it
to SQL 6.

Oops

Anyways, I have come to the conculsion that something has gotten updated in
the past month with Access 2003 or one of the things it uses because this
database works perfectly when I use it in Access 2007.

Check whether the Office SP3 was installed, if so there is an after SP
hotfix necessary for Access 2003:

http://support.microsoft.com/kb/945674

But I don't think this addresses your problem directly.

changed that now SQL doesn't like any Access 2003 query that has an outer
join in it. So, it looks like we will be upgrading to 2007 since that is an
easier fix then trying to find a work around for Access 2003.

Have the tables changed on the server? Try to relink the used tables.

Of what data type is the column [Rider_Contract_Num]?

Maybe rounding or charset issues?

btw, check if you have indices on these columns on your server. Maybe
they needed to be rebuild (DBCC CHECKDB/DBREINDEX).


mfG
--> stefan <--

 

[Michel Walsh]

Just in case, can you check if the log file has enough room to accommodate
the outer join? Someone mentioned me there could be similar problem with
older MS SQL Server ( I was on the impression you were using MS SQL Server
6) if the log file was full.



Vanderghast, Access MVP

 

[Bob Barrows [MVP]]

What you have below is what I think I already have and it isn't
working. Here is a SQL statement that I did as a test and it doesn't
work (didn't want to give you the actual one since it is very long).
It isn't just one query that isn't working. All of the queries I
have that have left outer joins in them are not working.

SELECT [qryFarmer Contracts].Forward_Contract_Num, [qryFarmer
Contracts].Member_Name
FROM [qryFarmer Contracts] LEFT JOIN [qryFarmer
Contracts-Forward-Market] ON [qryFarmer
Contracts].Forward_Contract_Num = [qryFarmer
Contracts-Forward-Market].Rider_Contract_Num;

This query is based on 2 other queries.

This query should work. Are the source queries all based on SQL Server
linked tables? Or is there a mixture of local and linked tables?

Can you accomplish what this query is doing with a sql server stored
procedure and a passthrough query?

 

[Sylvain Lafontaine]

Maybe a compilation problem here. Try to fully decompile your MDB file
twice (2 times) by using a shortcut with the options: « /decompile
/compact ».

Beside decompiling the file, in the past, I've seen that doing a small
change like adding the semi-comma ; at the end of the query or removing it
might be sufficient to make a complex with outer joins and subqueries
against ODBC linked tables starts to work again.

However, if Access 2007 works for you and you can upgrade, then this is
obviously the easy fix to choose.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Hi Stefan

To answer your question, we still use SQL 2000 and are slowly moving all
of
our databases to SQL 2005. Almost there but not quite done yet. I think
you
think we still use SQL 6 because I said we moved the data from Access to
SQL
6 months ago. I meant we moved the data 6 months ago, not that we moved
it
to SQL 6. The data was moved to SQL 2000 from Access.

Anyways, I have come to the conculsion that something has gotten updated
in
the past month with Access 2003 or one of the things it uses because this
database works perfectly when I use it in Access 2007. Not sure what got
changed that now SQL doesn't like any Access 2003 query that has an outer
join in it. So, it looks like we will be upgrading to 2007 since that is
an
easier fix then trying to find a work around for Access 2003.

Thanks
Jennifer

hi Jennifer,

I have a database with an Access frontend and a SQL 2000 backend. I
moved
the data in the database from Access to SQL 6 months ago and it has
worked
fine. [..] our SQL 2005 server and it also doesn't work.

Do you really have these three SQL Server versions in use?

One problem I remember: The drivers for SQL Server 6.5 installed version
depended different procedures on the server. These procedures failed
then with the other drivers.

Had the SQL Server 6 a profiler tool? Try to run SQL Profiler against
your SQL Server 2000 or 2005.


mfG
--> stefan <--

 

[JenniferW]

Hi Stefan

Check whether the Office SP3 was installed, if so there is an after SP
hotfix necessary for Access 2003:

- I do have SP 3 installed and I had found that hotfix you mentioned and
installed that but still no luck.

Have the tables changed on the server? Try to relink the used tables.

The tables haven't changed and I did re-link the tables.

Of what data type is the column [Rider_Contract_Num]?
Maybe rounding or charset issues?

This field is a text field so probably not rounding issues.

btw, check if you have indices on these columns on your server. Maybe
they needed to be rebuild (DBCC CHECKDB/DBREINDEX).

I did both of these and it didn't help.

Thanks for the reply with all the suggestions
Jennifer

 

[JenniferW]

Just in case, can you check if the log file has enough room to accommodate
the outer join? Someone mentioned me there could be similar problem with
older MS SQL Server ( I was on the impression you were using MS SQL Server
6) if the log file was full.

Are you talking about the SQL server log file that is listed in Enterprise
Manager? It is about 13 MB and I assume it is saved on the C drive which has
about 3 Gigs of space so I would think that would be enough. Don't know if
that would be the problem since we do have this same database on SQL 2005 and
have the same problem and there is a lot more hard drive space on that
server.

Thanks
Jennifer

 

[Michel Walsh]

Yes, that was the target. So it is not the source of the problem :-(


Vanderghast, Access MVP