Query on SQL Server 2000 Linked Table Doesnt Provide Correct Results

[Benjamin]

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');

 

[Allen Browne]

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

 

[Benjamin]

Thanks for the quick reply.

The string is actually built dynamically and would contain any
trailing characters if they existed. I think it has to be how Access
interprets the query because a direct copy and paste into query
analyzer gives the correct results.

Maybe I can create a stored procedure in SQL server instead? Is there
a way of calling these without changing the database to an ADP?

Thanks.
Ben

 

[Allen Browne]

Could you use a pass-through query?

 

[Benjamin]

I don't think that would work. Thanks anyways, I've been able to
manage a connection to a stored procedure to do the work I need

Thanks.

Ben