CODEWITHSUNDEEP
c#
Asaf Gilad
Asaf Gilad

Reputation:

How to get in C#/vb.net -the messages returned from raiserror command(sqlserver) during execution of a stored procedure

it's a bit long since I've done some research and loged my "findings" but the question is only 2 lines and bolded...

I have searched anywhere and I'm stacked between C# and SQL_SERVER:

I need to run a very big stored procedure which takes over 30 minutes

it got steps and I want to notify the dot.net on which step the SP is working now... some kind of status report.

from the SQL_SERVER side I think I got it.. RAISERROR('HELLO WORLD.', 10, 1) with nowait

when the error level is less then 11 it is a warning when the error level is between 11-19 it is an exception that can be handled when the error level is greater then 19 it is a "fatal" error closing the connection warning

So I use 10 - I want to send a warning - that will not fail the run.

On the .net side I'm lost... I read about threading, about RPC, about events and even... about a flag for framework above 1.0

I need a complete code with complete settings that get the following job done: Call a stored procedure and w h i l e executing it report status (while - not after!!!)

I think that the best related information I got so far is: geekswithblogs.net/mrnat/archive/2004/09/20/11431.aspx

www.eggheadcafe.com/forumarchives/NETFrameworkADONET/Nov2005/post24657346.asp

www.dotnet247.com/247reference/msgs/54/273728.aspx

msdn.microsoft.com/en-us/library/ms178592.aspx

www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic37218.aspx

If you can give a working example or refer me to a project in a site like(stackoverflow/codeproject etc.) of a SP which returns "Hello World" - during executing of a SP I'll be so grateful - right now I'm clicking the button and pray... and I really prefer the illusion of being in control :)

Thanks And sorry for the story

Upvotes: 3

Views: 1160

Answers (2)

TheVillageIdiot
TheVillageIdiot

Reputation: 40512

All you need is to catch SqlException then you can have

  1. LineNumber (on which error occured in your SP)

  2. Number (Error number [10 in your case])

  3. Procedure (Name of Stored Procedure that caused the error)

  4. State

  5. SqlErrorCollection object

and many other goodies!!

EDIT:- OOPS this will not help you directly you have different requirements.

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300827

Create a table that stores the current step number of your process, and have your process write this table at each step. Then you can either poll the 'current step' or set a SQLCacheDependency.

Upvotes: 1

Related Questions