[freetds] Weird query results -- freetds query results differ from native windows
James K. Lowden
jklowden at freetds.org
Tue Oct 14 10:21:21 EDT 2008
Patrick Spinler wrote:
> With one set of arguments I receive identical results on both
> freetds/linux and native windows.
> exec inv_search_api 4527
> However, when I execute the following query, I get different results in
> freetds from native windows. Under freetds, the procedure returns an
> interal code indicating a permission error (result code=1001), under
> windows, it returns a large set of result data (the expected result).
> exec inv_search_api 5240
> Would anyone have any clue why, or places to start investigating,
If you use the same account, it can't be a permission error. The server
doesn't know anything about the client environment except that it's a TDS
The procedure is subject to session options though that can affect its
behavior. That's especially true if you're using db-lib or ct-lib in
FreeTDS versus Microsoft's ODBC-based GUI tools. Look into @@OPTIONS,
paying special attention to ANSI defaults.
Something I bump into from time to time is string contatenation with NULL.
If, say, you construct a date string with "month +'/1/'+ year", an ODBC
tool will normally yield NULL if the month/year is NULL, but a db-lib
application will yield at least '/1/' -- which, if converted to a
datetime, results in an error, not a NULL. Thus the procedure that works
fine in Query Analyzer fails with NULLs in FreeTDS or even with
Microsoft's (db-lib based) ISQL.EXE.
More information about the FreeTDS