Re: Query on SQL Server 2000 Linked Table Doesnt Provide Correct Results
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Thu, 28 Jun 2007 23:01:56 +0800
Benjamin, I can't speak to how SQL Server interprets the query, but Access can give the discrepency you describe if a field contains additional trailing characters that are not visible, such as a space or null character (i.e. Chr(0), not the Null value.)
You may be able to use Len() in a calculated query field to determine if the count of characters is not what you expect.
For more details, see:
Trailing spaces give inconsistent query results
at:
http://allenbrowne.com/bug-15.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Benjamin" <benjamin.dizenhouse@xxxxxxxxx> wrote in message news:1183041993.661909.324380@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hello,
I have some backend tables in a SQL Server 2000 Database linked into
my MSACCESS database. When I run the following query in Access I get
0 (zero) results. If I run the same query in query analyzer, I get
the correct results. Can someone please help? I think the problem
has to do with the fact that I am comparing long strings. Originally,
all the tables were in MSACCESS and I found that for some comparisons,
if I only used [Detail] = '..' the match wouldnt work but If I used
[Detail] LIKE '...' (even without wildcards) it would work for some
but not all, thats why I have used both versions). Now, because of
the size of the tables, I though having them in a SQL server database
would help.
Thanks for any help!
Query:
"SELECT *
FROM Scan_UniqueFindings
WHERE [Server] = 'servername' AND
[Type] = 'once-only' AND
[Policy] = 'policy1' AND
[Check] = 'admin' AND
([Detail] = 'User daemon value login is true
User daemon value rlogin is true
User bin value login is true
User bin value rlogin is true
User adm value login is true
User adm value rlogin is true
User nobody value login is true
User nobody value rlogin is true
User lpd value login is true
User lpd value rlogin is true
User nuucp value login is true
User nuucp value rlogin is true
User sys value login is true
User sys value rlogin is true' OR
[Detail] LIKE 'User daemon value login is true
User daemon value rlogin is true
User bin value login is true
User bin value rlogin is true
User adm value login is true
User adm value rlogin is true
User nobody value login is true
User nobody value rlogin is true
User lpd value login is true
User lpd value rlogin is true
User nuucp value login is true
User nuucp value rlogin is true
User sys value login is true
User sys value rlogin is true');
.
- Follow-Ups:
- References:
- Prev by Date: Re: This should be simple but I don't know how!
- Next by Date: Re: This should be simple but I don't know how!
- Previous by thread: Query on SQL Server 2000 Linked Table Doesnt Provide Correct Results
- Next by thread: Re: Query on SQL Server 2000 Linked Table Doesnt Provide Correct Results
- Index(es):
Relevant Pages
|
