Re: OLE DB CAccessor problem

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Erland Sommarskog (esquel_at_sommarskog.se)
Date: 09/10/04 

Date: Fri, 10 Sep 2004 22:52:01 +0000 (UTC)

Peter de Vroomen (peterv[at][spamblock]jaytown[dot]com) writes:
> The actual call to save the message and get the result is as follows:
>
> hr = spParams.Open( m_Session, _T("EXEC ? = dbo.spInsertMessage ?;"),
> NULL, &lRecordCount, DBGUID_DBSQL, true, 0 );
>
> Now, I have tested my stored procedure using the Query Analyser, and I
> know it works. If it was successful, it returns the return value of 0,
> and returns 1 record with 1 column with the resulting identity value in
> it.
>
> However, when I run the SP from my code, I get a debug assertion
> 'GetInterface() != 0'. This is apparently because there is no resulting
> Rowset from the call to the SP. But I know the SP works, I tested it
> using the Query Designer. And if I choose Ignore in the assertion
> dialog, I can see that lRecordCount is 1. Also, the message is there in
> the database, so the SP was successfull. And m_ReturnValue in the
> accessor is set to 0, which denotes 'success'.
>
> I must be making a wrong assumption, but where? It IS possible to have an
> accessor with a PARAM list with an OUTPUT parameter, and a COLUMN list at
> the same time, isn't it?

I don't have any experience of the consumer templates - I access the
OLE DB interfaces directly. When you do it that way, you can indeed get
both the return value and the output parameter back.

I don't know if it helps, but it is better to use ODBC call syntax:

   {? = call dbo.spInsertMessage(?)}

this causes SQLOLEDB to make a remote procedure call, which is usually
the most effective method of calling a stored procedure.

I tried using your syntax with my setup, and it still worked. But experience
from another thread in this group has shown that when the consumer templates
comes into the pictures, the rules appear to change. 

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Relevant Pages