Chapter 8. Troubleshooting

Table of Contents

Known Issues
Porting Issues
Text Fields
Microsoft's Integrated Security
Is the server there?
Start with ping
Test with telnet
Test with tsql
Logging
Environment Variables that Control Logging
freetds.conf variables that Control Logging
Logging in ODBC land
"Page contains no data"
Slow connection or data retrieval

He's like motherf**king McGuiver, no he's better than McGuiver!

Jason Mewes (Mall Rats)

Known Issues

Porting Issues

Date Structures and Offsets

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 dbdatecrack().

Floating Point

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.

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 

or with the newer SQL92 CAST syntax e.g.,

	SELECT CAST(mycol as TEXT) FROM mytable

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. If you encounter some spurious out of memory error try to set TEXTSIZE to some reasonable value before querying any TEXT fields. For example, in isql:

	1> set TEXTSIZE 10000
	2> go

Another way to handle control the default TEXTSIZE is to use the setting in freetds.conf. As most of the time data contained in BLOBs fields are much smaller than larger supported fields, we try to avoid considering field sizes for BLOBs allocating memory as needed instead, so you should not have to reduce this value unless you really want the server to limit data returned by queries.

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 Microsoft 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™.

[Note]Note

FreeTDS™ supports integrated security mode, too. If you have Microsoft 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.