FreeTDS Development Group

["Duncan Kerr" <DKerr AT nyx.com>]

James

I did as you suggested, and it returns null as you suggested.  Does that
mean I can't use the instance option in .freetds.conf, or do I need to
do something else to point to a NULL server?

Duncan

 
lxdevws85 apps% tsql -S mcdb -U FUENV4\\fuadmin -P ful1ffe -Lv
locale is "C"
locale charset is "ANSI_X3.4-1968"
1> select serverproperty('InstanceName')
2> go

NULL
(1 row affected)
1>         

Duncan Kerr
NYSE Technologies 
t +44 (0)20 7655 7214

> -----Original Message-----
> From: freetds-bounces AT lists.ibiblio.org [mailto:freetds-
> bounces AT lists.ibiblio.org] On Behalf Of James K. Lowden
> Sent: 18 March 2009 14:50
> To: FreeTDS Development Group
> Subject: Re: [freetds] freeTDS to access sqlserverExpress
> 
> Duncan Kerr wrote:
> > If I connect using port in my .conf file, I can ask
> > the sqlserver what it's instance name is, but if I then try and
connect
> > using that instance name it fails, ie
> >
> > [mcdb]
> >     host = ntdevmog451
> >     tds version=8.0
> >     port=1433
> >
> > lxdevws85 apps% tsql -S mcdb -U FUENV4\\fuadmin -P ful1ffe
> > locale is "C"
> > locale charset is "ANSI_X3.4-1968"
> > 1> select @@ServerName
> > 2> go
> >
> > NTDEVMOG451
> > (1 row affected)
> 
> Try this query instead:
> 
>       select serverproperty('InstanceName')
> 
> It returns NULL for the default instance (or if there's only one, I'm
not
> sure).  That sort of makes sense,  because that's what's returned if
the
> argument is invalid, and on earlier versions of the server that didn't
> support instances, 'InstanceName' was invalid.
> 
> > I still don't understand the distinction between port and instance
> 
> Much as DNS assigns a name to the machine's IP address, SQL Server
assigns
> a name to the port served by the instance.  Unix is full of this sort
of
> thing, of course; for example, a directory assigns a name to an inode.
> 
> Microsoft could have used/adapted the DNS protocol for its
instance:port
> map, but chose to invent something either simpler or inferior,
depending
> on how you look at it.  The client queries the server at well known
port,
> always 1434, with a tiny UDP message.  The client sends the instance
name,
> and the server returns the associated port number, if any.
> 
> Recent snapshots provide a clumsy way to solicit instance information
from
> the server using tsql:
> 
> $ ~/freetds/build/src/apps/tsql -H $S -U$U -P$P -L
> locale is "C"
> locale charset is "646"
>      ServerName MS03NJS0878
>    InstanceName MSSQLSERVER
>     IsClustered No
>         Version 8.00.194
>             tcp 2500
>              np \\MS03NJS0878\pipe\sql\query
> Missing argument -p, looking for default instance ...
>       found default instance, port 2500
> 1>
> 
> The -L argument causes tsql to list all instances on a given host
(note:
> not server.  A server is a host-port-protocol tuple).  tsql queries
port
> 1434 for information on all instances.  The information is printed
> verbatim, apart from being formatted for human consumption.  On the
above
> server there's only one.  Because no -p argument was provided, tsql
looked
> for the default instance, found it, and connected.  Whoo hoo!
> 
> Now perhaps it's clear why a freetds.conf entry should have either
> instance or port, but not both.  In the event the user includes both,
the
> system must choose: which do you mean?  The freetds.conf man page
mentions
> that instance supersedes port.  That's probably not the best choice,
> though (and might change one day), because port is not subject to
> interpretation, whereas instance requires the server to provide the
port
> number.
> 
> A secondary weakness is that if the instance name is incorrect, the
server
> doesn't answer.  There's no error message.  The user is told the
> connection failed, but not precisely why.
> 
> I'm working, slowly, on sorting all this out, such that connection
> failures and name-lookup failures and login-protocol failures and
> login-authentication failures are all distinct.  I hope this little
> discussion helps explain why I think users need them to be distinct:
if
> the messages are clear, it's easier to know what to fix.
> 
> Regards,
> 
> --jkl
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds