ODBC TCP/IP Dynamic Determine Port check box

Hopefully a simple answer:

*editted for clarity*

Where does the "Dynamically determine port" check box come from when creating a ODBC connection using TCP/IP in the client configuration? Only shows up in the control panel/adminstrative tools/Data Sources (ODBC) when it is installed. I have 3 computers here that have it (they are for development) but I am finding my users desktops do not have the box and so I am getting errors trying to connect from their desktops.

I am using Excel and Microsoft Access 2000 to connect to the server using the ODBC connection.

I need my users to connect to SQL Server 2005 so I need the "Dynamically determine port" box checked to find the port. Tried typing in the port but that isn't solving the problem or answering my question. So far I have tried MDAC and .NET 2.0 platform installs with no luck.

Thanks for the help,

WHich driver are you using in those cases, the SQL Server driver or the SQL native CLient driver ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||SQL Server|||"Dynamically determine port" is used with SQL Server driver to locate the port for a named instance. So if you are connecting to a named instance of SQL Server the driver will attempt to dynamically find the port. The port is served up by the SQL Browser service so you need to insure the SQL Browser service is running on the target machine.|||

Matt Neerincx wrote:

"Dynamically determine port" is used with SQL Server driver to locate the port for a named instance. So if you are connecting to a named instance of SQL Server the driver will attempt to dynamically find the port. The port is served up by the SQL Browser service so you need to insure the SQL Browser service is running on the target machine.

Please understand I'm not a DBA and I have to wear severals hats at my company. Everything I'm reading says SQL Browser service runs on the server, not the client. I can get connect to the server fine on all my desktops, even if I set the port manually without checking "Dynamically determine port".

Problem is, I cannot get this to work on other desktops. The "dynamically determine port "checkbox is not present and typing in the port doesn't work either. I think there is simply some driver or install I need to do but I can't seem to figure out which one it is.

|||

What is the version of Windows that does not have the "dynamically determine port" checkbox? Is it possibly Windows Server 2000 (or older)?

From the discussion above I assume that your server is installed as a named instance (i.e. something like <serverName>\<instanceName> instead of just <serverName>. The SQL Server ODBC driver on Windows Server 2000 does not support connections to named instances, so this could be one reson you don't see the checkbox.

If the desktops that do not have the "dynamically determine port" checkbox are Windows Server 2000 (or older) you can install on them the latest version of Microsoft Data Access Components (MDAC), which contains ODBC SQL Server driver supporting named instances.

You can download MDAC from http://msdn.microsoft.com/data/ref/mdac/downloads/. The latest version is MDAC 2.8 SP1 (http://www.microsoft.com/downloads/details.aspx?familyid=78cac895-efc2-4f8e-a9e0-3a1afbd5922e&displaylang=en).

|||

Peter you are the man!

I had loaded MDAC but it was version 2.5. Apparently that wasn't good enough. As soon as I loaded 2.8 sp 1 the check box appeared and more importantly I tested on a desktop that couldn't connect and voila, it worked!

Thanks a heap!

P.S. Did a little more research and this article seems to explain it a little more that MDAC 2.6 and above is the answer...I was soooo close with 2.5 :

http://support.microsoft.com/kb/265808/en-us