Geek asks and answers

How to detect that a trigger failed after calling a stored procedure from a .Net application?

We have a .NET application (VB / VS2010) and many stored procedures are called on SQL Server 2008 to query the database. We also have quite a few update / insert / delete triggers that are executed automatically as soon as these stored procedures modify the database tables.

Often there are situations when a stored procedure is called, and it seems to work fine, since the error does not occur, and the .NET application continues to work as usual. However, if I then look under the covers and make a call to the stored procedure manually through the SQL Server client, I see that the trigger, which is executed immediately after the stored procedure failed, discards all changes.

So my question is: what is the best way to detect and go through errors in our .NET → stored procedure → trigger script to know exactly in a .NET application that everything worked or not in case of an error?

Thanks a lot in advance, Steve

Update: now I am at home and away from my desk (and code base) for the weekend, so you will not have the opportunity to check the very details of stored procedures. Thank you very much for the answers that have been given so far. I can take a look at the code again next week.

But in the meantime ...

Question about MS SQL Server version: this is 2008.

From what I know from my head, we call stored procedures (at least those that do not read data and “just” update, delete, or insert data) as follows:

Using connection As New SqlConnection("connectionString")
    Dim command As New SqlCommand("EXEC STORED_PROCEDUR_ENAME), connection)
    command.Connection.Open()
    command.ExecuteNonQuery()
End Using

, , , - , - ,

command.ExecuteNonQuery()

. , ? , ExecuteDataReader, , , ...

SQL, .

+5
3

,

, @msg sproc.

SET @msg = 'Test print 1'

, , - , sproc . , , , .

+1

as you mention that you are using SQL Server 2008. This makes it possible to use try .. catch. here is the article.

http://msdn.microsoft.com/en-us/library/ms175976.aspx

Also check out this forum.

http://social.msdn.microsoft.com/Forums/eu/transactsql/thread/03eae70e-d478-44a7-90f3-8e1d27d9f22e

I think try..catch will do the job. also on the .net side i would use the following code.

try
{
   // your stored procedure execution code.
}
Catch (sqlexception e)
{
  // do something with sql exception.
}

try this scenario, see if your problem solves.

0
source

More articles:


All Articles