Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

Query result incorrect when using SQL-server tables (ODBC)

Hi,

We are using Access and SQL-server. We have found a problem in the result of the queries. I will describe the situation:
We create a query that uses two tables of SQL-server via ODBC. The link between the tables is an outer join because not all items of the first table exists in the second table. The result is that only the items that exists in both tables will be displayed (strange!! It should be all items of the first).

We are sure that the query is correct and that there is a problem with the system, ODBC-driver or something because if we run the same query on another computer and the result is correct (all items of the first table).

Maybe it is an already known bug but we can't find a solution now.

Johan
0
JohanT
Asked:
JohanT
  • 10
  • 9
  • 3
  • +5
1 Solution
 
yitz99Commented:
Confidence aside, it would be a good idea to post your query here for reference.
0
 
JohanTAuthor Commented:
Here is an example of a query that isn't working correct on some systems:

SELECT Table1.Field1
FROM Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1
GROUP BY Table1.Field1;

So table1 will link to table2. The result should be at least the same number of items in table1.

Johan
0
 
NosterdamusCommented:
Hi JohanT,

If Table1.Field1 is NOT a unique field (i.e. it can contain DUP values through out the table), then you will definatly not get all the records in table1, as you are using the GROUP BY clause.

Is it possible that your intention was to use the ORDER BY clause instead?

Nosterdamus
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
casassusCommented:
With a left join, you have all the record of the table1 and record from table2 if join is possible.

For example :

Case 1
Table1.Field1

1
2
3

Table2.Field1
1
3

Your query give you
1
2
3

Case 2
Table1.Field1

1
2
3

Table2.Field1
1
3
4

Your query give you
1
2
3
0
 
JohanTAuthor Commented:
Thanks for your comments,

The given query is just a simple example of a query that is giving different results if I run them on different machines. It is not logical query but is showing that we are having a problem (it works correct on 8 systems and incorrect on 4 systems of the systems we checked).

Be aware that I know that my query is correct.

JohanT
0
 
NosterdamusCommented:
Are all machines using the same MDAC version?

Are all machines using the same OS? If not, then on what OS versions it runs OK and on what OS it wont?

Are all machines using the same MS Office environment (Version, SR etc.)? If not, then on what MS Office versions it runs OK and on what MS Office it wont?

Nosterdamus
0
 
Jonathan KellyCommented:
are u deffinately linked to the correct sql tables ?
0
 
dovholukCommented:
this may be a silly question, but why don't you create a view on your SQL server that does the join for you?

just a thought...

the only issues i've run into that are silly like what you are experiencing with access is when sorting views in the results window. a lot of times the results aren't sorted the way i'd expect, but i've never seen a loss of data.

an alternative would be to use a passthrough query from the client PC. this will often times be much faster as ONLY the returned records are transferred from server-client and it's guaranteed to work on all platforms because the server is doing ALL the work...

another thought...

dovholuk
0
 
JohanTAuthor Commented:
We have installed the MDAC_Type 2.5 on all the machines I used for testing.

Most of the systems are using WinNT and some Win2000. The machines with Win2000 don't give a problem, only some of the WinNT machine do.

On the WinNt machines we are working with Access '97. We thought that there was a problem with the dutch version of
Access because all the machines with the english version were working ok. But we completely reinstalled a machine and it is still not working.

Any idea?

Johan
0
 
Jonathan KellyCommented:
can u run make table queries and run the query against the new tables
0
 
NosterdamusCommented:
Was your MS Access app developed under Access 97 or Access 2000?
0
 
dovholukCommented:
JohanT,

have you had a chance to try out the ideas i posed in my previous post?

do you need any help in this direction?

dovholuk
0
 
JohanTAuthor Commented:
Hello,

Thanks for all your comments but it is not where I'm searching/asking for.
We have a database (read 1 database) that is working correct on most of the WinNT machines but not on all machines. It's a database designed and used under Access '97 and with the same MDAC_Type installed.
There should be in some way a difference between the machines but I really don't know what the difference is.

