DOCUMENT:Q151090  17-AUG-1999  [foxpro]
TITLE   :HOWTO: Return an Error Message from Microsoft SQL Server
PRODUCT :Microsoft FoxPro
PROD/VER:
OPER/SYS:
KEYWORDS:kbinterop kbDatabase kbvfp300 kbvfp500 kbvfp600 kbvfp260

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 6.0 
 - Microsoft FoxPro for Windows, versions 2.6, 2.6a 
-------------------------------------------------------------------------------

SUMMARY
=======

This article discusses how to create a stored procedure on Microsoft SQL Server
to return a specific error message to the user. The Raiserror Command allows you
to specify an error number and message. Note that numbers higher that 50,000 are
not used by SQL Server. With Microsoft SQL Server 6.0, you can also write this
procedure to the NT event log using the "with log" option.

MORE INFORMATION
================

Use the following code to implement the procedures above:

   *****************************************
   *SQL Stored Procedure Code on SQL Server*
   *****************************************

      create procedure MyAuthors
      @theLastname varchar(25) as
      if not exists(select * from authors
      where au_lname = @theLastname)

      begin
         raiserror 50001 "This author doesn't exist in the pubs database"
         return
      end

      select * from authors
      where au_lname = @theLastname

   ***************************
   *   Visual FoxPro Code    *
   ***************************

      CLEAR
      CLEAR ALL
      RELEASE ALL

      PUBLIC xHandle,ySuccess,zErr
      xHandle = SQLCONNECT("MySqlServer6","sa","")
      IF xHandle > 0
          ? "Good Connection"
          ? "Handle "+ str(xHandle)
       Else
          =MESSAGEBOX("Bad Connection",16,"Error Connecting")
          RETURN
      ENDIF

      ySuccess = SQLEXEC(xHandle,"use pubs")
      zErr=CheckErr()
      IF zErr = .F.
        RETURN
      ENDIF

      ySuccess = SQLEXEC(xHandle,"exec MyAuthors 'xxx'")
      * xxx above is surrounded by single quotes.
      zErr=CheckErr()
      IF zErr = .F.
         RETURN
      ENDIF

      RETURN

      PROCEDURE CheckErr
      IF ySuccess < 0
        * An error has occurred
        =AERROR(myError)

        CLEAR

         DO CASE
            CASE myError[1,5] = 50001
                *Predefined in SQL Stored Procedure MyAuthors
            xStrLoc =rat(']',myerror[1,3]) + 1
                * the left square bracket above is surrounded by single quotes
            =MESSAGEBOX(substr(myError[1,3],xStrLoc,55),16,"Author not found")
            =SQLDISCONNECT(xHandle)

         OTHERWISE
          *Handle other errors
         FOR n = 1 TO 7  && Display all elements of the array
            ? myError(n)
         ENDFOR
        =SQLDISCONNECT(xHandle)
        ENDCASE

        RETURN .F.

      ENDIF
      RETURN

REFERENCES
==========

"Microsoft NT 3.51 Administrator Guide" and "Microsoft SQL Server Language
Reference."

For more information, please see the following articles in the Microsoft
Knowledge Base:

   Q141140 HOWTO: Create a SQL Server Stored Procedure

   Q114787 HOWTO: Execute a Stored Procedure on SQL Server

Additional query words: SQLSERVER

======================================================================
Keywords          : kbinterop kbDatabase kbvfp300 kbvfp500 kbvfp600 kbvfp260 
Technology        : kbVFPsearch kbAudDeveloper kbFoxproSearch kbFoxPro260 kbFoxPro260a kbVFP300 kbVFP300b kbVFP500 kbVFP600
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1999.