|
Home > Archive > MS Access project with SQL Server > March 2006 > Displaying Error Messages From Triggers
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Displaying Error Messages From Triggers
|
|
|
|
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/defaul...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.
>
>
| |
| giorgio rancati 2006-03-16, 8:25 pm |
| 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]
"s" <s@b.com> ha scritto nel messaggio
news:erYl9GTSGHA.5036@TK2MSFTNGP12.phx.gbl...
>
> 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/defaul...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
>
>
>
>
>
>
| |
|
| 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.
"giorgio rancati" < giorgio_No_Spalmer_r
ancati@tiscali.it> wrote in message
news:OVQp1wUSGHA.4956@TK2MSFTNGP09.phx.gbl...
> 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]
>
> "s" <s@b.com> ha scritto nel messaggio
> news:erYl9GTSGHA.5036@TK2MSFTNGP12.phx.gbl...
> I
>
>
| |
| giorgio rancati 2006-03-17, 3:27 am |
| 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]
"Simon" <simon@schemax.com> ha scritto nel messaggio
news:O8Ws1jXSGHA.1572@tk2msftngp13.phx.gbl...
> 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.
>
>
> "giorgio rancati" < giorgio_No_Spalmer_r
ancati@tiscali.it> wrote in message
> news:OVQp1wUSGHA.4956@TK2MSFTNGP09.phx.gbl...
the[color=darkred]
around?[color=darkred]
>
>
| |
| Sylvain Lafontaine 2006-03-17, 3:27 am |
| You're not the first one making a mention of this problem about A2003. See
for exemple:
http://groups.google.com/group/micr...0b3a1c29994efd9
http://www.eggheadcafe.com/ng/Acces...ost23221592.asp
If you ever find the solution, don't forget to get back here and tell us
what's happening.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"s" <s@b.com> wrote in message news:erYl9GTSGHA.5036@TK2MSFTNGP12.phx.gbl...
>
> 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/defaul...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 2006-03-17, 3:27 am |
| 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)
----
RAISERROR ('........', 0, 1) works!
RAISERROR ('........', 16, 1) works!
----
bye
--
Giorgio Rancati
[Office Access MVP]
| |
|
| 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.
"giorgio rancati" < giorgio_No_Spalmer_r
ancati@tiscali.it> wrote in message
news:OBobxvZSGHA.5036@TK2MSFTNGP12.phx.gbl...
>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)
> ----
> RAISERROR ('........', 0, 1) works!
> RAISERROR ('........', 16, 1) works!
> ----
>
> bye
> --
> Giorgio Rancati
> [Office Access MVP]
>
>
| |
|
| 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.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23H3HejZSGHA.776@TK2MSFTNGP09.phx.gbl...
> You're not the first one making a mention of this problem about A2003.
> See for exemple:
>
> http://groups.google.com/group/micr...0b3a1c29994efd9
>
> http://www.eggheadcafe.com/ng/Acces...ost23221592.asp
>
> If you ever find the solution, don't forget to get back here and tell us
> what's happening.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
>
> "s" <s@b.com> wrote in message
> news:erYl9GTSGHA.5036@TK2MSFTNGP12.phx.gbl...
>
>
| |
| giorgio rancati 2006-03-17, 7:25 am |
| "Simon" <simon@schemax.com> ha scritto nel messaggio
news:eOEH9MaSGHA.776@TK2MSFTNGP09.phx.gbl...
> 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 :-)
:-)
--
Giorgio Rancati
[Office Access MVP]
| |
| Vadim Rapp 2006-03-19, 11:12 am |
| 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 2006-03-19, 11:12 am |
| 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" <vr@myrealbox.nospam.com> wrote in message
news:u0fG0RxSGHA.1204@TK2MSFTNGP12.phx.gbl...
> 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
| |
| Vadim Rapp 2006-03-21, 3:41 am |
| 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 2006-03-21, 7:29 am |
| "giorgio rancati" < giorgio_No_Spalmer_r
ancati@tiscali.it> ha scritto nel
messaggio news:OBobxvZSGHA.5036@TK2MSFTNGP12.phx.gbl...
> 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
----
and this is the result
----
RAISERROR ('........', 0, 1) error message doesn't Catch
RAISERROR ('........', 16, 1) error message Catch!
----
I think it isn't an Access problem!
bye
--
Giorgio Rancati
[Office Access MVP]
| |
|
| 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" < giorgio_No_Spalmer_r
ancati@tiscali.it> wrote in message
news:%23%237qalNTGHA
.1160@TK2MSFTNGP09.phx.gbl...
> "giorgio rancati" < giorgio_No_Spalmer_r
ancati@tiscali.it> ha scritto nel
> messaggio news:OBobxvZSGHA.5036@TK2MSFTNGP12.phx.gbl...
>
> 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
> ----
>
> and this is the result
> ----
> RAISERROR ('........', 0, 1) error message doesn't Catch
> RAISERROR ('........', 16, 1) error message Catch!
> ----
>
> I think it isn't an Access problem!
>
> bye
> --
> Giorgio Rancati
> [Office Access MVP]
>
>
| |
| giorgio rancati 2006-03-21, 8:34 pm |
| This Kb
-------
http://support.microsoft.com/defaul...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
----
bye
--
Giorgio Rancati
[Office Access MVP]
"s" <s@b.com> ha scritto nel messaggio
news:u2hRyeQTGHA.5552@TK2MSFTNGP14.phx.gbl...
> 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?
| |
| Malcolm Cook 2006-03-21, 8:34 pm |
| 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)
--
Malcolm Cook
Stowers Institute for Medical Research - Kansas City, MO USA
"s" <s@b.com> wrote in message news:erYl9GTSGHA.5036@TK2MSFTNGP12.phx.gbl...
>
> 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/defaul...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
>
>
>
>
>
>
|
|
|
|
|