Johan
0
 
peroveCommented:
My first guess in these scenarios when equal things work on one machine but not on another, is that there are someething with international settings, I see that you is not from US, and -as me- have (or will have) struggle with this thing. MS does not take to good care of us not-US developers..

Is there some date, number etc.. criteria in qour query?
Is there any difference in the OS language?
Is there any difference in the international settings (date, number, keyboard...etc)

perove


0
 
NosterdamusCommented:
hi JohanT,

Just to add to perove's comment:

If you are quering the DB for dates (e.g. WHERE mydate = #06/07/2001#) then notice that you MUST use a US date format in the SQL statement.

In the WHERE clause example, the SQL statement will search for mydate = 'June 7'th 2001' (mm/dd/yyy) and NOT for 'July 6'th 2001', as expected in European format (dd/mm/yyyy).

Nosterdamus
0
 
JohanTAuthor Commented:
I checked the regional settings. The setting is English(US), so this should be ok.
The language of the operating system is the same (also Access) and we are not linking on a date or a time. We just want to link an Id or a text field.

Johan
0
 
mcmahon_sCommented:
Hi Johan,

What version of SQL server (and service pack) are you using. I have had problems with things like this where the client PC has an older version of some of the SQL dll's than those that are shipped with the SQL server service packs.

Your problem sounds similar given that all the WIN 2000 PC's seem to be working and they would have the latest versions.
0
 
JohanTAuthor Commented:
We are working with SQL-server version 7 with SP2. On the WinNT machines with the problem there no SQL-Client installed but there is a Win95 machine with the SQL enterprise manager and this machine has the problem also.

If that is really the problem, why isn't it working on the Win95 machine?

Johan
0
 
NosterdamusCommented:
Hi JohanT,

Does this apply to your problem?
http://support.microsoft.com/support/kb/articles/Q171/9/48.asp

Nosterdamus
0
 
NosterdamusCommented:
If it does, then you might find this article (ACC: How to Create an SQL Pass-Through Query in Code) helpful:
http://support.microsoft.com/support/kb/articles/Q112/1/08.asp

Hope this helps,

Nosterdamus
0
 
NosterdamusCommented:
Sorry for the multiple posts, but this article (Create the ODBC connection string for an SQL pass-through query) could be helpful as well:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/achowUseOdbcConnectionStringBuilderCreateConnectioS.asp
0
 
JohanTAuthor Commented:
Hello Nosterdamus,

Thanks for your links. It could be that your pointing into the correct direction but there are some points (strange) left. It could be that subqueries with ODBC-linked tables are the problem, I wasn't thinking about that. But we cannot solved that (I think) because we are using them in forms.
There is still a point that I don't understand. There are some WinNT machines that work incorrect but also some that work correct. We tried to check the ODBC-driver version number, but we couldn't find a difference.
Any Idea why there is a difference? Could we get all the systems working in the correct way?

Johan
0
 
Jonathan KellyCommented:
does each machine have its own copy of the access database?


0
 
JohanTAuthor Commented:
Sorry that I let you waiting so long.

We have a database on our network and if anyone needs the database, he opens the database.
So for each machine it is the same database with the same data but with different results.

Johan
0
 
NosterdamusCommented:
Hi JohanT,

Are all the NT machines use the same SP?

If I'm not miskaging, the latest and most stable SP is 6. Micro$oft reccomends to upgrade to this one....

Nosterdamus
0
 
NosterdamusCommented:
GRrrrrrrr,

If I'm not miskaging

Should be:
If I'm not mistaking

;-)
0
 
JohanTAuthor Commented:
You were pointing into the correct direction Nosterdamus!
Solution: use the english version of office (some were dutch but also the english version was giving problems) and install SR2.

Thanks!!

Johan
0
 
NosterdamusCommented:
Glad I could help,

Nosterdamus
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 10
  • 9
  • 3
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now