Problem with SQL Server RAISERROR and FoxPro PassThruSQL

[Creeder]

Hi ...<br><br>We are running into a problem with the behavior of VFP handling of<br>RAISERROR. We are using PastThruSQL to call all of our data modification<br>routines, for example, one of the SQL Stored Procedure uses the following<br>codes..<br><br>...<br>...<br>BEGIN TRANS<br>-- Delete associated table records<br>DELETE FROM subtableA WHERE field1 = 13234<br>-- Delete main table record<br>DELETE FROM tableA WHERE field1 = @field1<br>IF (@@error != 0)<br>BEGIN<br>ROLLBACK TRANS<br>RETURN (1)<br>END<br>COMMIT TRANS<br><br>Let say the DELETE FROM ... statement caused a Constraint Error and resulted<br>in a RAISERROR. Using SQL QA, the execution will proceed to IF<br>(@@error!=0)... and issues a ROLLBACK TRANS. However, when calling this<br>procedure from VisualFoxPro, the RAISERROR is detected but the exeution of<br>the stored procedure stopped. This resulted in the deletion of subtableA<br>not being rolled back.<br><br>Any insight and advise is greatly appreciated.<br><br>Thanks.<br><br><br>

 

[foxdev]

Do you have a delete trigger that is explicitly calling RaiseError?&nbsp;&nbsp;If it is a simple constraint violation (not a trigger calling RaiseError) all that should happen is that you'll get a SQLEXEC() return of -1, and checking MESSAGE() will give you something like &quot;constraint violation.&quot; <p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br>

 

[Guest_imported]

Thanks for the reply.&nbsp;&nbsp;I am working on the same problem with Creeder.<br><br>The problem we had was the behaviour inconsistency between running a stored procedure in SQL Query Analyzer and calling the stored procedure from VFP when it comes to RAISERROR.&nbsp;&nbsp;<br><br>In QA, the execution of the stored procedure will continue even after a RAISERROR has occur which allows for transaction ROLLBACK, if any previous change were made prior to the Constraint voilation.&nbsp;&nbsp;We are detecting the RAISERROR properly in VFP, the problem is the stored procedure code after the RAISERRROR is not executed when called from VFP.<br><br>Lin-Chow Sim<br>

 

[Guest_imported]

Two settings you may want to change are the connection's Asynchronous and Transactions properties.<br><br>To manually control SQL transactions, change the default 1 (automatic) to 2 (manual).<br><br>Try also changing the Asynchronous property from the default .F. to .T.&nbsp;&nbsp;Program execution continues after the SQLEXEC() call.&nbsp;&nbsp;At some point later in the program, you'll have to issue SQLMORERESULTS() to determine if processing is still running, terminated normally, or terminated in an error condition.<br><br>I hope this helps.<br>

 

[foxdev]

K_hoona, you are correct about async, but I think they're saying that apparently the <b>server</b> code is not continuing, not the VFP code (in async mode, VFP code continues to run even before the server finishes executing the SQLEXEC).<br><br>How are you calling the SP?&nbsp;&nbsp;Are you using the ODBC <font color=blue><FONT FACE=monospace>{CALL My_SP}</font></font> method? <p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br>

 

[Guest_imported]

Unfortunately, the connection Asynchronous setting has made no difference in terms of the handling behavior of RAISERROR in the SQL7 stored procedure.&nbsp;&nbsp;We wanted the stored procedure on the SQL Server to continue execution after a RAISERROR (which using SQL7 Query Analyzer works perfectly fine) but VFP seems to close the connection right after the RAISERROR is received from the stored procedure.<br><br>All transactions are controlled within the stored procedure and is not handle by the VFP application, at this point of the development and point of design, it is not an option to move the transaction handling logic from the Server to the client program.<br><br>LC

 

[Guest_imported]

Robert,<br><br>Thanks for the reply again.&nbsp;&nbsp;Yes, we have tried using both the EXEC MySP and {CALL MySP} as the lcSQLString and neither is working properly with RAISERROR.<br><br>LC

 

[Guest_imported]

There has been a new development in the RAISERROR problem we were having.&nbsp;&nbsp;Contrary to what we thought was happening before, SQL code after a RAISERROR was indeed being executed as expected.&nbsp;&nbsp;However, the problem is no SELECT results is being returned to VFP, although the SQL Profiler shows the code being executed.<br><br>CREATE PROCEDURE TEST&nbsp;&nbsp;AS<br><br>RAISERROR (....)&nbsp;&nbsp;<br>SELECT author_id FROM authors<br><br>When this is being called from VFP, no data is being returned.

 

[foxdev]

But you wouldn't want any data returned if there is an error.&nbsp;&nbsp;It seems the behavior is the (normally) desirable one.<br><br>If you have an &quot;abnormal&quot; requirement, consider using a return value rather than RaiseError; you could encode some special flag or whatever in the return value. <p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br>