RE: String as output from SP in C++

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++
    ... SET NOCOUNT ON ... // get admitting and/or ordering physician email addrs ... string from a stored procedure. ... adParamOutput, 2560, var_phy_email); ...
    (microsoft.public.data.ado)
  • RE: String as output from SP in C++
    ... actually had 2 queries in it and the Parameters collection was not cleared ... Thanks Mike - LOL ... string from a stored procedure. ... adParamOutput, 2560, var_phy_email); ...
    (microsoft.public.data.ado)
  • Re: Adding date to parameter
    ... ClaimedDate does appear to have a properly formatted date. ... I am using the following stored procedure: ... -- SET NOCOUNT ON added to prevent extra result sets from ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Using DTS to Insert 1 Second record for every second in a month?
    ... Okay...so you basically need a calendar table by seconds. ... And yes that will perform pretty fast for the type of query ... >set NoCount OFF ... >This stored procedure took about 21 minutes. ...
    (microsoft.public.sqlserver.dts)
  • RE: What stored procedures call this stored procedure?
    ... this in the wrong way - I have the ID of a stored procedure and I'm looking ... > set nocount on ... > declare @rv int ...
    (microsoft.public.sqlserver.programming)