Skip to Content.
Sympa Menu

freetds - Re: [freetds] freeTDS to access sqlserverExpress

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Duncan Kerr" <DKerr AT nyx.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] freeTDS to access sqlserverExpress
  • Date: Thu, 19 Mar 2009 09:41:45 -0000

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




Archive powered by MHonArc 2.6.24.

Top of Page