is the Server Name available to me?

[R3D3]

Within my DLL created via VB6 code I am connecting to an SQL Database and I use a connect string like this....
"userid=sa;password=xxx;data source=yyy; initial catalogue=zzz"
where yyy=Name of SQL Server
yyy=Name of database

How can I access the name of the machine from VB so I dont have to 'hard code' the Name of the SQL Server. Of course I cant hold this within the SQL Server database.
I prefer not to have the overhead of ODBC.

Even better, is there a way of accessing run-time application variables without having to supply them through another IO from external file. I open the connection so many times it would become an unnecessary overhead.

Thanks

 

[TheDrider]

You can use this snippet of VB code to access the computer name. This will only work if the VB app is running on the same machine as SQL Server...

Option Explicit

Private Const MAX_COMPUTERNAME_LENGTH = 31

Private Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long


Public Function Get_ComputerName() As String
    Dim strComputerName As String
    
    On Error Resume Next
    
    strComputerName = String(MAX_COMPUTERNAME_LENGTH + 1, Chr(0)) 'Pad
    
    If GetComputerName(strComputerName, MAX_COMPUTERNAME_LENGTH + 1) = 0 Then
        Get_ComputerName = ""
    Else
        Get_ComputerName = FixNullTermString(strComputerName)
    End If
End Function



'------------------------------------------------------------------------
' Name:     FixNullTermString
' Author:   Dave Miller
' Description:  Most DLLs return C-style strings that are NULL-terminated
'               This will return a VB style string up to the first NULL
' Inputs:   strData - the String to modify
' Return:   String - the same string minus up to the first NULL char
'------------------------------------------------------------------------
Public Function FixNullTermString(strData As String) As String
    Dim pos As Integer
    
    On Error Resume Next
        
    If strData = "" Then
        'If no data then exit function
        FixNullTermString = strData
    Else
        pos = InStr(1, strData, Chr(0))
        If pos = 0 Then
            'There was no NULL in the string
            FixNullTermString = strData
        Else
            'Grab everything up to the NULL
            FixNullTermString = Left(strData, pos - 1)
        End If
    End If
End Function

 

[bjd4jc]

However, if your app is not running on that machine you could use either the Registry, an XML file or even an INI file to store the name of the SQL Server.

I would create a global variable and make the IO call once to get the name and then store it in the variable.

There are FAQ's as well as numerous posts in this forum as to how to do any one of the above mentioned methods. Look in the FAQ section or use Keyword Search capability of this forum to find them.

 

[TheDrider]

If this a full client-side VB app, you could also include a logon screen to connect to a different DB at startup. This is useful for running against a Development vs Production database...

 

[mattKnight]

You could investigate the SQL DMO library which amongst many other things will enumerate the SQL servers on the network.

this assumes that you will only ever have 1 SQL server on you networkbig assumption! or that somehow you will be able to differentiate between the sql servers


Take Care

Matt
If at first you don't succeed, skydiving is not for you.