Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

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.
0
fredforsyth
Asked:
fredforsyth
  • 3
  • 3
  • 2
  • +1
3 Solutions
 
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
 
arbertCommented:
"look in the registry;"

But this won't necessarily be the alias their currently using right?  If they have multiple aliases?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
arbertCommented:
"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
 
SoftEng007Commented:
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
 
ShogunWadeCommented:
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now