Problem calling SQL stored procedure with InTouch

[pascal22]

Hi,

I try to call a SQL stored procedure from an InTouch (IT) script (IT v9.5)

It works fine to call the stored procedure, and the actions are executed in SQL.
But the problem is that the value (1 row of 1 value) returned by the stored is not received by IT. I means that after the call, the IT Tag configured in the bind list is not affected.

My SQL stored procedure :

CREATE PROCEDURE [dbo].[AjoutVal]
@pValue REAL ,
@pComment CHAR(30)
AS
INSERT IGNORE INTO TableTest
(ValeurProcess,Commentaire)
VALUES
(@pValue,@pComment)

SELECT "ValeurProcess"=@pValue
GO

My Intouch script :

TagResultCode = SQLSetStatement( ConnID#PMDMotLog, "EXEC dbo.AjoutVal "+StringFromReal( dsBE210\reDebTbAct , 1, "f" )+",'AjoutVal à "+$TimeString+"'");

TagResultCode =SQLExecute( ConnID#PMDMotLog , "BindListTableTest1", 0 );

SQLFirst( ConnID#PMDMotLog ) ;

The bind list BindListTableTest1 contains the connection :

Tag1 <--> "ValeurProcess"


When executing, the IT Logger gives the following error :
-1011: No Rows were selected

Furthermore, the stored procedure gives back the good result when it is called localy in the SQL request analyser.

*******

So, is it possible to get the result from a stored procedure in InTouch ??

Or what do I wrong ??


Thanks,

Pascal

 

[dash]

You may want to try to change your stored procedure to

CREATE PROCEDURE [dbo].[AjoutVal]
@pValue REAL ,
@pComment CHAR(30)
AS

SET NOCOUNT ON

INSERT IGNORE INTO TableTest
(ValeurProcess,Commentaire)
VALUES
(@pValue,@pComment)
SET NOCOUNT OFF

SELECT "ValeurProcess"=@pValue
GO


Notice the additional NOCOUNT statements. If I remember right, InTouch will interpret the insert response as a result set which is why you get the empty bind list error. By bracketing all your statements that you do not want a repsponse to InTouch with the NOCOUNT instruction you will get the desired data from the stored procedure. This instruction causes the stored procedure to not provide any output when NOCOUNT is set to ON, remember to set to OFF before the data you want to see.

Hope this works for you,

Darren

 

[pascal22]

Yes !

Thank you for your quick response !

It resolves my problem !! All is ok now!

Thank you!!

 

[dash]

Glad to help. You tested that very quickly.

There used to be a couple of very good InTouch programmers that worked with SQL Server quite a bit that lurked around on the InTouch forums. After getting over a few hurdles like that though, I have not been back on the Wonderware hosted forums in a couple of years. You may want to look over there and see what the state of the forums are.

Darren