RE: String as output from SP in C++

Doh! It really helps to pay attention to what your doing. The function
actually had 2 queries in it and the Parameters collection was not cleared
prior to the 2nd query. Worked as advertised I guess. Thanks Mike - LOL

"Mike" wrote:

Added
SELECT @s_addr_list_txt

at bottom of query and still getting the IDispatch #3092 error :(

"Mike" wrote:

OK I am now down to this
---------------------------------------------
CREATE PROCEDURE dbo.qry_MyQry
@s_addr_list_txt nvarchar(5)=NULL OUTPUT

AS
SET NOCOUNT ON

SET @s_addr_list_txt = "A" --or 'A', doesnt matter
GO
------------------------------------------------------------------------
Then in my C++ code I have the following:
----------------------------------------------
_CommandPtr cmd("ADODB.Command");
_ParameterPtr param;

cmd->ActiveConnection = _epi_conn;
cmd->CommandText = "qry_MsgEmailPhysEmail";
cmd->CommandType = adCmdStoredProc;

//
// get admitting and/or ordering physician email addrs
//
_variant_t var_phy_email;

//
// setup stored procedure call
//
var_phy_email.vt = VT_BSTR;
var_phy_email.bstrVal = ::SysAllocString(L"");

param = cmd->CreateParameter (_bstr_t("@s_addr_list_txt"), adVarChar,
adParamOutput, 5, var_phy_email);
cmd->Parameters->Append(param);

cmd->Execute(NULL, NULL, adCmdStoredProc);
---------------------------------------------------------------------

Now I get "Procedure or function qry_MsgEmailPhysEmail has too many
arguments specified. -2147217900"

Any hints?

"Mike" wrote:

I can't seem to properly form a CreateParamter statement for retrieving a
string from a stored procedure. The SP looks a bit like this:
---------------------------------------------
CREATE PROCEDURE dbo.qry_MyQry
@l_spec_cd as int,
@s_addr_list_txt nvarchar(2560) OUTPUT

AS
SET NOCOUNT ON

DECLARE @my_email_txt nvarchar(1280)

SELECT @my_email_txt=EmailAddrTxt
FROM tblPhysician
WHERE PhysicianCd = @l_spec_cd

.
.
.


SET @s_addr_list_txt = @s_addr_list_txt + @my_email_txt

GO
----------------------------------------------

Then in my C++ code I have the following:
----------------------------------------------
_CommandPtr cmd("ADODB.Command");
_ParameterPtr param;

email_addr_list = L"";

cmd->ActiveConnection = _epi_conn;
cmd->CommandText = "qry_MsgEmailPhysEmail";
cmd->CommandType = adCmdStoredProc;

//
// get admitting and/or ordering physician email addrs
//
_variant_t var_phy_email;
CString s_phy_email;

//
// setup stored procedure call
//
param = cmd->CreateParameter (_bstr_t("@l_spec_cd"), adInteger,
adParamInput, 4);
cmd->Parameters->Append(param);
param->Value = spec_cd;

var_phy_email.vt = VT_BSTR;
param = cmd->CreateParameter (_bstr_t("@s_addr_list_txt"), adVarChar,
adParamOutput, 2560, var_phy_email);
cmd->Parameters->Append(param);

cmd->Execute(NULL, NULL, adCmdStoredProc);
---------------------------------------------------------------------

I get an exception while doing the second create parameter for the return
string. Not sure if its a a poorly formatted call to CreateParameter,
illegal variable in that call, or some other violation.

Help?!

.

Relevant Pages

  • RE: String as output from SP in C++
    ... "Mike" wrote: ... SET NOCOUNT ON ... string from a stored procedure. ... adParamOutput, 2560, var_phy_email); ...
    (microsoft.public.data.ado)
  • problem invoking stored procedure
    ... I'm trying to invoke a SQL server 7 stored procedure from asp 3.0 and I'm ... PDFLookup.DocId = PDFDocs.DocId inner join PDFCode on PDFLookup.CodeId = ... PDFLookup inner join PDFDocs on PDFLookup.DocId = PDFDocs.DocId inner join ... objDisplayCode.Direction = adParamOutPut ...
    (microsoft.public.inetserver.asp.db)
  • Re: UPDATE problems with Parameters
    ... the parameters defined in your stored procedure. ... Peter ... Prev by Date: ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Passing output parameters to SQL Server stored procedures through an SqlCommand objects Paramete
    ... Microsoft's stored procedure, part of their Great Plains product. ... Is it a bug in the Parameters collection object? ... > As for using ExecuteReader() or ExecuteNonQuery, yes, you can get what ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Stored procedure Output Parameter headache
    ... I have the following code chunk for a page which accesses an SQL ... The stored procedure works fine in Query analyzer, ... adParamOutput) ... declare @RetMsg varchar ...
    (microsoft.public.inetserver.asp.db)