Ececuting a stored procedure in SQL via an odbc link

[sue]

Hello,

 

We have a SQL outbound business operation which updates a SQL database via an odbc link using a stored prodecure and  ..Adapter.ExecuteProcedureParmArray which works sucessfully.

 

We have set up another operation to access a different SQL database with a different obdc link ( which connects successfully) and have set up the stored procedure and  ..Adapter.ExecuteProcedureParmArray as in the business operation above which works. It gives the following error :-

 

 ERROR <Ens>ErrFailureTimeout: FailureTimeout of 15 seconds exceeded in PAS.ICMLocationUpdate; status from last attempt was ERROR <Ens>ErrGeneral: Retrying Message body 7...@PAS.CV3LocUpdateRequest / 82575 because response 8...@PAS.CV3LocUpdateResponse Status 'ERROR #6022: Gateway failed: BindParameters. + ERROR <Ens>ErrGeneral: SQLState: (HY105) NativeError: [0] Message: [Microsoft][ODBC Driver Manager] Invalid parameter type ' matched ReplyCodeAction 1 : 'E' resulting in Action code R 

 

All the parameter values are strings or nvarchars.

 

Does anyone have any suggestion as we are not having much luck with it.

 

Thanks

Sue 

 

[Marc Lang]

Hi Sue, can you post up the code? Both the COS and the SQL (just the first portion with the parameters)

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com
To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.
To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[sue]

Hi Mark,

 

The start of the SQL stored procedure is as follows: -

 

USE

[production]

GO

/****** Object: StoredProcedure [dbo].[ND_LocationNameUpdate] Script Date: 03/21/2013 10:50:25 ******/

SET

ANSI_NULLS ON

GO

SET

QUOTED_IDENTIFIER ON

GO

ALTER

PROCEDURE [dbo].[ND_LocationNameUpdate]

-- parameters for the stored procedure

@Patlist

NVARCHAR(10),

@NewDescription

NVARCHAR(30)

AS

The business operation code to call this is as below:- 

 

 Class PAS.ICMLocationUpdate Extends Ens.BusinessOperation [ ProcedureBlock ]
{

Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";

Parameter INVOCATION = "Queue";

Method LocationUpdate(pRequest As PAS.CV3LocUpdateRequest, Output pResponse As PAS.CV3LocUpdateResponse) As %Status
{
kill pResponse
set pResponse = $$$NULLOREF


// If a blank Pat List has been passed in then return an empty Response
if pRequest.PatList = ""
{
set pResponse = ##class(PAS.CV3LocUpdateResponse).%New()

QUIT $$$OK
}
// Set up parameters

$$$TRACE("set parameters")

set p(1) = pRequest.PatList
set p(2) = pRequest.PatListDescription
set p = 2

// Call ICM stored procedure ND_LocationNameUpdate

set tSC = ..Adapter.ExecuteProcedureParmArray(.ResultSnapshots, .tOutParms,
"{ CALL dbo.ND_LocationNameUpdate (?,?) }", ,.p)

$$$TRACE("After call")

set pResponse = ##class(PAS.CV3LocUpdateResponse).%New()

Quit tSC
}

 

Thanks

Sue

 

On Thursday, 21 March 2013 09:03:02 UTC, Marc Lang wrote:

Hi Sue, can you post up the code? Both the COS and the SQL (just the first portion with the parameters)

On 20 Mar 2013 12:43, "sue" <susan.pa...@gmail.com> wrote:

Hello,

 

We have a SQL outbound business operation which updates a SQL database via an odbc link using a stored prodecure and  ..Adapter.ExecuteProcedureParmArray which works sucessfully.

 

We have set up another operation to access a different SQL database with a different obdc link ( which connects successfully) and have set up the stored procedure and  ..Adapter.ExecuteProcedureParmArray as in the business operation above which works. It gives the following error :-

 

 ERROR <Ens>ErrFailureTimeout: FailureTimeout of 15 seconds exceeded in PAS.ICMLocationUpdate; status from last attempt was ERROR <Ens>ErrGeneral: Retrying Message body 7...@PAS.CV3LocUpdateRequest / 82575 because response 8...@PAS.CV3LocUpdateResponse Status 'ERROR #6022: Gateway failed: BindParameters. + ERROR <Ens>ErrGeneral: SQLState: (HY105) NativeError: [0] Message: [Microsoft][ODBC Driver Manager] Invalid parameter type ' matched ReplyCodeAction 1 : 'E' resulting in Action code R 

 

All the parameter values are strings or nvarchars.

 

Does anyone have any suggestion as we are not having much luck with it.

 

Thanks

Sue 

 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healthcare-unsub...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healthcare+unsub...@googlegroups.com.