Error Trapping

I have a linked server setup in a SQL Server 2000 environment that links to
an Oracle database. I then have a Trigger on a SQL Server table, that on
insert, inserts a row of data into the Oracle table via the linked server.
All of this is driven by a front-end application written in VB.NET. I want
the front end to be oblivious to trigger. In other words, if the linked
server loses connection to the Oracle database I don't want the trigger to
execute. I have a "clean-up" stored proc that will insert any rows that
weren't inserted by the trigger. Any ideas on how I can accomplish this? I
am unable to trap the error before it actually occurs. I would like to be
able to somehow check the status of the linked server before executing the
trigger. If it is offline (or errors) then the trigger should not execute
and no errors should be sent to the VB.NET front end. Thanks for any advice
.
Marcocould you not just write a sProc that uses the below and execute it from .ne
t
as follows:
Private Function RunExecRoutines()
Try
cn = New SqlClient.SqlConnection("user id=" & UserName.Text & ";password=" &
Password.Text & ";database=" & Database.Text & ";server=" & Server.Text)
cn.Open()
Catch ex As Exception
sqlCnError = ("Error: Could not establish database connection")
End Try
cmd = New SqlClient.SqlCommand
cmd.Connection = cn
cmd.CommandText = ProcToExec -- whatever sProc you wanna run
cmd.CommandTimeout = 9000
cmd.CommandType = CommandType.StoredProcedure
-- any params you want to pass or receive back...
cmd.Parameters.Add("@.pWho", UNameRoutine)
cmd.Parameters(0).Direction = ParameterDirection.Input
cmd.Parameters.Add("@.pRESULT", SqlDbType.Int)
cmd.Parameters(1).Direction = ParameterDirection.Output
Try
cmd.ExecuteNonQuery()
Catch ex As Exception
sqlCnError = ("Error: Could not execute " + ProcToExec + " stored procedure"
)
MessageBox.Show(sqlCnError, "AppName", MessageBoxButtons.OK, _
MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
End Try
cmd.Dispose()
cn.Close()
cn.Dispose()
end fuction
select srvid, srvstatus, srvname, srvproduct, providername, catalog from
master.dbo.sysservers where srvstatus & 32 <> 0
sp_helptext sp_sqlagent_get_perf_counters -- msdb
sp_helptext sp_enum_oledb_providers -- master
sp_helptext xp_enum_oledb_providers -- master|||Marc,
Thanks for the advice but I am working with a trigger that is executed in
SQL Server. The error occurs in SQL Server and is then reported to the app.
I had thought about the try...catch method but it does me no good since I am
working with a trigger that is idenpendent of .NET. Do you have any other
ideas? I would rather not reengineer my front-end app if at all possible.
Thanks,
Marco
"marcmc" wrote:

> could you not just write a sProc that uses the below and execute it from .
net
> as follows:
> Private Function RunExecRoutines()
> Try
> cn = New SqlClient.SqlConnection("user id=" & UserName.Text & ";password="
&
> Password.Text & ";database=" & Database.Text & ";server=" & Server.Text)
> cn.Open()
> Catch ex As Exception
> sqlCnError = ("Error: Could not establish database connection")
> End Try
> cmd = New SqlClient.SqlCommand
> cmd.Connection = cn
> cmd.CommandText = ProcToExec -- whatever sProc you wanna run
> cmd.CommandTimeout = 9000
> cmd.CommandType = CommandType.StoredProcedure
> -- any params you want to pass or receive back...
> cmd.Parameters.Add("@.pWho", UNameRoutine)
> cmd.Parameters(0).Direction = ParameterDirection.Input
> cmd.Parameters.Add("@.pRESULT", SqlDbType.Int)
> cmd.Parameters(1).Direction = ParameterDirection.Output
> Try
> cmd.ExecuteNonQuery()
> Catch ex As Exception
> sqlCnError = ("Error: Could not execute " + ProcToExec + " stored procedur
e")
> MessageBox.Show(sqlCnError, "AppName", MessageBoxButtons.OK, _
> MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
> End Try
> cmd.Dispose()
> cn.Close()
> cn.Dispose()
> end fuction
>
> select srvid, srvstatus, srvname, srvproduct, providername, catalog from
> master.dbo.sysservers where srvstatus & 32 <> 0
> sp_helptext sp_sqlagent_get_perf_counters -- msdb
> sp_helptext sp_enum_oledb_providers -- master
> sp_helptext xp_enum_oledb_providers -- master|||would this be any use?
if @.varName <= 0 goto trig_error
trig_error:
raiserror('Allocation Error - Unable to Allocate Zero Quantity',16,-1)|||Marc,
Thanks for the ideas. What I ended up doing was adding a status indicator
field to my table. In the front end I try to connect to the database. If I
cannot connect it sets this indicator field to a 1. If on insert that field
is set to one the Trigger then ignores the inserted row. This will also
allow me to monitor, a bit more closely, any connectivity issues I may have
with the Oracle database. thanks again for your help.
Marco
"marcmc" wrote:

> would this be any use?
> if @.varName <= 0 goto trig_error
> trig_error:
> raiserror('Allocation Error - Unable to Allocate Zero Quantity',16,-1)
>