<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Troubleshooting</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REL="HOME" TITLE="FreeTDS User Guide" HREF="index.htm"><LINK REL="PREVIOUS" TITLE="Python" HREF="python.htm"><LINK REL="NEXT" TITLE="Is the server there?" HREF="serverthere.htm"><LINK REL="STYLESHEET" TYPE="text/css" HREF="userguide.css"></HEAD ><BODY CLASS="CHAPTER" BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#840084" ALINK="#0000FF" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="3" ALIGN="center" ><SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN > User Guide: A Guide to Installing, Configuring, and Running <SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN ></TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="bottom" ><A HREF="python.htm" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="80%" ALIGN="center" VALIGN="bottom" ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="bottom" ><A HREF="serverthere.htm" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="CHAPTER" ><H1 ><A NAME="TROUBLESHOOTING" ></A >Chapter 8. Troubleshooting</H1 ><TABLE BORDER="0" WIDTH="100%" CELLSPACING="0" CELLPADDING="0" CLASS="EPIGRAPH" ><TR ><TD WIDTH="45%" > </TD ><TD WIDTH="45%" ALIGN="LEFT" VALIGN="TOP" ><I ><P ><I >He's like motherf**king McGuiver, no he's better than McGuiver!</I ></P ></I ></TD ></TR ><TR ><TD WIDTH="45%" > </TD ><TD WIDTH="45%" ALIGN="RIGHT" VALIGN="TOP" ><I ><SPAN CLASS="ATTRIBUTION" >Jason Mewes (Mall Rats)</SPAN ></I ></TD ></TR ></TABLE ><DIV CLASS="TOC" ><DL ><DT ><B >Table of Contents</B ></DT ><DT ><A HREF="troubleshooting.htm#KNOWNISSUES" >Known Issues</A ></DT ><DT ><A HREF="serverthere.htm" >Is the server there?</A ></DT ><DT ><A HREF="logging.htm" >Logging</A ></DT ><DT ><A HREF="pagenodata.htm" >"Page contains no data"</A ></DT ><DT ><A HREF="seemtooslow.htm" >Slow connection or data retrieval</A ></DT ></DL ></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="KNOWNISSUES" >Known Issues</A ></H1 ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="TEXTFIELDS" ><SPAN CLASS="TYPE" >Text</SPAN > Fields</A ></H2 ><P >Questions sometimes arise over large <SPAN CLASS="TYPE" >varchar</SPAN > types (anything larger than <SPAN CLASS="TYPE" >varchar(255)</SPAN >) that became available with Microsoft <SPAN CLASS="PRODUCTNAME" >SQL Server 7.0</SPAN >. When accessing long <SPAN CLASS="TYPE" >varchar</SPAN >s with <ACRONYM CLASS="ACRONYM" >TDS</ACRONYM > 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 <SPAN CLASS="TYPE" >text</SPAN > types. </P ><P >In Microsoft <SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN > 7.0 and later, <CODE CLASS="STRUCTNAME" >varchar</CODE > types can hold up to 8000 bytes (8000 <ACRONYM CLASS="ACRONYM" >ASCII</ACRONYM > characters or 4000 Unicode characters). To move these large <CODE CLASS="STRUCTNAME" >varchar</CODE >s through <ACRONYM CLASS="ACRONYM" >TDS</ACRONYM > 4.2, convert them with either a <B CLASS="COMMAND" >CONVERT</B > as in, <PRE CLASS="SCREEN" ><KBD CLASS="USERINPUT" >SELECT mycol = convert(mycol, text) FROM mytable</KBD > </PRE > or with the newer SQL92 <B CLASS="COMMAND" >CAST</B > syntax e.g., <PRE CLASS="SCREEN" ><KBD CLASS="USERINPUT" >SELECT CAST(mycol as TEXT) FROM mytable</KBD ></PRE > </P ><P >A related problem is that some people have reported problems with <SPAN CLASS="TYPE" >text</SPAN > field using <ACRONYM CLASS="ACRONYM" >TDS</ACRONYM > version 7.0. One known workaround is to convert long strings to <SPAN CLASS="TYPE" >varchar(8000)</SPAN > in your query text with <B CLASS="COMMAND" >CAST( <CODE CLASS="PARAMETER" >variable_name</CODE > as <SPAN CLASS="TYPE" >varchar</SPAN >(8000) ) as <CODE CLASS="PARAMETER" >variable_name</CODE ></B >. Text datatype handling is fixed in <SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN > 0.60, except for bcp operations. </P ><P >There is also a bug (<SPAN CLASS="QUOTE" >"Lions and tigers and bugs! Oh, my!"</SPAN >) in Microsoft's implementation of <SPAN CLASS="TYPE" >text</SPAN > fields. Disregardless [sic] of their documentation, you must explicitly set the value of <CODE CLASS="ENVAR" >TEXTSIZE</CODE >, 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 <SPAN CLASS="QUOTE" >"out of memory"</SPAN > error or segment fault. To avoid this, set <CODE CLASS="ENVAR" >TEXTSIZE</CODE > to some reasonable value before querying any <SPAN CLASS="TYPE" >TEXT</SPAN > fields. For example, in <SPAN CLASS="APPLICATION" >isql</SPAN >: <PRE CLASS="SCREEN" ><SAMP CLASS="PROMPT" >1></SAMP ><KBD CLASS="USERINPUT" >set <CODE CLASS="ENVAR" >TEXTSIZE</CODE > 10000</KBD > <SAMP CLASS="PROMPT" >2></SAMP ><KBD CLASS="USERINPUT" >go</KBD ></PRE > Another way to handle control the default <CODE CLASS="ENVAR" >TEXTSIZE</CODE > is to use the setting in <A HREF="freetdsconf.htm#FREETDSCONFFORMAT" ><TT CLASS="FILENAME" >freetds.conf</TT ></A >. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="ENDIANISM" >Endianism</A ></H2 ><P >If either your server or your client is a big endian system, pay careful attention to all references to endianism anywhere near <SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN >. See the section on <A HREF="configs.htm#EMULLE" >Little Endian Emulation</A > for details. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="DATETIME" ><SPAN CLASS="TYPE" >Datetime</SPAN > and <SPAN CLASS="TYPE" >Money</SPAN ></A ></H2 ><P >Big endian clients may experience difficulty with Microsoft servers. Some versions of <SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN > 7 did not handle these types on these machines correctly, according to the protocol. According to <A HREF="http://support.microsoft.com/support/kb/articles/Q254/1/23.ASP" TARGET="_top" > http://support.microsoft.com/support/kb/articles/Q254/1/23.ASP</A > on the Microsoft support site, it's fixed as of service pack 3. Unfortunately, there's no direct way for <SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN > 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. <DIV CLASS="NOTE" ><P ></P ><TABLE CLASS="NOTE" WIDTH="100%" BORDER="0" ><TR ><TD WIDTH="25" ALIGN="CENTER" VALIGN="TOP" ><IMG SRC="../images/note.gif" HSPACE="5" ALT="Note"></TD ><TD ALIGN="LEFT" VALIGN="TOP" ><P >The Knowledge Base article states <SPAN CLASS="QUOTE" >"The Sybase CT-Lib client is the only known big-endian client that can connect to <SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN >."</SPAN > Depends on who's doing the knowing, of course. </P ></TD ></TR ></TABLE ></DIV > </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="INTEGRATEDSECURITY" >Microsoft's <SPAN CLASS="QUOTE" >"Integrated Security"</SPAN ></A ></H2 ><P ><SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN > may be unable to connect to the server. The error message that appears will be <SAMP CLASS="COMPUTEROUTPUT" >"Login failed for user 'example'. Reason: Not associated with a trusted SQL Server connection"</SAMP >. To solve this, turn on <SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN > authentication:</P ><P ></P ><UL ><LI ><P >Open the <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" ><SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN > Enterprise Manager</I ></SPAN >, </P ></LI ><LI ><P >Select the server, </P ></LI ><LI ><P >Right mouse click and choose <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >Properties</I ></SPAN >. A properties window will appear. </P ></LI ><LI ><P >Choose the <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >Security</I ></SPAN > tab. The security properties will be displayed. </P ></LI ><LI ><P >Change the <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >Authentication</I ></SPAN > field to <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" ><SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN > and Windows</I ></SPAN >, </P ></LI ><LI ><P >Apply the changes and try again. </P ></LI ></UL ><P >These instructions apply to Microsoft <SPAN CLASS="PRODUCTNAME" >SQL Server 7</SPAN > and <SPAN CLASS="PRODUCTNAME" >SQL Server 2000</SPAN >. </P ><DIV CLASS="SECT3" ><H3 CLASS="SECT3" ><A NAME="BACKGROUND" ><SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN >'s Security Model</A ></H3 ><P >Microsoft supports two security models in three permutations: <P ></P ><UL ><LI ><P ><I CLASS="FIRSTTERM" >Windows NT Authentication Mode</I >. The operating system performs authentication; users will <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >not</I ></SPAN > have explicit <SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN > accounts. </P ></LI ><LI ><P ><I CLASS="FIRSTTERM" >Standard Mode</I >. <SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN > authenticates connections itself without consulting the operating system. Users of course need <SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN > accounts to log in. </P ></LI ><LI ><P ><I CLASS="FIRSTTERM" >Mixed Mode</I > Combines the above two. </P ></LI ></UL > For normal operation, you need either Standard or Mixed mode. </P ><P ><SPAN CLASS="QUOTE" >"Windows NT Authentication"</SPAN >, often called <SPAN CLASS="QUOTE" >"integrated security"</SPAN >, relies on Microsoft's <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >domain login</I ></SPAN >. In it, a user's network security attributes are established at network login time. When connecting to the database server, <SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN > uses the facilities of the host operating system (<SPAN CLASS="PRODUCTNAME" >Windows NT </SPAN > or similar) to determine the authenticated network username. <SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN > then permits or denies login access based on that network username alone, without requiring a separate login name and password. </P ><DIV CLASS="NOTE" ><P ></P ><TABLE CLASS="NOTE" WIDTH="100%" BORDER="0" ><TR ><TD WIDTH="25" ALIGN="CENTER" VALIGN="TOP" ><IMG SRC="../images/note.gif" HSPACE="5" ALT="Note"></TD ><TD ALIGN="LEFT" VALIGN="TOP" ><P >The <SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN > supports integrated security mode. If you have <SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN > running in integrated (domain) mode along with a Windows PDC, and wish to try it, see <A HREF="domains.htm" >Domain Logins</A > in the <A HREF="configs.htm" >Advanced Configurations</A > chapter.</P ></TD ></TR ></TABLE ></DIV ><P ><SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN > supports the traditional database security model, which Microsoft terms <SPAN CLASS="QUOTE" >"SQL Server Authentication"</SPAN > but is frequently known as <SPAN CLASS="QUOTE" >"standard security"</SPAN >. Username+Password pairs have to be passed to the server explicitly. </P ><P >Mixed Mode allows users to connect using either authentication method. Users who connect through a <SPAN CLASS="PRODUCTNAME" >Windows NT</SPAN > account can make use of trusted connections in either Windows NT Authentication Mode or Mixed Mode. After successful connection to <SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN >, the security mechanism is the same for both modes. </P ></DIV ></DIV ></DIV ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="python.htm" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.htm" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="serverthere.htm" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Python</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" > </TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Is the server there?</TD ></TR ></TABLE ></DIV ></BODY ></HTML >