Re: Weird query results -- freetds query results differ from native windows

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,
> please?

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
client.  

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.  

HTH.  

--jkl