[egenix-users] Strange behavior using mxODBC & SQL Server

[Tim Golden]

> When performing a query such as:
> SELECT * FROM CD  
> WHERE genre_id = 5
> AND artist = 'artist1'
> 
> I sometimes end up with 0 rows even though there is at least 1 with the 
> criteria (as verified against SQL Server using the Enterprise Manager 
> query tool).
> 
> Oddly enough, if I issue a second query changing the AND clauss to
> AND artist = 'artist2'
> 
> I will get results.  If I then re-issue the first query (knowing that the 
> underlying data has not been modified) I get nothing... Do it again, I get

> results.
> 

Not sure if this is pertinent, but speaking as someone who uses
 SQL Server daily professionally, from Python (via Object Craft's 
 Python mssql module)and with Query Analyzer, 
 I can assert that it doesn't do to rely too heavily on the fact that
 "it works in Query Analyzer / Enterprise Manager". What I mean is that
 there are several session-level settings for SQL Server which are set
 implicitly by the SQL Server tools (altho' you can change them) and which
 are not set on a raw connection via Python. For example, there is a setting
 which determines whether NULLs match anything. (Astonishing to me, coming
 from an Oracle background and loudly proclaiming that NULLs never match
 anything). In SQL Server it was set so that NULLs are equal to NULLs; in
 my Python session, it wasn't. So a query which ran fine in Query Analyzer
 failed to produce results on Python, or vice versa.

It doesn't look as though this is the particular issue in your query, altho'
 as you haven't supplied your data I can't be certain, but it's certainly
 worth considering this as a possibility. Check out the SET command in SQL
 Server Help.

TJG

________________________________________________________________________
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________