MSSQL Database get database

[MsmVc]

Hi all I am using Ms SQL Server 2005.I try to get all databases names of MSSQL.I am able to connect with mssql through this code.

#import ".\sqldmo.dll" no_namespace
#include "afxwin.h"
_SQLServer2Ptr spSQLServer;
CString str1="",str2="",str3="",m_MHOST="";
LPWSTR HOST;
HRESULT hr;
if SUCCEEDED(hr = CoInitialize(NULL))
{
try
{

		if (SUCCEEDED(spSQLServer.CreateInstance(\_\_uuidof(SQLServer2))))
		{
			try
			{
				HOST=(\_bstr\_t)m\_MHOST;
			
				
					spSQLServer->LoginSecure = TRUE;
					spSQLServer->Connect((\_bstr\_t)m\_MHOST);
				
			
				
				str1.Format(\_T("Connected to %s"), (LPTSTR) spSQLServer->Name);
			
		
			}
			catch(\_com\_error pCE)
			{
			
				
				str2.Format(\_T("%s"),(TCHAR\*)pCE.Description());
				MessageBox(str2,"Message");
				return ;
			}
		}
		else
		{
			str3.Format(\_T("Unable to create the SQL Server object.\\n"));
	
		}
	}
	catch(\_com\_error pCE)
	{
		str2.Format(\_T("\\n%s Error: %ld\\r\\n%s\\r\\n%s\\r\\n"),
			(TCHAR\*)pCE.Source(),
					pCE.Error(),
			(TCHAR\*)pCE.Description(),
			(TCHAR\*)pCE.ErrorMessage());
		AfxMessageBox(str2);
		return;
	}


}
else
{
	str2.Format(\_T("Call to CoInitialize failed.\\n"));
}

i try to get database name through this way.

QueryResultsPtr Qry;
CString dbs="SELECT * FROM SYS.DATABASES";
Qry=spSQLServer->ExecuteWithResults((_bstr_t)dbs);

but i am not able to get database name.Please help me

[Durga_Devi]

Hi, Before executing the query "SELECT * FROM SYS.DATABASES", execute this query "USE master" and try ur select query. I think it will work.

[MsmVc]

see my problem is not using master.My problem is how i fetch mssql all database in listbox.

[Durga_Devi]

That what i am saying. If u want to query any sys databases. First we have to switch to master user and then only we can fetch the records from the sysdatabases. From the ordinary user u cant execute the query.

[MsmVc]

ok can you give me some line of example to fetch MSSQL Database?

[MsmVc]

Hello can you give some line of code?

[Durga_Devi]

Give me some time. I dont have sql server now to write sample. will send it by tomorrow.

[MsmVc]

ok i am waiting for your example..

[MsmVc]

hi durga i have solve problem to show database through this code

spDatabases = spSQLServer->Databases;
for(int i=1;i<=spDatabases->Count;i++) {
spDatabase = spDatabases->Item(i);
_bstr_t db_name(spDatabase->GetName());
CString s((LPCSTR)db_name);
m_lsmssqldatabase.AddString(s);
}

Can you give me idea how to show Table Name of particullar database.Plz help me

[Durga_Devi]

Cool yaar! What's the mistake have u done? The query for fetching the table name is "select name from sysobjects where type = 'U'". This query will display the table name that is created by user. Before executing this, run the query "use ".