Click here to Skip to main content
Click here to Skip to main content

SQL Server and Database Enumerator

, 25 Apr 2000
Rate this:
Please Sign up or sign in to vote.
A class that helps enumerate SQL servers, databases and languages.
  • Download demo executable - 8 Kb
  • Download source files - 33 Kb
  • Sample Image - SQLSrvEnumerator.jpg

    Introduction

    This sample helps you to enumerate the list of SQL Servers and the databases and languages that a particular SQL server has.

    The class CSQLInfoEnumerator encapsulates this functionality into it.

    It has the functions:

    BOOL EnumerateSQLServers();
    BOOL EnumerateDatabase(LPCTSTR pszSQLServer,LPCTSTR pszUserId,LPCTSTR pszPwd);
    BOOL EnumerateDatabaseLanguage(LPCTSTR pszSQLServer,LPCTSTR pszUserId,LPCTSTR pszPwd);

    to perform this task. It fills in its result into the CStringArray m_szSQLServersArray, m_szSQLServerDatabaseArray, and m_szSQLServerLanguageArray data members respectively.

    The heart of this class uses the function SQLBrowseConnect that enables you to build upon an incomplete connect string.

    Example of a connect string:

    ODBC;Driver={SQL Server};SERVER=MYSQLSERVER;APP=MFCAPP;WSID=San;DATABASE=mydb;UseProcForPrepare=0; UID=san;PWD=123

    Note: A connect string is used to establish a database connection using the CDatabase Open or OpenEx member functions.

    Passing an incomplete connect string such as ">Driver={SQL Server};." would cause retrieval of a list of SQL servers. When passed to the SQLBrowseConnect it would retrieve of a list of SQL servers as the server information is missing in the connect string. By passing "Driver={SQL Server};SERVER=MYSQLSERVER; APP=MFCAPP; WSID=San;UID=san;PWD=123;UseProcForPrepare=0;" it would retrieve a list of databases since the database information is missing. The RetrieveInformation function in the CSQLInfoEnumerator class encapuslates this function.

    The function SQLDisconnect has to be called at the end of the SQLBrowseConnect browsing operation completion.

    The complete function RetrieveInformation is as follows.

    //Allocate the environment handle
    m_iRetcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hSQLEnv);
    
    if (m_iRetcode == SQL_SUCCESS || m_iRetcode == SQL_SUCCESS_WITH_INFO)
    {
    	//Set the environment attribute to SQL_OV_ODBC3
    	m_iRetcode = SQLSetEnvAttr(hSQLEnv,SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
    	if (m_iRetcode == SQL_SUCCESS || m_iRetcode == SQL_SUCCESS_WITH_INFO) 
    	{
    		//Allocate a connection handle
    		m_iRetcode = SQLAllocHandle(SQL_HANDLE_DBC, hSQLEnv, &hSQLHdbc);
    		if (m_iRetcode == SQL_SUCCESS || m_iRetcode == SQL_SUCCESS_WITH_INFO) 
    		{
    			CString szConnStrOut;
    			//Call SQLBrowseConnect for additional information
    			m_iRetcode = SQLBrowseConnect(hSQLHdbc, (SQLCHAR *)pszInputParam, SQL_NTS, 
    			                              (SQLCHAR *)(szConnStrOut.GetBuffer(MAX_RET_LENGTH)), 
    			                              MAX_RET_LENGTH,&sConnStrOut);
    			szConnStrOut.ReleaseBuffer();
    			
    			//if the look up key is foundfill in the result set
    			SQLDisconnect(hSQLHdbc);
    		}
    		SQLFreeHandle(SQL_HANDLE_DBC, hSQLHdbc);
    	}
    	SQLFreeHandle(SQL_HANDLE_ENV, hSQLEnv);
    }
    

    The CSQLInfoEnumerator class requires linking with the odbc32.lib file.

    The sample application attached uses the CSQLInfoEnumerator class to display the list of SQL Servers, databases and supported languages.

    Limitations

    When the list of languages supported by a particular SQL server is performed, it does not support character translation for the language name meaning you may see special characters and ?'s in the language listing for particular SQL Servers.

    You can reach me at SantoshRao@bigfoot.com

    License

    This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    About the Author

    Santosh Rao
    Architect
    India India
    1993 started with Computers
     
    BE(Computer Science) and MS (Software Systems)
     
    Industry Experience: 10 Years
     
    C, C++, VC++(MFC), .NET, C#, MTS, Queuing, ASP.NET, AJAX, Java, J2EE, SunOne, JMS
     
    Banking, Insurance & Pension,Health Care

    Comments and Discussions

     
    GeneralThanks PinmemberPJ Arends7-Jan-11 7:18 
    AnswerDrivers for later generations of SQL-server PinmemberJonas Hammarberg16-May-09 2:48 
    Hi
     
    As I've been using the same approach for getting all servers for SQL-Server and only getting SQL-2000, 7 and earlier. Missing 2005 and 2008. By supplying later drivers all supporting servers will be returned.
     
    SQL2008: Driver={SQL Server Native Client 10.0}
    SQL2005: Driver={SQL Native Client}
    -SQL2000: Driver={SQL Server}
     
    NB. SQL2005 will find SQL2008-servers.
     
    To find your drivers - HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI
    QuestionUsage of code in commercial offering? PinmemberJHey20084-Nov-08 15:11 
    GeneralOther databases PinmemberFlora PL10-Sep-04 3:08 
    GeneralCannot retrieve list of Databases on Trusted connections PinsussAnonymous25-Oct-02 0:09 
    GeneralRe: Cannot retrieve list of Databases on Trusted connections (Fixed, Added RetrieveLogin) PinsussJacques Beaurain4-Feb-03 11:40 
    GeneralRe: Cannot retrieve list of Databases on Trusted connections (Fixed, Added RetrieveLogin) PinmemberBSR15-Jan-04 2:44 
    GeneralRe: Cannot retrieve list of Databases on Trusted connections (Fixed, Added RetrieveLogin) PinmemberBSR15-Jan-04 2:45 
    GeneralRe: Cannot retrieve list of Databases on Trusted connections (Fixed, Added RetrieveLogin) Pinmemberjbeaurain15-Jan-04 3:15 
    GeneralInternationalization BUG & Repair in SQL Server and Database Enumerator Pinmembergbalog17-Jul-02 2:03 
    GeneralThe modifyed File is here for Internationalization BUG & Repair in SQL Server and Database Enumerator Pinmembergbalog17-Jul-02 2:04 
    GeneralRe: The modifyed File is here for Internationalization BUG & Repair in SQL Server and Database Enumerator PinmemberSantosh Rao17-Jul-02 18:57 
    GeneralSQLBrowseConnect PinmemberSimone Giannecchini16-Dec-01 1:55 

    General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

    Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

    | Advertise | Privacy | Mobile
    Web02 | 2.8.140721.1 | Last Updated 26 Apr 2000
    Article Copyright 2000 by Santosh Rao
    Everything else Copyright © CodeProject, 1999-2014
    Terms of Service
    Layout: fixed | fluid