• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1251
  • Last Modified:

Stored Procedure Return Parameter

Hi Guys,
I am executing an SQL Server Stored procedure from C++.
This stored procedure returns a value of type int(the queue handle of an SQL Server Profiler Trace).
This is the T-SQL of Stored Procedure:
===============================================
Stored Procedure - sp_chkCSPFeed
================================================
CREATE Procedure sp_chkCSPFeed as
DECLARE @column_value int
SET @column_value =  67108864|1
DECLARE @queue_handle int
EXEC master..xp_trace_addnewqueue 11000, 10000, 95, 90, @column_value,  @queue_handle OUTPUT
EXEC master..xp_trace_seteventclassrequired @queue_handle, 11 ,1 -- RPC:Starting
EXEC master..xp_trace_seteventclassrequired @queue_handle, 10 ,1 -- RPC:Ending
EXEC master..xp_trace_seteventclassrequired @queue_handle, 31 ,1 -- Select

EXEC master..xp_trace_setappfilter @queue_handle, 'Parser', 'SQL Server Profiler%'

EXEC master..xp_trace_setqueuedestination @queue_handle, 2,1, NULL, 'e:\CSPFeed.trc'

EXEC master..xp_trace_startconsumer @queue_handle

return @queue_handle
==========================================================
I am 100% confident that the above Stored Procedure is working correctly as I am successfully executing it from a VB application and reading the return value(i.e. the queue handle).

However in C++ I am not fully clear on the best way to read in the return value.

See my C++ code below to execute the stored procedure(it is successfully executing the stored procedure)

Basically what I am trying to do is to execute my Stored Procedure which executes an SQL Server Profiler trace and returns a queue handle to that trace.
Then I want to subsequently pass this queue handle to another stored procdure which stops and destroys the Profile Trace.

I think where I am faltering is I am not correctly setting up a variable to include in my CreateParameter statement and also I don't think I am correctly reading this variable after the command is executed.

When I debug the program iQHndl is zero.
Any help would be appreciated - let me know if any point needs to be clarified or explained better

Cheers

==========================================================
Stored Procedure - sp_stopchkCSPFeed
==========================================================
// COM initialization
      CoInitialize (NULL);

    // Establishing a connection to the datasource
      
      try
      {
         HRESULT hr = m_pConn.CreateInstance (__uuidof(Connection));
      
         if (FAILED (hr))
         {
               wsprintf( szMsg, "CreateInstance failed." );
                  MessageBox( NULL, szMsg, "main", MB_OK );
         }

            if (FAILED (m_pConn->Open (_bstr_t ("Provider=MSDASQL;DSN=Support;UID=sa;PWD=elijah;"),
                              _bstr_t (""), _bstr_t (""), adModeUnknown)))
            {
                  wsprintf( szMsg, "Cannot open data source" );
                  MessageBox( NULL, szMsg, "main", MB_OK );
            }
      }
      catch ( _com_error &e )
      {
            _bstr_t bstrSource (e.Source());
            _bstr_t bstrDescription (e.Description());
            
      }
            
      try
      {
            _CommandPtr pCommand;
            _ParameterPtr   Param1;
            _RecordsetPtr rs;
            _CommandPtr pCommand2;

            HRESULT hr = pCommand.CreateInstance(__uuidof(Command));

            if (FAILED(hr))
            {
                  wsprintf( szMsg, "CreateInstance failed." );
                  MessageBox( NULL, szMsg, "main", MB_OK );
                  return;
            }

            pCommand->ActiveConnection = m_pConn;
            pCommand->CommandType = adCmdStoredProc;
            pCommand->CommandText = "sp_chkCSPFeed";
            
          _variant_t vQHndl_Id;
             vQHndl_Id.ChangeType(VT_INT);


          Param1 = pCommand->CreateParameter(_bstr_t("o_id"),adInteger,adParamReturnValue,sizeof(int), vQHndl_Id);
        pCommand->Parameters->Append(Param1);      

            pCommand->Execute(NULL, NULL, adCmdStoredProc);
            Param1 = pCommand->Parameters->Item["o_id"];
            int iQHndl = Param1->GetValue().lVal;
            

                  wsprintf( szMsg, "CreateInstance failed." + iQHndl );
                  MessageBox( NULL, szMsg, "main", MB_OK );

            HRESULT hr2 = pCommand2.CreateInstance(__uuidof(Command));

            if (FAILED(hr))
            {
                  wsprintf( szMsg, "CreateInstance failed." );
                  MessageBox( NULL, szMsg, "main", MB_OK );
                  return;
            }

            pCommand2->ActiveConnection = m_pConn;
            pCommand2->CommandType = adCmdStoredProc;
            pCommand2->CommandText = "sp_stopchkCSPFeed";
            
            
            
            _ParameterPtr   Param2;
            Param2 = pCommand2->CreateParameter(_bstr_t("i_id"), adInteger, adParamInput, sizeof(int), _variant_t( (int) iQHndl));
            Param2->Value = _variant_t( (int) iQHndl );
             pCommand2->Parameters->Append(Param2);

            rs = pCommand2->Execute(NULL, NULL, adCmdStoredProc);
      
      
      }      

      catch( _com_error &e )
      {
            _bstr_t bstrSource(e.Source());
            _bstr_t bstrDescription(e.Description());
            
      }
 
