[freetds] freeTDS to access sqlserverExpress
DKerr at nyx.com
Thu Mar 19 05:41:45 EDT 2009
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?
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')
(1 row affected)
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
> > 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
> sure). That sort of makes sense, because that's what's returned if
> 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
> a name to the port served by the instance. Unix is full of this sort
> thing, of course; for example, a directory assigns a name to an inode.
> Microsoft could have used/adapted the DNS protocol for its
> map, but chose to invent something either simpler or inferior,
> on how you look at it. The client queries the server at well known
> always 1434, with a tiny UDP message. The client sends the instance
> and the server returns the associated port number, if any.
> Recent snapshots provide a clumsy way to solicit instance information
> 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
> The -L argument causes tsql to list all instances on a given host
> not server. A server is a host-port-protocol tuple). tsql queries
> 1434 for information on all instances. The information is printed
> verbatim, apart from being formatted for human consumption. On the
> server there's only one. Because no -p argument was provided, tsql
> 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,
> system must choose: which do you mean? The freetds.conf man page
> 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
> A secondary weakness is that if the instance name is incorrect, the
> 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:
> the messages are clear, it's easier to know what to fix.
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
More information about the FreeTDS