Solved

Obtaining the SQL Server 2000 alias name through T-SQL

Posted on 2004-09-03
12
300 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

856 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