He's like motherf**king McGuiver, no he's better than McGuiver!
|Jason Mewes (Mall Rats)|
Microsoft and Sybase use different DB-Library date structures and conventions. Notably months can be in the range [0,11] or [1,12]. Pay careful attention to the results of
Precision may surprise you if you pay attention. Microsoft's DB-Library promotes single-precision to double in
dbbind() by appending zeros; C promotes it to the nearest double. FreeTDS relies on the C compiler.
Math libraries vary, too. If porting an application whose output uses functions such at
log(3), expect differences in different implementations. Perfectly consistent results between OSes will require the use of a single math library.
Questions sometimes arise over large varchar types (anything larger than varchar(255)) that became available with Microsoft SQL Server 7.0. When accessing long varchars with TDS protocol version 4.2 or 5.0, these fields will be truncated to 255 characters, due to limitations inherent in the protocol definition. Your best bet in that case is to convert them to text types.
In Microsoft SQL Server 7.0 and later,
varchar types can hold up to 8000 bytes (8000 ASCII characters or 4000 Unicode characters). To move these large
varchars through TDS 4.2, convert them with either a CONVERT as in,
SELECT mycol = convert(mycol, text) FROM mytableor with the newer SQL92 CAST syntax e.g.,
SELECT CAST(mycol as TEXT) FROM mytable
A related problem is that some people have reported problems with text field using TDS version 7.0. One known workaround is to convert long strings to varchar(8000) in your query text with CAST(
variable_name as varchar(8000) ) as
variable_name. Text datatype handling is fixed in FreeTDS 0.60, except for bcp operations.
There is also a bug ("Lions and tigers and bugs! Oh, my!") in Microsoft's implementation of text fields. Disregardless [sic] of their documentation, you must explicitly set the value of
TEXTSIZE, else the text fields will be represented to have a maximum size of 4 gigabytes or so. The usual manifestation is some sort of spurious "out of memory" error or segment fault. To avoid this, set
TEXTSIZE to some reasonable value before querying any TEXT fields. For example, in isql:
1> setAnother way to handle control the default
TEXTSIZE10000 2> go
TEXTSIZEis to use the setting in freetds.conf.
If either your server or your client is a big endian system, pay careful attention to all references to endianism anywhere near FreeTDS. See the section on Little Endian Emulation for details.
Big endian clients may experience difficulty with Microsoft servers. Some versions of SQL Server 7 did not handle these types on these machines correctly, according to the protocol. According to http://support.microsoft.com/support/kb/articles/Q254/1/23.ASP on the Microsoft support site, it's fixed as of service pack 3. Unfortunately, there's no direct way for FreeTDS to know whether or not a service pack has been installed, and how/whether to support the buggy version is an outstanding issue. Your best bet is to apply their patch.
The Knowledge Base article states "The Sybase CT-Lib client is the only known big-endian client that can connect to SQL Server." Depends on who's doing the knowing, of course.
FreeTDS may be unable to connect to the server. The error message will be "Login failed for user 'example'. Reason: Not associated with a trusted SQL Server connection". To solve this, turn on SQL Server authentication:
Open the SQL Server Enterprise Manager,
Select the server,
Right mouse click and choose Properties. A properties window will appear.
Choose the Security tab. The security properties will be displayed.
Change the Authentication field to SQL Server and Windows,
Apply the changes and try again.
These instructions apply to Microsoft SQL Server 7 and SQL Server 2000.