JezK
Edit File: ChoosingTdsProtocol.html
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>Choosing a TDS protocol version</title><link rel="stylesheet" type="text/css" href="userguide.css" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="home" href="index.html" title="FreeTDS User Guide" /><link rel="up" href="install.html" title="Chapter 3. Install FreeTDS" /><link rel="prev" href="install.html" title="Chapter 3. Install FreeTDS" /><link rel="next" href="name.lookup.html" title="servername Lookup" /></head><body><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">Choosing a <acronym class="acronym">TDS</acronym> protocol version</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="install.html">Prev</a> </td><th width="60%" align="center">Chapter 3. Install <span class="productname">FreeTDS</span></th><td width="20%" align="right"> <a accesskey="n" href="name.lookup.html">Next</a></td></tr></table><hr /></div><div class="sect1"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a id="ChoosingTdsProtocol"></a>Choosing a <acronym class="acronym">TDS</acronym> protocol version</h2></div></div></div><p>The <acronym class="acronym">TDS</acronym> protocol version is probably something you'd rather not know even existed, much less something you'd have to choose. But there's not that much to it, really. Unless you run into an incompatibility, you're best off running with the highest protocol version supported by your server. That's what the vendors' own products do, which is why when you read the Sybase or Microsoft documentation you find no mention of <acronym class="acronym">TDS</acronym> versions. </p><div class="table"><a id="tab.Protocol.by.Product"></a><p class="title"><strong>Table 3.1. Versions of the <acronym class="acronym">TDS</acronym> Protocol, by Product</strong></p><div class="table-contents"><table class="table" summary="Versions of the TDS Protocol, by Product" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Product</th><th>TDS Version</th><th>Comment</th></tr></thead><tbody><tr><td>Sybase before System 10, Microsoft SQL Server 6.x</td><td>4.2</td><td>Still works with all products, subject to its limitations. </td></tr><tr><td>Sybase System 10 and above</td><td>5.0</td><td>Still the most current protocol used by Sybase. </td></tr><tr><td>Sybase System SQL Anywhere</td><td>5.0 <span class="emphasis"><em>only</em></span> </td><td>Originally Watcom SQL Server, a completely separate codebase. Our best information is that SQL Anywhere first supported TDS in version 5.5.03 using the OpenServer Gateway (OSG), and native TDS 5.0 support arrived with version 6.0. </td></tr><tr><td>Microsoft SQL Server 7.0</td><td>7.0</td><td>Includes support for the extended datatypes in <span class="productname">SQL Server</span> 7.0 (such as char/<span class="structname">varchar</span> fields of more than 255 characters), and support for Unicode.</td></tr><tr><td>Microsoft SQL Server 2000</td><td>7.1</td><td>Include support for <span class="symbol">bigint</span> (64 bit integers), <span class="symbol">variant</span> and collation on all fields. Collation is not widely used. </td></tr><tr><td>Microsoft SQL Server 2005</td><td>7.2</td><td>Includes support for varchar(max), varbinary(max), xml datatypes and MARS<a href="#ftn.idm885" class="footnote" id="idm885"><sup class="footnote">[a]</sup></a>.</td></tr><tr><td>Microsoft SQL Server 2008</td><td>7.3</td><td>Includes support for time, date, datetime2, datetimeoffset.</td></tr><tr><td>Microsoft SQL Server 2012 or 2014</td><td>7.4</td><td>Includes support for session recovery.</td></tr></tbody><tbody class="footnotes"><tr><td colspan="3"><div id="ftn.idm885" class="footnote"><p><a href="#idm885" class="para"><sup class="para">[a] </sup></a><span class="emphasis"><em>Multiple Active Result Sets</em></span>.</p></div></td></tr></tbody></table></div></div><p><br class="table-break" /></p><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a id="idm896"></a>Choosing protocol version since <span class="productname">FreeTDS</span> 1.1</h3></div></div></div><p>Version 1.1 improved the discovery of the protocol version. If you are using Microsoft SQL Server is recommended to leave the version to <strong class="userinput"><code>auto</code></strong> (the default). If you are using any Sybase product you could set version to 5.0 to get faster connections (although <strong class="userinput"><code>auto</code></strong> will work too). </p></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a id="idm902"></a>Choosing protocol version before <span class="productname">FreeTDS</span> 1.1</h3></div></div></div><p> Choosing the correct <acronym class="acronym">TDS</acronym> protocol version for use with SQL Server can be confusing. Hopefully, these steps will lead you to the correct version. If you have a Sybase server, you should be able to use version 5.0, otherwise, if you have Microsoft SQL Server refer to the following section. </p><p> </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>Step 1: Find out which FreeTDS version you are running. You can use the command: `tsql -C`</p></li><li class="listitem"><p>Step 2: Find out what version of Microsoft SQL Server you are running.</p></li><li class="listitem"><p>Step 3: Pick the lower TDS Version number out of what matches steps 1 and 2 from the table below.</p></li></ul></div><p> </p><div class="table"><a id="tab.Protocol.by.SQLServerProduct"></a><p class="title"><strong>Table 3.2. What Version of the <acronym class="acronym">TDS</acronym> Protocol Should I use with Microsoft SQL Server?</strong></p><div class="table-contents"><table class="table" summary="What Version of the TDS Protocol Should I use with Microsoft SQL Server?" border="1"><colgroup><col /><col /><col /><col /></colgroup><thead><tr><th>FreeTDS Version</th><th>Microsoft SQL Server Version Supported</th><th>Highest TDS Version Supported</th><th>Microsoft Extended Support End Date</th></tr></thead><tbody><tr><td>1.00</td><td>2016</td><td>7.4</td><td>July 9th, 2024</td></tr><tr><td>1.00</td><td>2014</td><td>7.4</td><td>July 9th, 2024</td></tr><tr><td>1.00</td><td>2012</td><td>7.4</td><td>July 12th, 2022</td></tr><tr><td>0.95</td><td>2008</td><td>7.3</td><td>July 9th, 2019</td></tr><tr><td>0.91</td><td>2005</td><td>7.2</td><td>April 12th, 2016</td></tr><tr><td>0.82</td><td>2000</td><td>7.1</td><td>April 9th, 2013</td></tr><tr><td>0.64</td><td>2000</td><td>7.1</td><td>April 9th, 2013</td></tr></tbody></table></div></div><p><br class="table-break" /> </p><p> NOTE FOR USERS WHO NEED SQL SERVER 2000 SUPPORT (VERY RARE; SQL SERVER HAS BEEN OUT OF EXTENDED SUPPORT SINCE 2013 AND SHOULD *NEVER* BE USED IN PRODUCTION): Years ago, Microsoft didn't officially create a TDS version number until after FreeTDS was released, and it was assumed 8.0 would be the next version; it turned out to be 7.1. Because of this: </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>If you are running FreeTDS Version 0.64 or 0.82 with Microsoft SQL Server 2000, use TDS Version 8.0 instead of 7.1.</p></li><li class="listitem"><p>If you are running FreeTDS Version 0.91 or greater with Microsoft SQL Server 2000, use TDS Version 7.1.</p></li><li class="listitem"><p>Please note, this is ONLY if you need Microsoft SQL Server 2000 support.</p></li></ul></div><p> </p><p>For best results, use the highest version of the protocol supported by your server. If you encounter problems, try a lower version. If that works, though, please report it to the mailing list! </p></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a id="idm970"></a>Regarding obsolete versions</h3></div></div></div><p>In the earlier days of <span class="productname">FreeTDS</span>, Microsoft did not release official specs for the TDS protocol. When MSSQL 2000 (product 8.0) was released, there was semi-official indications from the Microsoft community that the TDS protocol would be version 8.0. So the <span class="productname">FreeTDS</span> developers adopted that version for <span class="productname">FreeTDS</span>. Years later, when Microsoft started releasing official specs of the protocol, it became obvious that the TDS versions that <span class="productname">FreeTDS</span> had labeled 8.0 and 9.0 were actually versions 7.1 and 7.2 respectively.</p><p>Version 8.0 cannot be used from <span class="productname">FreeTDS</span> version 1.3.</p><div class="itemizedlist"><p class="title"><strong>TDS 4.2 has limitations</strong></p><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><acronym class="acronym">ASCII</acronym> only, of course.</p></li><li class="listitem"><p><acronym class="acronym">RPC</acronym> is not supported.</p></li><li class="listitem"><p><acronym class="acronym">BCP</acronym> is not supported.</p></li><li class="listitem"><p><span class="structname">varchar</span> fields are limited to 255 characters. If your table defines longer fields, they'll be truncated.</p></li><li class="listitem"><p>dynamic queries (also called <em class="firstterm">prepared statements</em>) are not supported.</p></li></ul></div><p> The protocol version may also affect how database servers interpret commands. For example, Microsoft SQL Server 2000 is known to behave differently with versions 4.2 and 7.0. Version 7.0 is recommended for compatibility with Microsoft SQL Server tools.</p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="install.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="install.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="name.lookup.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 3. Install <span class="productname">FreeTDS</span> </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> <em class="replaceable"><code>servername</code></em> Lookup</td></tr></table></div></body></html>