ACC1x: Trapping SQL Server RAISERROR() Function Values

ID: Q101678


The information in this article applies to:


SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

Microsoft SQL Server users often create triggers or stored procedures that perform specific functions which are fired during certain events. Often, a custom-generated error value is desired to indicate the status of such events. This article describes a way to trap that value in Microsoft Access.

NOTE: This article assumes that the user is running Microsoft Access version 1.1, because that version corrects a problem to allow the SQL Server RAISERROR() function to return a value to Microsoft Access.


MORE INFORMATION

  1. Create the following stored procedure based on the sample database PUBS in Microsoft SQL Server:
    
          CREATE PROCEDURE TestProc
          AS
            RAISERROR 25000 'This is a test error' 


  2. Create a trigger on the table from which you want this stored procedure to run:

    NOTE: Stored procedures cannot be executed directly from Microsoft Access 1.1, unless they are triggered from a SQL Server trigger or the SQL pass-thru .DLL file (SPT110.DLL) is used. However, when using version 2.0, you can invoke a stored procedure using an SQL pass- through query. For this example, you will use a trigger that fires when a record is updated.
    
          CREATE TRIGGER TestTrig
          ON Authors
          FOR UPDATE
          AS
            EXECUTE TestProc 


  3. Add the following code to an Access Basic module. The subprocedure updates the Authors table (the attached table dbo_authors) in some way, and traps the error value that is passed by the RAISERROR() function:
    
          Sub TrapIt ()
              Dim db As Database, Mydyna As Dynaset
              Dim Xerr As String, Xval As Integer,
              Dim Xstart As Integer, Xlen As Integer
              On Error GoTo ErrorHandler
              Set db = CurrentDB()
              Set Mydyna = db.CreateDynaset("select * from dbo_authors;")
              Mydyna.Edit
              Mydyna!au_fname = Mydyna!au_fname
              Mydyna.Update
              Mydyna.MoveNext
              Exit Sub
    
          ErrorHandler:
    
          'This routine parses the error string returned from ODBC and
          'extracts only the error value you assigned with the RAISERROR()
          'function in SQL Server.
    
          'traps the error message
              xerror = Error$
          'finds start of error value
              Xstart = InStr(1, xerror, "#") + 1
          'finds length of error value
              Xlen = InStr(Xstart, xerror, ")") - Xstart
          'extracts error value from string
              Xval = Mid(xerror, Xstart, Xlen)
              MsgBox ("You have encountered error #" & CStr(Xval))
              Resume Next
          End Sub 



Keywords          : kb3rdparty IsmPdox 
Version           : 1.1
Platform          : WINDOWS 
Issue type        : kbinfo 

Last Reviewed: March 25, 1999