Lets face it no matter where you work, if you’ve been there long enough you find out that some piece of legacy software (perhaps an internal tool) is still being used but hasn’t been updated for a decade or two. Think about all those versions of the .NET framework dating back to 1.1 and 1.0, but let me take you all back to a distant time, a time where integers could actually contain the average debt of the typical citizen (32767), yes I’m talking about the days of 16 bit software. These days included Borland C++, Turbo C, Microsoft Quick C, Watcom C, etc.
Today I was asked to find out what would be required to get an old 16 bit tool working with SQL Server named instances (imagine the madness in such a request)! The good news is that it is possible. I’ll try to save you the wasted time and cut to the main points.
1. When you install named instances on a Server running SQL Server each named instance gets allocated a dynamic port # (tcpip) or a dynamic named pipe. To find out the port # that matches a given named instance check out this link here.
Below I have pasted the important parts:
SQL Server 2000
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\(InstanceName)\MSSQLServer\SuperSocketNetLib\TCP
SQL Server 2005
There is no distinction between default and named instances. An instance is assigned a number based on the order it was installed. We first need to locate the registry key for the instance, which looks like
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.#
# is the number assigned to the instance. The instance name is stored as the default value for this registry key. For a default instance, it is MSSQLSERVER.
Once the registry key for the instance is found, we know the TCP/IP registry key is
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.#\MSSQLServer\SuperSocketNetLib\TCP\IPAll
SQL Server 2008
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\TCP\IPAll
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.(InstanceName)\MSSQLServer\SuperSocketNetLib\TCP\IPAll
However, if two SQL Server products exist on one host, then the location of the registry key could differ from above. For example, if SQL Server 2005 or 2008 are installed on the same host after SQL Server 2000, then the TCP/IP registry key of SQL Server 2005 or 2008 will follow the SQL Server 2000 format. Say, if a SQL Server 2000 instance INST2000 and a SQL Server 2005 instance INST2005 are installed on a host sequentially, then the registry key for the TCP/IP protocol of the SQL Server 2005 instance would reside at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\INST2005\MSSQLServer\SuperSocketNetLib\TCP, not HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\TCP\IPAll.
2. Now that you have found the port # that is associated to the named instance you want to connect to from your cheesy 16 bit client, simply change the servername to: myserver,12345 (substitue myname and 12345 with the actual server name and the port # you found in step #1) and voila, your 16 bit application can talk to named instances.