Link to home
Start Free TrialLog in
Avatar of hearts
heartsFlag for United Kingdom of Great Britain and Northern Ireland

asked on

How to get SQL SERVER 2000 / MSDE 2000 Server Name Or Instance Name

Dear Experts,
        I want to get SQL Server 2000 or MSDE 2000 Server name / instance name installed on clients computer, through coding.


Avatar of Mikal613
Mikal613
Flag of United States of America image

well there could be many instances
you can get them from SQLDMO object

'Add a reference to the Microsoft SQLDMO object

One way to do this makes use of SQL-DMO COM objects. To use SQL-DMO, you
must have the SQL-DMO files on the PC running the app.  If you've installed
MSDE with your Visual Studio install, you should have this already.  Look
for a file called sqldmo.dll.  The MS Knowledge Base article Q258157
describes how to deploy SQL-DMO to support an application.

Once SQL-DMO is in place, you have to create a COM Interop wrapper for
SQL-DMO.  This is much easier than it sounds, because the IDE basically does
everything for you.  In the Solution Explorer, select the project you want
to be able to access the SQL Server list in.  Select References, and
right-click to get the shortcut menu. Choose Add Reference, and you'll get
the Add Reference dialog box.  Choose the COM tab, and look for Microsoft
SQLDMO Object Library, version 8.0 (make sure you use 8.0, not 7, because it
won't work, as I discovered the hard way...). Make sure this name appears in
Selected Components, then click OK.  .NET will create a reference for you,
which should be called SQLDMO, and is actually a file called
Interop.SQLDMO.dll.

Once you have the reference, you can work with the SQL-DMO objects.  I
needed to do something like what you are doing, plus have access to logins,
users, roles etc. on a SQL Server and in a specific database.


Here's a snippet:

        Dim oSQL As New SQLDMO.Application()
        Dim oServerNames As SQLDMO.NameList
        Dim i As Integer

            'use SQL-DMO to list server names
            oServerNames = oSQL.ListAvailableSQLServers
            For i = 1 To oServerNames.Count
                Me.cboServer.Items.Add(oServerNames.Item(i)) ' the server list shows up in a combo box
            Next
            oServerNames = Nothing
            oSQL = Nothing
Avatar of hearts

ASKER

Thanks Mikal613

Its working fine and getting all sqlserver names installed on the network,
but for my own pc its showing "(local)" instead of name, can i get name here?
Secondly can i distinguish b/w sql server names and MSDE names, means which is installed as sql server and which is installed as MSDE.

ThanX
you want the computer name  (local) if not named is your computer name

you can stick this in a bas module and call it when you need it...


Public Declare Function GetComputerNameA _
 Lib "kernel32" (ByVal lpBuffer As String, _
 nSize As Long) As Long

Public Function GetComputerName() As String
    'Dimension some variables.
    Dim TMP As String
    Dim Length As Long
   
    'Create a buffer.
    TMP = Space$(128)
    Length = 128
   
    'Get the Computer's name.
    GetComputerNameA TMP, Length
   
    'Trim it to length and return it.
    GetComputerName = Left$(TMP, Length)
End Function
--------------

SQL isn't and upgrade of MSDE: "they both have exaclty the same engine". The difference is that SQL Svr comes with extra tools (query analyzer etc) and is not limited to 5 simultaneous connections as MSDE is. MSDE is a kind of barebones SQL Server with a connection limit.
Avatar of hearts

ASKER

ThanX alot Mikal613,
actually the problam which i am trying to solve is, i've created a client server application for users located on different locations each user has its own client server application installed and the process is:
On one pc user installed MSDE and application on that pc i'm making connection as you described above by getting computer name because by default MSDE installed with computer name as its instance name. Now when user installs application for his 2nd user on another computer; now problem starts how can i get the name of the MSDE server installed on another computer to connect this application to same database. ?
ASKER CERTIFIED SOLUTION
Avatar of Mikal613
Mikal613
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial