Solved

Obtaining the SQL Server 2000 alias name through T-SQL

Posted on 2004-09-03
12
298 Views
Last Modified: 2008-01-16
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
Comment
Question by:fredforsyth
  • 3
  • 3
  • 2
  • +1
12 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11974494
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
 
LVL 9

Expert Comment

by:SoftEng007
ID: 11977379
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
 
LVL 34

Expert Comment

by:arbert
ID: 11980543
"look in the registry;"

But this won't necessarily be the alias their currently using right?  If they have multiple aliases?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 9

Expert Comment

by:SoftEng007
ID: 11984874
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
 
LVL 34

Accepted Solution

by:
arbert earned 84 total points
ID: 11985114
"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
 
LVL 9

Assisted Solution

by:SoftEng007
SoftEng007 earned 83 total points
ID: 11987136
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
 

Author Comment

by:fredforsyth
ID: 12014581
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
 
LVL 18

Assisted Solution

by:ShogunWade
ShogunWade earned 83 total points
ID: 12014821
as i said i thing your only option is to write an extended stored proc.  there is no SQL functionalitiy to return this
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12274677
Difficult call leaned one.    I'll go with the consensus
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now