sql server odbc: Hi all,
I have a problem executing a sp_spaceused on a database.

Please execute sp_spaceused on your local database so that further
discussion will be more easier to understand.


The code snippet is as follows :

SQLCHAR database_size[15];

SQLCHAR unused[15];

The database_size is member of first record set and
unused is member of second recordset returned by the sp_spaceused.

SQLINTEGER rlength;

retcode = SQLAllocHandle(SQL_HANDLE_STMT,hDBC,&hStmt);


if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
retcode = SQLExecDirect(hStmt,(unsigned char *)sqlCommand,
SQL_NTS);

retcode = SQLBindCol(hStmt,2, SQL_CHAR, (SQLPOINTER) &database_size,
sizeof(database_size),&rlength);

//Problem - was not able to bind any
column of the second recordset so not able to fetch the unused space.

if (retcode != SQL_SUCCESS )
{
AfxMessageBox("Error occured while binding");
}

CString csDatabase_size;
//The while loop below returns after the first iteration as only first
recordset is available and nothing is accessible of second recordset so
cant get 'unused' field :(
while ((retcode = SQLFetch(hStmt)) == SQL_SUCCESS)
{
stored the return
values of database_size and unused.
}

SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
}
else
{
AfxMessageBox("error while allocating a statement handle");
}


So the Problem is :
Is there any way to access all recordsets returned by sp_spaceused
using ODBC APIs on C++?

Please reply.
Thanks in advance :)

-Ashutosh

exec sp_spaceused returns:

Result set 1:
database_name
database_size
unallocated space

Result set 2:
reserved
data
index_size
unused

So, from your description you are trying to get database_size (col2) from
first result set, and unused (col4) from second result set.

You have to:
SQLExecDirect()

SQLBindCol( database_name, col2 )
SQLFetch() until all records read from first result set

SQLMoreResults() if returns SQL_SUCESS then you have another result set, so
....
SQLFreeStmt( SQL_UNBIND )

SQLBindCol( unused, col4 )
SQLFetch() until all records read from second result set


cmk

Thanx alot for your help :)!

[quoted text, click to view]