Solved

find Local SQL Server Name

Posted on 2006-11-02
16
488 Views
Last Modified: 2013-12-25

Hi

I want to find Local SQL Server Name

a) Without connecting to SQL database
b) SQLDMO object gives me “(local)” as Local SQL Server Name
c) Without using NetServerEnum API





0
Comment
Question by:RETAILREALM
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 2
16 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17857063
you could read in the registry, but note that you might find several instances...

default instance:
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQLServer
named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQLServer$Instance_name

0
 

Author Comment

by:RETAILREALM
ID: 17857107
Thanks angelIII for quick reply
But I don’t find it suitable to search registry  :(    [Another Exclusion]
As I don’t know on which OS my application is going to run
I appreciate your help
You have any other idea for this ?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17857133
>But I don’t find it suitable to search registry  :(    [Another Exclusion]
why not? registry reading is not that difficult, and the registry location for the sql server does not change between windows versions.

>You have any other idea for this ?
no
0
Industry Leaders: 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!

 

Author Comment

by:RETAILREALM
ID: 17857235
Thanks angelIII Let’s see if others have any answer to this
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17857619
What SQL Server version?
0
 

Author Comment

by:RETAILREALM
ID: 17857790
Thanks Nightman I have 8.00.194 But dont know which will client have
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17857881
Dim oServer As SQLDMO.SQLServer
Set oServer = New SQLDMO.SQLServer
oServer.Connect "(local)" 'and whatever your credentials are
MsgBox oServer.NetName 'This is the servername (not the instance name!)

Dim qr As QueryResults
Set qr = oServer.ExecuteWithResults("SELECT @@servername")
MsgBox qr.GetColumnString(1, 1) ' this will give you the instance name

I can't see an alternative other than connecting to the server, looking in the registry or enumerating the servers across the network.

HOWEVER, if you are SURE that your default instance will always be the servername, use the  GetComputerName  API
Private Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long


to use:

Dim liRet As Long
Dim sName As String
Dim liSize As Long
Dim sResult As String

sName = Space(255)
liSize = 255
liRet = GetComputerName(sName, liSize)

sResult = Left$(sName, liSize)

Does this help at all?

0
 

Author Comment

by:RETAILREALM
ID: 17858009
Thanks  Nightman for your reply & code
But I don’t want to connect to database, as I don’t know which is ‘Local’ database
I am not sure about default instance  :(
I am finding a way by which I just get the name of SQL server on my pc
Again many thanks for quick reply
Please let me know if u gets any thing
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17858429
You don't want to connect to the server, you don't want to use computer name, you don't want to look in the registry, and you don't want to enumerate them across the network?

With all due respect, there is no programatic crystal ball for you to look into to guess this information. And if none of these meet your requirements, consider changing your requirements.
0
 

Author Comment

by:RETAILREALM
ID: 17858715
Thanks Nightman
I do understand this
Basically I want to check if this particular Sql servers are running suppose  A,B,C
So if I use SQLDMO I may get 10 servers with one of them as ‘Local’ which may be ‘A’ and other two as B & C
So the Server ‘A’ is running but I don’t get its name L
That why my requirements is like this
If you have any other way please let me know


0
 
LVL 29

Expert Comment

by:Nightman
ID: 17859205
I'm not sure I understand. Please correct me if I am wrong:

1. You use the DMO and one of the servers that is returned is called (local).
2. You want to determine the name of the SQL Server instance, which has to be running on the same machine where the DMO is being used.
3. Yet you can't connect to it, can't check the registry and can't enumerate server instances and can't assume the computer name.

I suspect that you are out of options.
0
 

Author Comment

by:RETAILREALM
ID: 17865124
Thanks Nightman
I agree to your point 1 & 2
I have thought a workaround on this
As I have connection details of servers I want to connect
So I will try with ‘local’ server

Dim oServer As SQLDMO.SQLServer
Set oServer = New SQLDMO.SQLServer
oServer.Connect "(local)" 'and whatever your credentials are
MsgBox oServer.NetName 'This is the servername (not the instance name!)

Dim qr As QueryResults
Set qr = oServer.ExecuteWithResults("SELECT @@servername")
MsgBox qr.GetColumnString(1, 1) ' this will give you the instance name


And if any one is at all successful I will assume that ‘local’ =That Server. This seems to be working for me.
Thanks for your help
But still I think there must be some way to get the SQL server name from Computer
I am searching that else I would be happy to award points to you


0
 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
ID: 17865286
Hi RetailRealm

Think about what you are asking, and think about the workarounds.

1. SQL will expose the instance/server name via the NetServerEnum API
2. SQL will store the instance and server name in the registry
3. SQL will expose this server/instance name via the variable (@@servername)

Alternatively:
have a look at http://devpinoy.org/blogs/keithrull/archive/2006/06/08/Programmatically-List-All-SQL-Servers-And-Their-Databases-Using-SMO-.aspx

Using the SMO (from SQL 2005 - there is a C# and VB.NET example) to enumerate servers. For some reason this o nly shows up one of my local instances (the one that matches my machine name) and ignores the rest. HOWEVER, I assume that it is using the NetServerEnum API in the BACKGROUND anyway.

I don't think you will get better than this.
0
 

Author Comment

by:RETAILREALM
ID: 17866609
Thanks Nightman
One more issue SQLDMO gives wrong status
Means even if I closed remote server, it gives me it as running
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17866735
I guess you would have to reconnect to determine the status. That sounds about normal.
0
 

Author Comment

by:RETAILREALM
ID: 17880560
Thanks Nightman
That not the case , anyways Thanks for your Help
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month3 days, 21 hours left to enroll

630 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