×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Problem with SQL Server RAISERROR and FoxPro PassThruSQL

Problem with SQL Server RAISERROR and FoxPro PassThruSQL

Problem with SQL Server RAISERROR and FoxPro PassThruSQL

(OP)
Hi ...

We are running into a problem with the behavior of VFP handling of
RAISERROR. We are using PastThruSQL to call all of our data modification
routines, for example, one of the SQL Stored Procedure uses the following
codes..

...
...
BEGIN TRANS
-- Delete associated table records
DELETE FROM subtableA WHERE field1 = 13234
-- Delete main table record
DELETE FROM tableA WHERE field1 = @field1
IF (@@error != 0)
BEGIN
ROLLBACK TRANS
RETURN (1)
END
COMMIT TRANS

Let say the DELETE FROM ... statement caused a Constraint Error and resulted
in a RAISERROR. Using SQL QA, the execution will proceed to IF
(@@error!=0)... and issues a ROLLBACK TRANS. However, when calling this
procedure from VisualFoxPro, the RAISERROR is detected but the exeution of
the stored procedure stopped. This resulted in the deletion of subtableA
not being rolled back.

Any insight and advise is greatly appreciated.

Thanks.


RE: Problem with SQL Server RAISERROR and FoxPro PassThruSQL

Do you have a delete trigger that is explicitly calling RaiseError?  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 "constraint violation."

Robert Bradley

FoxDev - Visual FoxPro Development

RE: Problem with SQL Server RAISERROR and FoxPro PassThruSQL

Thanks for the reply.  I am working on the same problem with Creeder.

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.  

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.  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.

Lin-Chow Sim

RE: Problem with SQL Server RAISERROR and FoxPro PassThruSQL

Two settings you may want to change are the connection's Asynchronous and Transactions properties.

To manually control SQL transactions, change the default 1 (automatic) to 2 (manual).

Try also changing the Asynchronous property from the default .F. to .T.  Program execution continues after the SQLEXEC() call.  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.

I hope this helps.

RE: Problem with SQL Server RAISERROR and FoxPro PassThruSQL

K_hoona, you are correct about async, but I think they're saying that apparently the server code is not continuing, not the VFP code (in async mode, VFP code continues to run even before the server finishes executing the SQLEXEC).

How are you calling the SP?  Are you using the ODBC {CALL My_SP} method?

Robert Bradley

FoxDev - Visual FoxPro Development

RE: Problem with SQL Server RAISERROR and FoxPro PassThruSQL

Unfortunately, the connection Asynchronous setting has made no difference in terms of the handling behavior of RAISERROR in the SQL7 stored procedure.  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.

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.

LC

RE: Problem with SQL Server RAISERROR and FoxPro PassThruSQL

Robert,

Thanks for the reply again.  Yes, we have tried using both the EXEC MySP and {CALL MySP} as the lcSQLString and neither is working properly with RAISERROR.

LC

RE: Problem with SQL Server RAISERROR and FoxPro PassThruSQL

There has been a new development in the RAISERROR problem we were having.  Contrary to what we thought was happening before, SQL code after a RAISERROR was indeed being executed as expected.  However, the problem is no SELECT results is being returned to VFP, although the SQL Profiler shows the code being executed.

CREATE PROCEDURE TEST  AS

RAISERROR (....)  
SELECT author_id FROM authors

When this is being called from VFP, no data is being returned.

RE: Problem with SQL Server RAISERROR and FoxPro PassThruSQL

But you wouldn't want any data returned if there is an error.  It seems the behavior is the (normally) desirable one.

If you have an "abnormal" requirement, consider using a return value rather than RaiseError; you could encode some special flag or whatever in the return value.

Robert Bradley

FoxDev - Visual FoxPro Development

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close