Solved

Stored Procedure Return Parameter

Posted on 2002-04-22
8
1,231 Views
Last Modified: 2012-05-07
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
Comment
Question by:Barry Cunney
  • 4
  • 3
8 Comments
 
LVL 49

Expert Comment

by:DanRollins
ID: 6961139
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
 
LVL 6

Expert Comment

by:Triskelion
ID: 6961547
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
 
LVL 49

Expert Comment

by:DanRollins
ID: 6962115
>>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
 
LVL 17

Author Comment

by:Barry Cunney
ID: 6963388
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 49

Expert Comment

by:DanRollins
ID: 6963645
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
 
LVL 17

Author Comment

by:Barry Cunney
ID: 6963680
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
 
LVL 49

Accepted Solution

by:
DanRollins earned 100 total points
ID: 6963937
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
 
LVL 17

Author Comment

by:Barry Cunney
ID: 6965206
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IntroductionThis article is the second in a three part article series on the Visual Studio 2008 Debugger.  It provides tips in setting and using breakpoints. If not familiar with this debugger, you can find a basic introduction in the EE article loc…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now