Chapter 8. Troubleshooting
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 mytableor 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.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 Microsoft 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 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.
| 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. |