PDA

View Full Version : Getting a list of databases for SQL Server name...



sogilvie
05-19-2004, 03:18 PM
Hi folks,

I am trying to get a list of databases from a specific SQL Server <I got the specific SQL Server by getting a list of sql servers on the current domain and getting the user to select one...) I used this function to get the list of sql servers: <I am using SQLDMO>

function GetListServers(listServer)
OBJECT oNameList, oDMOApp;
STRING szDMOApp, szNameList;
NUMBER nCount;
begin
szDMOApp = "SQLDMO.Application";
set oDMOApp = CreateObject(szDMOApp);
szNameList = "SQLDMO.NameList";
set oNameList = CreateObject(szNameList);
set oNameList = oDMOApp.ListAvailableSQLServers;

for nCount = 1 to oNameList.Count
ListAddString( listServer, oNameList(nCount), AFTER);
endfor;
set oDMOApp = NOTHING;
set oNameList = NOTHING;

end;

so now I have the name of the SQL Server, now I want to get the databases that this server has... SQL DMO isn't the that well documented... any ideas how I can get the list of databases???

thanks in advance,

hidenori
05-20-2004, 11:38 AM
Here is a sample C++ code to get a list of databases using SQL-DMO:



CComPtr<IWSQLDMOServer> spDMOServer;
CoCreateInstance(CLSID_SQLDMOServer, NULL, CLSCTX_INPROC_SERVER, IID_ISQLDMOServer, (LPVOID*)&spDMOServer);

spDMOServer->Connect(bstrServer, bstrUserName, bstrPassword);

CComPtr<IWSQLDMODatabases> spDatabases;
spDMOServer->GetDatabases(&spDatabases);

long lNumDB;
spDMOServer->GetDatabaseCount(&lNumDB);

for(long lCount = 0; lCount < lNumDB; lCount++)
{
CComPtr<IWSQLDMODatabase> spDatabase;
spDatabases->GetItemByOrd(lCount,&spDatabase);

BOOL bSystemObject = FALSE;
spDatabase->GetSystemObject(&bSystemObject);
if(!bSystemObject)
{
stringx strDBName;
spDatabase->GetName(&strDBName);
vecDatabases.push_back(strDBName);
}
}


Hope this helps.
Hidenori Yamanishi
InstallShield Software Corporation

sogilvie
05-20-2004, 11:52 AM
merci beaucoup mon ami... :)

I will "translate" it to installscript.

thanks,

sogilvie
05-20-2004, 01:43 PM
darn.. I want to use InstallScript.. it should be possible, I am using InstallScript to get the list of servers <which works wonderfully>
here is what I did, but it crashes when displaynig the sdoptionlist

function FillDatabaseList(listDataBase)
OBJECT oDataBase, oSQLServer;
STRING szoDataBases, szoDataBase, szSQLServer, szDataBaseName;
STRING szUserName, szPassword, szServerName;
NUMBER lCount, lnumDB, nCount;
begin

/* connect to SQL Server */
szSQLServer = "SQLDMO.SQLServer";
set oSQLServer = CreateObject(szSQLServer);
szoDataBases = "SQLDMO.DataBases";
set oDataBase = CreateObject(szoDataBases);
szoDataBase = "SQLDMO.Database";

/* Set the login timeout */
oSQLServer.LoginTimeout = 15;
/* Decision code for login authorization type: WinNT or SQL Server */
oSQLServer.LoginSecure = TRUE;
szUserName = "";
szPassword = "";
szServerName = szSQL_Server;
oSQLServer.Connect(szServerName, szUserName, szPassword);

set oDataBase = oSQLServer.Databases;

/* Enumerate all of the databases and add the names to the list box. */
for lCount = 1 to oDataBase.Count
if ( oDataBase.SystemObject = FALSE ) then
ListAddString( listDataBase, oDataBase.Name(lCount), AFTER);
endif;
endfor;

set oSQLServer = NOTHING;
set oDataBase = NOTHING;
end;

hidenori
05-20-2004, 05:56 PM
I assume it is crashing on the following line:

szoDataBases = "SQLDMO.DataBases";
set oDataBase = CreateObject(szoDataBases);

You don't have to do that, as oSQLServer.Databases will create the object.

Anyway, I would suggest that you debug your code to see where it crashes.

Hidenori

sogilvie
05-21-2004, 02:46 PM
YES!! I finally got it to work!!
I went on to the www.Planet-Source-Code.com website and looked for SQLDMO VB source found some and fiddled with it, here is what I got:

function FillDatabaseList(listDataBase)
OBJECT oDataBase, oSQLServer;
STRING szoDataBase, szSQLServer;
STRING szUserName, szPassword, szServerName;
NUMBER nCount;
begin

/* connect to SQL Server */
szSQLServer = "SQLDMO.SQLServer";
set oSQLServer = CreateObject(szSQLServer);
/* Set the login timeout */
oSQLServer.LoginTimeout = 15;
/* Decision code for login authorization type: WinNT or SQL Server */
oSQLServer.LoginSecure = TRUE;
szUserName = "";
szPassword = "";
szServerName = szSQL_Server;
oSQLServer.Connect(szServerName, szUserName, szPassword);

/* Now were are connected, get the list of databases */
szoDataBase = "SQLDMO.Database";
set oDataBase = CreateObject(szoDataBase);

for nCount = 1 to oSQLServer.Databases.Count
set oDataBase = oSQLServer.Databases(nCount);
if ( oDataBase.SystemObject = FALSE ) then
ListAddString( listDataBase, oDataBase.Name, AFTER);
endif;
endfor;

set oSQLServer = NOTHING;
set oDataBase = NOTHING;
end;

works like a charm :)

Hertzgog
07-20-2007, 12:52 PM
Can someone point me to a tutorial or template for an Install that you can enter the SQL connection info, get a list of available Databases to select from and run a simple SQL script on?

I need to create an install by the end of next week and all help / suggestions are GREATLY appreciated!

Thanks in advance!

hidenori
07-20-2007, 01:12 PM
You may want to consider upgrading to IS 2008. The new version of InstallShield now introduces the built-in support for the database browse functionality. All you need to do is to add a connection and your SQL script files in the SQL Scripts view, and then build your setup. At runtime, clicking the Browse button next to the the Database Name field on the SQLLogin dialog brings you a list of database names available on the target database server. You can select one of the databases from the list to bring it into the Database Name field. If you are interested in, you can download the evaluation version of IS 2008 from http://www.macrovision.com/downloads.htm to try it out.

Regards.