Displaying Error Messages From Triggers

[s]

In my post below I described a problem I have been experiencing since the
client upgraded to Access 2003. I have since found the following article,
which refers to Access 2002 and several posts across the internet where
folks have been finding inconsistent issues that are not tied to a
particular version of Access. Sometimes, the problem occurs in 2002,
sometimes in 2003.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;275057

I wonder if anyone has got a more definitive diagnosis to the problem. ie
are there dll problems or MDAC issues etc.

Any feedback would be truly appreciated.

If I can't get the trigger to raise errors, what is the best work around? I
would like to leave the business logic on the server side if at all
possible, so perhaps a stored procedure would have to be used.

Thanks, Simon

 

[giorgio rancati]

Hi,
change the severity level.
----
CREATE TRIGGER Table1_Trigger1
ON dbo.Table1
FOR Update
As
RAISERROR ('You cannot edit this field', 16, 1)
ROLLBACK TRANSACTION

 

[Simon]

Thanks, Giorgio but I'm at 16 already - the trigger errors got picked up by
Access 2002 but not Access 2003. Nothing on the server has changed.

Hi,
change the severity level.
----
CREATE TRIGGER Table1_Trigger1
ON dbo.Table1
FOR Update
As
RAISERROR ('You cannot edit this field', 16, 1)
ROLLBACK TRANSACTION
----
it works

bye
--
Giorgio Rancati
[Office Access MVP]

In my post below I described a problem I have been experiencing since the
client upgraded to Access 2003. I have since found the following
article,
which refers to Access 2002 and several posts across the internet where
folks have been finding inconsistent issues that are not tied to a
particular version of Access. Sometimes, the problem occurs in 2002,
sometimes in 2003.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;275057

I wonder if anyone has got a more definitive diagnosis to the problem.
ie
are there dll problems or MDAC issues etc.

Any feedback would be truly appreciated.

If I can't get the trigger to raise errors, what is the best work around? I
would like to leave the business logic on the server side if at all
possible, so perhaps a stored procedure would have to be used.

Thanks, Simon

 

[giorgio rancati]

Hi Simon,

it's very weird, what is your Access and Sql Server versions?

these are my versions
----
Client
Access 2003 (11.6566.6568) SP2
on Windows XP pro SP2

Server
SQL Server 2000 - 8.00.2040 (Intel X86) Personal Edition
on Windows 2000 pro SP4
----

bye
--
Giorgio Rancati
[Office Access MVP]

Thanks, Giorgio but I'm at 16 already - the trigger errors got picked up by
Access 2002 but not Access 2003. Nothing on the server has changed.

Hi,
change the severity level.
----
CREATE TRIGGER Table1_Trigger1
ON dbo.Table1
FOR Update
As
RAISERROR ('You cannot edit this field', 16, 1)
ROLLBACK TRANSACTION
----
it works

bye
--
Giorgio Rancati
[Office Access MVP]

In my post below I described a problem I have been experiencing since the
client upgraded to Access 2003. I have since found the following
article,
which refers to Access 2002 and several posts across the internet where
folks have been finding inconsistent issues that are not tied to a
particular version of Access. Sometimes, the problem occurs in 2002,
sometimes in 2003.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;275057

I wonder if anyone has got a more definitive diagnosis to the problem.
ie
are there dll problems or MDAC issues etc.

Any feedback would be truly appreciated.

If I can't get the trigger to raise errors, what is the best work

around?
I

would like to leave the business logic on the server side if at all
possible, so perhaps a stored procedure would have to be used.

Thanks, Simon




I have an Accesss 2002 adp file that connects to SQL Server 2000. We
are
now using Access 2003 and I have found that Access does not appear to
be
catching errors raised on the server. I used to execute "RunCommand
acCmdSaveRecord" and if there were any errors on the server I would
capture
Err number 5000. Under Access 2003 the server side errors do not get
passed
to the client.

Any ideas why this is no longer working? Tx.

 

[Sylvain Lafontaine]

You're not the first one making a mention of this problem about A2003. See
for exemple:

http://groups.google.com/group/micr..._frm/thread/c5850ce5af238cd9/b0b3a1c29994efd9

http://www.eggheadcafe.com/ng/AccessadpSQLServer/Jul2005/post23221592.asp

If you ever find the solution, don't forget to get back here and tell us
what's happening.

 

[giorgio rancati]

I think that it depends from the Sql Server version and service pack.

Access 2003 (11.6566.6568) SP2
+ Sql Server 2000 (8.00.2040) Sp4
----
RAISERROR ('........', 0, 1) doesn't work
RAISERROR ('........', 16, 1) works!
----

Access 2003 (11.6566.6568) SP2
+ Sql Server 2005 Express (9.00.1399.06)

 

[Simon]

Thanks, I'll check the version numbers of our Access 2003 and SQL Server
2000 instance but it will be a while before we migrate to 2005.

However, it may not matter because I have no luck using severity of 16,
which as you indicate you have success with under both conditions.

 

[Simon]

Yes, I came across your posts in those other forums. If I do find a
solution, or even a cause, I will report back here.

 

[giorgio rancati]

Thanks, I'll check the version numbers of our Access 2003 and SQL Server
2000 instance but it will be a while before we migrate to 2005.

[CUT]

Sql Server 2005 was only an example to show the different result with the
same Access2003.
I didn't want you change the Sql Server version

 

