Skip to main content

Executing a stored procedure with an output parameter

Author: walsh.dale.s@edumail.vic.gov.au (dwalsh)

We have traditionally been an oracle shop but now have a number of SQL Server databases too.  We are running Uniface 9.7.01.02.  I have temp license to connect to SQL Server and can successfully execute select and insert statements.  But what I really want to do is execute an existing stored procedure from the SQL server database.  I originally had problems with the syntax but think I've got that correct now.  OFDA is my database connection defined in my .asn file.  OFDASYS.dbo.dt_loadDESAppn is the stored procedure which has 4 input parameters and 1 output. Either of these 2 statements gives me the same error now : SQL "DECLARE @result INT; EXEC OFDASYS.dbo.dt_LoadDESAppn @statements = '%%$statements$', @session_user_id = '%%$user_id$', @school_cd = '%%school_cd.student_yr', @stud_email = '%%email_addr.student_yr', @result = @result OUTPUT;","OFDA" SQL "DECLARE @return_value INT, @result INT; EXEC @return_value = OFDASYS.dbo.dt_LoadDESAppn @statements = '%%$statements$', @session_user_id = '%%$user_id$', @school_cd = '%%school_cd.student_yr', @stud_email = '%%email_addr.student_yr', @result = @result OUTPUT;","OFDA" Error I'm getting is : 42000 [Microsoft][SQL Server Native Client 11.0][SQL Server]The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. I have checked and my connection user has sufficient privileges to execute a stored procedure. Any other suggestions what the problem might be?

Executing a stored procedure with an output parameter

Author: walsh.dale.s@edumail.vic.gov.au (dwalsh)

We have traditionally been an oracle shop but now have a number of SQL Server databases too.  We are running Uniface 9.7.01.02.  I have temp license to connect to SQL Server and can successfully execute select and insert statements.  But what I really want to do is execute an existing stored procedure from the SQL server database.  I originally had problems with the syntax but think I've got that correct now.  OFDA is my database connection defined in my .asn file.  OFDASYS.dbo.dt_loadDESAppn is the stored procedure which has 4 input parameters and 1 output. Either of these 2 statements gives me the same error now : SQL "DECLARE @result INT; EXEC OFDASYS.dbo.dt_LoadDESAppn @statements = '%%$statements$', @session_user_id = '%%$user_id$', @school_cd = '%%school_cd.student_yr', @stud_email = '%%email_addr.student_yr', @result = @result OUTPUT;","OFDA" SQL "DECLARE @return_value INT, @result INT; EXEC @return_value = OFDASYS.dbo.dt_LoadDESAppn @statements = '%%$statements$', @session_user_id = '%%$user_id$', @school_cd = '%%school_cd.student_yr', @stud_email = '%%email_addr.student_yr', @result = @result OUTPUT;","OFDA" Error I'm getting is : 42000 [Microsoft][SQL Server Native Client 11.0][SQL Server]The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. I have checked and my connection user has sufficient privileges to execute a stored procedure. Any other suggestions what the problem might be?

Not sure what the problem with this method might be, but are you aware that you can run the stored procedure using a signature?  Implementation is 'Stored procedure component'  Each operation then has a literal name of your stored procedure, and parameters which have literal names matching your procedural parameter names. (Without the @).  I had some fun with output parameters, (I was returning an entire resultset) but I got it working in the end. I do believe that for it to work I had to have 'mars=on' in the $MSS_PARAMS, maybe that's missing?  Iain


Author: Iain Sharp (i.sharp@pcisystems.co.uk)

Executing a stored procedure with an output parameter

Author: walsh.dale.s@edumail.vic.gov.au (dwalsh)

We have traditionally been an oracle shop but now have a number of SQL Server databases too.  We are running Uniface 9.7.01.02.  I have temp license to connect to SQL Server and can successfully execute select and insert statements.  But what I really want to do is execute an existing stored procedure from the SQL server database.  I originally had problems with the syntax but think I've got that correct now.  OFDA is my database connection defined in my .asn file.  OFDASYS.dbo.dt_loadDESAppn is the stored procedure which has 4 input parameters and 1 output. Either of these 2 statements gives me the same error now : SQL "DECLARE @result INT; EXEC OFDASYS.dbo.dt_LoadDESAppn @statements = '%%$statements$', @session_user_id = '%%$user_id$', @school_cd = '%%school_cd.student_yr', @stud_email = '%%email_addr.student_yr', @result = @result OUTPUT;","OFDA" SQL "DECLARE @return_value INT, @result INT; EXEC @return_value = OFDASYS.dbo.dt_LoadDESAppn @statements = '%%$statements$', @session_user_id = '%%$user_id$', @school_cd = '%%school_cd.student_yr', @stud_email = '%%email_addr.student_yr', @result = @result OUTPUT;","OFDA" Error I'm getting is : 42000 [Microsoft][SQL Server Native Client 11.0][SQL Server]The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. I have checked and my connection user has sufficient privileges to execute a stored procedure. Any other suggestions what the problem might be?

Thanks Iain.  Tried the mars:on but didn't solve my problem.  Have actually determined that it's not the output parameter that is the problem.  The SQL server stored procedure I'm executing actually connects back to my oracle database via a linked server and this is where I'm getting a transaction error.  Still investigating a solution.


Author: dwalsh (walsh.dale.s@edumail.vic.gov.au)
Terms & ConditionsAccessibility statement