Obtaining the SQL Server 2000 alias name through T-SQL

I've got a named instance of SQL Server 2000 with a named-pipe alias. Selecting @@SERVERNAME returns the MACHINE\Instance name, rather than the alias name, even though I've connected through the alias name.

Is it possible to get the alias name using T-SQL?

Thanks
Fred Forsyth
Development Manager, RCP Consultants Ltd.
fredforsythAsked:
Who is Participating?
 
arbertConnect With a Mentor Commented:
"correct arbert my mistake.

the correct key should be:
"


Not exactly what I meant.  There could be multiple aliases on the client machine--just because it exists in the registry doesn't tell you which alias you're currently connected with in your application--right?
0
 
ShogunWadeCommented:
There isnt any built in functionality in sql to acquire this.    unfortunately the networking stuff i pretty much out of the SQL environment.   I suspect it may be possible to acquire this with a bit of C programming.   You could then wrap this into an extended stored proc.   But my C skills are ... shall we say "challenged"
0
 
SoftEng007Commented:
look in the registry;

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

the key name will be your alias and its value will have

DBNMPNTW,\\alias_name\pipe\sql\query

this will find any you have:

create table #Temp(value varchar(255),Data varchar(255))

insert into #Temp
EXEC master..xp_regenumvalues
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo'

select * from #Temp where data like 'DBNMPNTW%'

drop table #Temp
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
arbertCommented:
"look in the registry;"

But this won't necessarily be the alias their currently using right?  If they have multiple aliases?
0
 
SoftEng007Commented:
correct arbert my mistake.

the correct key should be:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\<INSTANCENAME>\Client\ConnectTo


MSSQLServer is used for the default instance.
0
 
SoftEng007Connect With a Mentor Commented:
arbert you are correct. this will list the alias' on the server not on the client.

if the client only has one alias the application could inspect this reg key to get the name.

I think for a client connection you would have to name the alias you were connecting with in the connections string wouldn't you? if so then you should already know the alias.

 How to define which network protocol to use


Example:
"Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=foo;"  = tcp/IP

Example:
"Provider=sqloledb;Data Source=Bob;Network Library=dbnmpntw;Initial Catalog=pubs;User ID=sa;Password=foo;"  = named pipes.. ALIAS Named Bob




Name Network library
dbnmpntw Win32 Named Pipes
dbmssocn Win32 Winsock TCP/IP
dbmsspxn Win32 SPX/IPX
dbmsvinn Win32 Banyan Vines
dbmsrpcn Win32 Multi-Protocol (Windows RPC)


Important note!
When connecting through the SQLOLEDB provider use the syntax Network Library=dbmssocn
and when connecting through MSDASQL provider use the syntax Network=dbmssocn


0
 
fredforsythAuthor Commented:
As someone pointed out, reading the registry would be serverside, and the client maybe connecting from another computer. I want a SQL solution, by the sounds of if, this is not possible.

Anyone else care to have a go?
0
 
ShogunWadeConnect With a Mentor Commented:
as i said i thing your only option is to write an extended stored proc.  there is no SQL functionalitiy to return this
0
 
ShogunWadeCommented:
Difficult call leaned one.    I'll go with the consensus
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.