FreeTDS Development Group

["Steve Murphree" <smurph AT smcomp.com>]

----- Original Message -----
From: "James K. Lowden" <jklowden AT schemamania.org>
To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
Sent: Wednesday, July 30, 2003 1:45 AM
Subject: Re: [freetds] Applying Steve Murphee patch (2)


> > This is much clear. I though to use syntax line
> > Server=myserver/myinstance
> > For mssql2k+
> > I never used multiple instance (even on windows..). Can you configure a
> > DSN for no default instance and see configuration entries ?

No, I don't think so.  When setting up a DSN you either have to provide a
port number or a 'Server Alias' and determine the port dynamicly.  If you
are specifying a port, you will get whatever instance is there.  If you are
determining the port dynamicly, the driver setup tries a 'Server Alias'
(instance) -> port match against a port range (not sure what this range is)
on the server and failing that, uses port 1433.  The default instance can be
a MSSQL v6.5, v7, or a 2k server.  If a v6.5 or v7 server is previously
installed on a machine, then an instance of the 2k server cannot be the
default instance.  The default instance is the only instance that uses the
computer name as it's instance name.  All other instances are called named
instances and their names will not be the same as the computer name.

In a multiple instance environment, if you don't know the ports, you had
better know the instance name and determine the port dynamicly.  If you
don't know the instance name but you do know the port, the M$ driver setup
will magicly glean the instance name from the server.  Cute huh?

> <servername> listens on a host at a port.  It's the label we assign the
> host+port combination, for convenience.  Multiple instances are merely
> several ports on a given host.  Give them different names.  No big deal.
> Or am I missing something?

Well...  It is just a name in the end but it is also returned by a call to
SQLGetInfo() and it is also used to confgure the server in the Enterprise
Manager.  Here is some output of an ODBC command line processor:

clp> connect to mstest user smurph
Enter current password for smurph:

Connected to DSN: mstest
 Database Name: TEST
 Instance Name: SPM2003
     DBMS Name: Microsoft SQL Server
  DBMS Version: 08.00.0194

clp>

The instance name is retreived with the call:

ctl->rc = SQLGetInfo(ctl->hcon, SQL_SERVER_NAME, buffer, 255, &outlen);
if (CTL_SUCCEEDED(ctl)) {
    check_con(ctl);
    oprintf(ctl, "   Instance Name: %s\n", buffer);
}

The output is from the windows SQL Server ODBC driver.  So, the FreeTDS ODBC
driver needs to return the instance name ('Server Alias' as M$ Calls it) for
this call in the FreeTDS, not just the hostname.  Where does it get it from?
Well, for one, it is returned in the TDSMSGINFO struct upon an error or
information from the server.  But how would one know this before an error
occurred?  I did have some code that intentionaly caused an error in order
to glean this information from the server at the connection time.  But I
really didn't like having to do this.  The ODBC configuration should store
this information otherwise the above call would not return a correct 'Sever
Alias' until it was gleaned from the server in some fashion.  It need not
interfere with the TDS at all in the end.  Only the odbc.ini entry.

Maybe we should get some terminology straight.  I'll present it in M$'s and
their ODBC driver's setup terms.

Computer Name:  The workstation's or server's Windows name which usually
equates to the NetBIOS netname and might be the primary TCP/IP interface's
hostname.

Server Name:  For the TCP/IP transport, this can be the same as the
'Computer Name' or another local interface's hostname or a dotted quad IP
address.

Server Alias:  It is the SAME as the 'Computer Name' for the default
instance always.  Any other instance will have it's own name.

Port:  port is port.

So the default instance's Server Alias is alsways the same as the Computer
Name but not always the same as the Server Name.  Throw in the fact that the
default instance might be a downlevel server too.  A 2k server can exist
without a default instace meaning that all instances are named.  What I'm
getting at is that the 'Server Alias' for any given port cannot be assumed.
It is only known by the user and the database server.

In the M$ ODBC TCP/IP Driver setup, you can specify 'Server Alias' (Instance
Name), 'Server Name' (Which is the hostname or IP address) and a Port.
Which leads me to believe that the 'Server Alias' is retained in the ODBC
configuration somewhere in the registry.  And it's not in the the ODBC.INI
DSN entry of the registr either!  But neither is port.  Only servername.
So some weird M$ magic is happening to get the rest of the story.

Steve