Solved

Obtaining the SQL Server 2000 alias name through T-SQL

Posted on 2004-09-03
12
292 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
Comment Utility
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
Comment Utility
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
Comment Utility
"look in the registry;"

But this won't necessarily be the alias their currently using right?  If they have multiple aliases?
0
 
LVL 9

Expert Comment

by:SoftEng007
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 34

Accepted Solution

by:
arbert earned 84 total points
Comment Utility
"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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Difficult call leaned one.    I'll go with the consensus
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Update foreign key reference after insert 9 30
t-sql month question 8 42
Complex SQL 10 32
When to use an Aggregate Function. 18 33
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 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

17 Experts available now in Live!

Get 1:1 Help Now