Working with mssql stored procedures
I am trying to create a utility to monitor and record database changes. One of the stored procedures that I would like to use is the sp_spaceused.
Looking at this stored procedure, it returns a value of 1 or 0, but when run in the Query Analyzer, it also returns a recordset detailing the space used details of the database. In C#, how do I review and recordset data? I have tried several iterations of reading this information but it appears as though I can't read it in the application. If the Store Procedure displays a recordset of data in Query analyzer, is it also reasonable to assume that I should be able to read it when called via the application?
Any help is greatly appreciated.
Re: Working with mssql stored procedures
You can use the methods of System.Data.SqlClient library. It has the methods for handling the recordset. In msdn , you can see all the methods detail. Hope that starts up...
Re: Working with mssql stored procedures
Thank you for the reply, but I am already using the SQLClient library in an attempt to expose the data elements, but cannot seem to get it to read the data (I am new to C#).
when I run the sp_spaceused <database> in query analyzer, the return set is like this:
database_name | database_size | unallocated_space
---------------------------------------------------------------------
and also
reserved | data | index_size | unused
------------------------------------------------
in essence 2 recordsets of 1 row each. When I execut ethe stored procedure, the only value i seem to get is the return value (0 success, 1 failure). Can someone provide any resource or code sample of how to handle this? I am almost assuradly missing something very simple and basic.
Re: Working with mssql stored procedures
Use the data adapter with store procedure command type...
MSDN can help
http://msdn.microsoft.com/en-us/library/59x02y99.aspx
Re: Working with mssql stored procedures
Notification services exist to advise you when data in a database changes, but they are not simple to use.. For an example, this might help: http://www.codeproject.com/KB/database/chatter.aspx