0
Barry Cunney
Asked:
Barry Cunney
  • 4
  • 3
1 Solution
 
DanRollinsCommented:
You don't need to mess with parameters -- they always confuse the issue.  If you need to pass in some parameters, just make them part of the SQL command text.

The Execute function returns a recordset.  In this case, it will be a one-column, one-row recordset containing the datum you want.

-- Dan
0
 
TriskelionCommented:
DR> You don't need to mess with parameters
Dan, I agree with you only when not using MFC.

There are some efficiencies gained when parameterized CRecordsets are used.

BCUNNEY, can you use (or) have you used MFC with this?
If you can, it will shift the complication to an area that has some associated documentation.
0
 
DanRollinsCommented:
>>I agree with you only when not using MFC.
I don't see how MFC's wrappers make any difference; it is always possible to specify a complete SQL statement (typically a SELECT statement) whether using MFC CRecordset or ADO or OLEDB.  

>>There are some efficiencies gained when parameterized CRecordsets are used
The efficiency gain would be related on having to pass fewer quote marks and letting the DBMS do a little less parsing.  These gains are 'lost in the noise' in comparison to the I/O time and actual data access time.

-- Dan
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Barry CunneyAuthor Commented:
Hi Guys,
I actually figured out how to do this late last night
Below is the code - The key to the whole thing was how to set up VARIANT to read in return parameter

int iQHndl;
VARIANT vtQHndl;
vtQHndl.vt = VT_I2;      

and also reading in return value from parmeter to variant
to pass to second stored procedure
pCommand->Execute(NULL, NULL, adCmdStoredProc);
                  
iQHndl = Param1->GetValue().iVal;
vtQHndl.iVal = iQHndl;



I have also put the whole thing in a thread as I am trying to write a trace app the will be running a trace at regular intervals.

========================================================
Code
========================================================
#include <process.h>
#include <stdio.h>
#include <windows.h>
#include <conio.h>

#import "msado15.dll" \
            no_namespace \
            rename( "EOF", "adoEOF" )


_ConnectionPtr m_pConn;
char szMsg[80];
int a[ 5 ];

