MS SQL Server ODBC - Problem with way to access all recordsets returned by sp

Author

Problem with way to access all recordsets returned by sp_spaceused using ODBC APIs on C++

Ashutosh

2006-12-13, 7:12 pm

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_H
ANDLE_STMT,hDBC,&hStmt);

if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INF
O)
{
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_HA
NDLE_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

Chris Kushnir

2006-12-13, 7:12 pm

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

Ashutosh

2006-12-14, 5:27 am

Thanx alot for your help :)!

Chris Kushnir wrote:
> 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

Sponsored Links