Potentially has

set ANSI_WARNINGS off

set ARITHABORT off

you  can test with 

select SESSIONPROPERTY('ARITHABORT')

You might find it better to use RAISERROR to generate your error to force the try/catch.  Iain

Okay, setting either ANSI_WARNINGS or ARITHABORT to ON at the beginning of my procedure will cause Uniface to return $status = -150 but it still doesn't go into the catch block inside the procedure. So should I just be checking $status after activating my SSP and hope that if $status is equal to 0 everything *should* be okay? I must admit I do not feel very confident about relying on this statuscode rather than my own error logic inside the stored procedure.

Maybe I do not understand you, but I do not see any output parameter in your code. When you look at the example in the documentation topic Service Stored Procedures on Microsoft SQL Server, it uses input and output parameters. CREATE procedure [WORKTODO].[STR_TEST]  @in char(40), @out char(8000) OUTPUT as begin set @out = @in end  

Have you tried wrapping the insert statement in  set nocount on   set nocount off It may be returning a bad internal status.... 

Theo Neeskens said Maybe I do not understand you, but I do not see any output parameter in your code. When you look at the example in the documentation topic Service Stored Procedures on Microsoft SQL Server, it uses input and output parameters. CREATE procedure [WORKTODO].[STR_TEST]  @in char(40), @out char(8000) OUTPUT as begin set @out = @in end    

Sorry, this was my bad, when creating a simplified test to show you guys here on the forum my problem I accidentally removed too much. So to clarify I do have an OUTPUT parameter.

Iain Sharp said Have you tried wrapping the insert statement in  set nocount on   set nocount off It may be returning a bad internal status....   

Thank you very much Ian! This solved my Issue with not getting data back when using SSP.   Now on to my second problem, is there a way to not have to call commit inside Uniface proc when calling stored procedures that perform insert statements? If I execute the SSP using the code below and the underlying stored procedure performs an insert, the table remains locked by Uniface until I call commit. activate "TUSTRUDOK2".STR_TEST(in, out) What I would like todo is do the error handling and handle the commit and rollback from within the stored procedure instead of having todo it in Uniface proc, is this scenario even possible?   Tomas

Tomas, Have you tried to do a "newinstance.... "transaction=TRUE" before your activate statement? I believe that should open a separate path / connection to the database - which you can then perform a separate commit/rollback on. Just remember to delete the instance afterwards in order to close / free up that extra connection . Regards, Knut

Alternatively, try the 

begin transaction jim ... ... commit transaction jim

within the SSP. 

@Knut Creating new instances of SSP's is not possible due to the requirement that SSP's are declared as Stateless components. Even if it was possible it defeats what I'm trying to accomplish, that is not having to call commit in Uniface but allow SQL Server to handle the transaction. @Iain Sharp Sorry but I've already tried that as well, I must admit I find it a bit strange that even if I wrap my procedure inside a transaction and call commit at the end I still have to call commit in Uniface proc.   Any other ideas?

Tomas, Remember, when you go directly to the db (either via raw sql or via a SP) - the Uniface kernel / driver does not know what you're doing. Since you could be inserting / updating / deleting other records outside of the SP - Uniface would either be holding a lock or prepare to insert one or more rows. If the SP is running on the active path from Uniface, but is also called from other parts of your system outside of Uniface, maybe a parameter to the SP would be "I'm from a Uniface app - don't commit" whereas the other part(s) of your system that calls the SP either omit that parameter, or it sets "I'm not from a Uniface app - please commit". Apart from this - I'd say your fight is with the kernel / driver - and in this case, I believe the the kernel / driver is correct... Knut

If the kernel/driver does not know what I'm doing inside my SP then why doesn't the table remain locked if I only do a simple select inside my SP? I suppose I could live with the fact that I have to do commit/rollback in Uniface if I could get a reliable status back from my SP that something went wrong. So I modified my SP like below in hope that I would receive a statuscode back to Uniface telling me something went wrong and I could do a rollback instead of commit. But no, when running the SP below as a SSP from Uniface it will happily return status = 1 and the id output parameter will hold the value of the newly created guid instead of the (for me) expected result of status -1 and id = 00000000-0000-0000-0000-000000000000. Why is the Uniface driver ignoring the fact that I have a try catch block in my SP? [spoiler] CREATE  procedure [STR_TEST] @name varchar(50), @id varchar(40) OUTPUT, @status int OUTPUT as begin SET NOCOUNT ON; begin try --begin transaction jim declare @returnid table (id uniqueidentifier) declare @dummy int     INSERT INTO tjtest(         name     )     output inserted.id into @returnid     VALUES(         @name     )  select @id = CONVERT(VARCHAR(50), r.id) from @returnid r     set @dummy = 4/0 --this code is used to simulate an error inside the SP.      set @status = 1 --commit transaction jim end try begin catch   --rollback transaction jim   set @id = '00000000-0000-0000-0000-000000000000'   set @status = -1 end catch SET NOCOUNT OFF; end [/spoiler] Running the SP from within MS SQL Management Studio will give me the "correct" result, but as I've commented out the transaction/rollback code it will of course still insert the row. [spoiler]declare @id char(40) declare @status int exec [STR_TEST] 'sql', @id output, @status output select @id, @status[/spoiler]

tomas.johansson said If the kernel/driver does not know what I'm doing inside my SP then why doesn't the table remain locked if I only do a simple select inside my SP? ... 

I think this behaviour has nothing to do with Uniface: it is related to the standard MSS locking mode. If you need to move away from "read are locking write" default MSS behaviour I suggest you to have a look to connection modes available in the MSS driver USYS$MSS_PARAMS = lock isolation : level where level should be defined to support snapshot. Snapshots modes are available since SQL2005 but they got more mature and stable with 2008 and 2012. Snapshot support has some collateral effects on TempDB size because snapshot is maintained there. Hope this helps. Regards, Gianni

I think we have to put the locking behavior aside and concentrate on the second part, that is why is the catch part igonerd when calling the stored procedure from Uniface? Is there a setting somewhere that I need to change or what? I've run the query both from within Management Studio as well as created a simple .NET application using a ODBC command and in both cases the execution falls into the catch block inside the Stored Procedure. So what is Uniface (or my settings) doing differently when calling the SP from within Uniface? I had a look with SQL Profiler to see what is actually called when activating an SSP and this is what I found: [spoiler] declare @p1 int set @p1=NULL declare @p4 char(40) set @p4=NULL declare @p5 numeric(38,0) set @p5=1 exec sp_prepexecrpc @p1 output,N'dbo.STR_TEST','fdsf                                              ',@p4 output,@p5 output select @p1, @p4, @p5 exec sp_unprepare 8 [/spoiler] But calling that exakt same code directly in Management Studio works as expected for me, please help!