// Thread to execute stored procedure
void ExecuteTrace( void* pParams )
{
      
      while(TRUE)
      {

            // COM initialization
            CoInitialize (NULL);

            // Establishing a connection to the datasource
      
            try
            {
                  HRESULT hr = m_pConn.CreateInstance (__uuidof(Connection));
      
                  if (FAILED (hr))
                  {
                        wsprintf( szMsg, "CreateInstance failed." );
                        MessageBox( NULL, szMsg, "main", MB_OK );
                  }

                  if (FAILED (m_pConn->Open (_bstr_t ("Provider=MSDASQL;DSN=Support;UID=sa;PWD=elijah;"),
                              _bstr_t (""), _bstr_t (""), adModeUnknown)))
                  {
                        wsprintf( szMsg, "Cannot open data source" );
                        MessageBox( NULL, szMsg, "main", MB_OK );
                  }
            }
            
            catch ( _com_error &e )
            {
                  _bstr_t bstrSource (e.Source());
                  _bstr_t bstrDescription (e.Description());
            
            }
            
            try
            {
                  _CommandPtr pCommand;
                  _ParameterPtr   Param1;
                  _RecordsetPtr rs;
                  _CommandPtr pCommand2;

                  HRESULT hr = pCommand.CreateInstance(__uuidof(Command));

                  if (FAILED(hr))
                  {
                        wsprintf( szMsg, "CreateInstance failed." );
                        MessageBox( NULL, szMsg, "main", MB_OK );
                        return;
                  }

                  pCommand->ActiveConnection = m_pConn;
                  pCommand->CommandType = adCmdStoredProc;
                  pCommand->CommandText = "sp_chkTenforeFeed";
            
                  int iQHndl;
                  VARIANT vtQHndl;
                  vtQHndl.vt = VT_I2;      


                  Param1 = pCommand->CreateParameter(_bstr_t("o_id"),adInteger,adParamReturnValue, sizeof(int), vtQHndl);
                  pCommand->Parameters->Append(Param1);      

                  pCommand->Execute(NULL, NULL, adCmdStoredProc);
                  
                  iQHndl = Param1->GetValue().iVal;
                  vtQHndl.iVal = iQHndl;

                  Sleep(30000);
            
                  HRESULT hr2 = pCommand2.CreateInstance(__uuidof(Command));

                  if (FAILED(hr))
                  {
                        wsprintf( szMsg, "CreateInstance failed." );
                        MessageBox( NULL, szMsg, "main", MB_OK );
                        return;
                  }

                  pCommand2->ActiveConnection = m_pConn;
                  pCommand2->CommandType = adCmdStoredProc;
                  pCommand2->CommandText = "sp_stopchkTenforeFeed";
                        
                  _ParameterPtr   Param2;
                  Param2 = pCommand2->CreateParameter(_bstr_t("i_id"), adInteger, adParamInput, sizeof(int), vtQHndl);
                  Param2->Value = vtQHndl ;
                   pCommand2->Parameters->Append(Param2);

                  rs = pCommand2->Execute(NULL, NULL, adCmdStoredProc);
      
      
            }      

            catch( _com_error &e )
            {
                  _bstr_t bstrSource(e.Source());
                  _bstr_t bstrDescription(e.Description());
            
            }

            
            //////////////////////////////////////////////////////////////////////////////////////
            // Move file to local drive interrogate file
                  system("move J:\TenforeFeed.trc C:");
            //////////////////////////////////////////////////////////////////////////////////////

            // Interrogate file
            

            //////////////////////////////////////////////////////////////////////////////////////

            
            
            
            
            system("del C:\TenforeFeed.trc");
            //////////////////////////////////////////////////////////////////////////////////////


            Sleep(60000);
      }
}
 
int main( void )
{

  // Execute trace thread      Thread;
  _beginthread( ExecuteTrace, 0, NULL );
   
 
              
0
 
DanRollinsCommented:
Good fix; useful info aabout setting up the adParamReturnValue.  

I suggest that you have Community Support PAQ this question and refund the points since you solved it yourself.

-- Dan
0
 
Barry CunneyAuthor Commented:
Yes DanRollins, I didn't think it would so tricky dealing with the return variable.
I have done the exact same thing in VB and it's very simple you just simply specify return type of adInteger, read it into an integer variable and then subsequently pass this variable to another paramter object for stored procedure which has input paramter of type int.
...but the C++ CreateParamter is a bit trikier.

I was worried that I wasn't explaining the specific root of my problem well enough on EE.


0
 
DanRollinsCommented:
I think that the problem is that in BASIC, all variables are variants.  

I'd also like to clarify my post:  In my SPs, I usually return values via a SELECT statement; eg.,

    SELECT @queue_handle

rather than RETURN.  That way, I get back a one-column, one-field Recordset which is easy to handle.

-- Dan
0
 
Barry CunneyAuthor Commented:
Thanks Dan,
I didn't fully catch your point at first re: returning recordset.
...but yes, now I understand where you are coming from.
makes perfect sense what you are saying and certainly the recordset would be a lot easier to handle.
... It's just I had used Parameters in VB and I was determined to figure out how to use them in C++.


Cheers





0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now