Porting Issues

Text Fields

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 mytable 

	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>set TEXTSIZE 10000
	2>go

Endianism

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.

Datetime and Money

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.

Microsoft's "Integrated Security"

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.

FreeTDS supports integrated security mode, too. If you have SQL Server running in integrated (domain) mode along with a Windows PDC, and wish to try it, see Domain Logins in the Advanced Configurations chapter. If you have Active Directory you can also use Kerberos, see Kerberos support.