Solved

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

Posted on 2001-07-24
28
268 Views
Last Modified: 2009-12-16
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
Comment
Question by:JohanT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
  • 3
  • +5
28 Comments
 
LVL 1

Expert Comment

by:yitz99
ID: 6311521
Confidence aside, it would be a good idea to post your query here for reference.
0
 

Author Comment

by:JohanT
ID: 6311609
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
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6311621
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 3

Expert Comment

by:casassus
ID: 6311629
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
 

Author Comment

by:JohanT
ID: 6311725
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
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6311746
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
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 6312044
are u deffinately linked to the correct sql tables ?
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6312098
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
 

Author Comment

by:JohanT
ID: 6312295
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
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 6312359
can u run make table queries and run the query against the new tables
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6312363
Was your MS Access app developed under Access 97 or Access 2000?
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6312543
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
 

Author Comment

by:JohanT
ID: 6313093
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
 
LVL 9

Expert Comment

by:perove
ID: 6316190
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
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6316320
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
 

Author Comment

by:JohanT
ID: 6316631
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
 
LVL 4

Expert Comment

by:mcmahon_s
ID: 6316793
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
 

Author Comment

by:JohanT
ID: 6317538
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
 
LVL 7

Accepted Solution

by:
Nosterdamus earned 100 total points
ID: 6318008
Hi JohanT,

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

Nosterdamus
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6318047
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
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6318054
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
 

Author Comment

by:JohanT
ID: 6322154
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
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 6322538
does each machine have its own copy of the access database?


0
 

Author Comment

by:JohanT
ID: 6333325
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
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6333336
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
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6333340
GRrrrrrrr,

If I'm not miskaging

Should be:
If I'm not mistaking

;-)
0
 

Author Comment

by:JohanT
ID: 6340795
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
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6343444
Glad I could help,

Nosterdamus
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question