[Vadim Rapp]

Hello,
You wrote in conference microsoft.public.access.adp.sqlserver on Thu, 16
Mar 2006 11:42:18 -0800:

s>> Any ideas why this is no longer working? Tx.

I don't quite understand your question. You have found the right ms article
that says that it indeed does not work, and does not provide any workaround.
Which means you can't rely on the trigger-raised error.

I would then write something like this: create a separate table with a
single cell. Before launching the operation in access, set the cell value to
0. The trigger should update it to 1; but if it then raises the error, that
would be rolled back to 0. After that, you check the value in Access, and if
you find there 0, you will know that the error was raised.

Vadim Rapp

 

[Simon]

Vadim,

Despite the MS article, which pertained to Access 2002, it was working with
2002. Now, it is not working with 2003 although it is working for others.

Thanks for your suggested workaround but I have several errors that I'm
checking for in my trigger and I need to know which one is getting raised.
Certainly, I can use a more elaborate method along the lines of your
suggestion or more likely I can test for errors with a stored procedure but
I was hoping to find the cause of this apparent inconsistency before
committing to the extra work.

Simon

 

[Vadim Rapp]

S> I was hoping to find the cause of this apparent inconsistency before
S> committing to the extra work.

The cause, obviously, is a bug in Access 2003. I'm surprised though they did
not bother to issue a patch; I don't recall another case when a bug was
described but not fixed. This is probably yet another indicator of how
unimportant Access ADP is for Microsoft. Certainly, if it was a security
vulnerability with 1/10**9 probability of being hit, the patch would be
written overnight and pushed by windowsupdate to the whole world.

I would open support incident with microsoft just to ask them where is the
patch to the acknoledged bug, and is it their new policy to acknowledge
program bugs without fixing them, and if so, what products are involved.

 

[giorgio rancati]

I think that it depends from the Sql Server version and service pack.

I wanted to do other test

This is the result test with Access 2000

Access 2000 (9.0.6926 SP-3)
+ Sql Server 2000 (8.00.2040) Sp4
----
RAISERROR ('........', 0, 1) doesn't work
RAISERROR ('........', 16, 1) works!
----

This is the test with Visual Studio 2005 (8.0.50727.42 RTM)
+ Sql Server 2000 (8.00.2040) Sp4
----
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim cn As New System.Data.SqlClient.SqlConnection
Dim cmd As New System.Data.SqlClient.SqlCommand
cn.ConnectionString = _
"Data Source=Giorgio" + _
";Initial Catalog=Pubs" + _
";Integrated Security=SSPI"
cn.Open()

cmd.CommandText = _
"UPDATE Tabe1 " + _
"SET RecordInfo='Your Info' " + _
"WHERE RecordId=1"

cmd.Connection = cn

Try
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
End Try

cmd = Nothing
cn.Close()
cn = Nothing

End Sub

 

[s]

Giorgio,

Maybe I'm missing something but it seems that all of your tests show that
using a severity level of 16 raises the error but using 0 doesn't. How does
that prove that the issue is with SQL Server?

Simon

 

[giorgio rancati]

This Kb
-------
http://support.microsoft.com/default.aspx?scid=kb;EN-US;275057
.....
RAISERROR('You cannot edit this field', 0, -1)
Rollback Transaction
-------

tell
-------
A trigger that is assigned to a table does not display an error message when
the trigger is executed.
.....
APPLIES TO
Microsoft Access 2002 Standard Edition
-------
well, the same problem is in all my access version, in Visual Studio 2005
and in VBS script.
Is this an Access problem ?
I may be wrong but IMHO it isn't an access problem.

You don't receive the error message using a severity level of 16, well, have
you tried another way ?
For example with a VBS script
----
Dim cn
Dim cmd
Set cn=CreateObject("ADODB.Connection")
Set cmd=CreateObject("ADODB.Command")

cn.ConnectionString = _
"Provider=SQLOLEDB.1" & _
";Data Source=." & _
";Initial Catalog=Pubs" & _
";Integrated Security=SSPI"

cn.Open
Set cmd.ActiveConnection = cn
cmd.CommandType = 1

cmd.CommandText = "UPDATE Tabe1 " & _
"SET RecordInfo='Your Info' " & _
"WHERE RecordID=1"

cmd.Execute
Set cmd=Nothing
cn.Close
Set cn=Nothing

 

[Malcolm Cook]

Simon,

I find RAISERROR messages from SQL Server are consistently reported in client ADPs in both Access 2002 and Access 2003 but ONLY if
you pass a value greater than 10. The MSDN article is misleading since it shows an example of using 0 as the servirty level.

Furthermore, in 2002 (at least) I can interrogate the error(s) in a form_error proc if I'm sure to check
Me.Form.Recordset.ActiveConnection.Errors.

Maybe I can help your case if you would show us:

* the CREATE TRIGGER statement
* an example of a a row in the database before trying to edit it
* a proposed edit to the row that should cause the triggers RAISERROR to run
* the method you use to "capture Err number 5000" (as you say)
* any code in the form module (especially, any Form_Error proc)

 

[aaron.kempf]

VADIM

**** YOU I HAVE A HUNDRED BUGS THAT ARE DESCRIBED BUT NOT FIXED

TAKE YOUR BMW AND YOUR PRIDE AND SHOVE IT

START FIXING BUGS REDMOND!!!!