Cannot get SQL error message

Question

I am tring to run a sql script from my c# application

string sql = File.ReadAllText(sqlFile);
string[] QueryArray= sql.Split(new string[] { "GO" }, StringSplitOptions.RemoveEmptyEntries);

foreach (string query in QueryArray)
{
    cmd = new SqlCommand(query, Connection);
    cmd.ExecuteNonQuery();
}

Then my script contains raiserror statement

RAISERROR ('Cannot procees' , 20, 1) WITH LOG

When I try to access this error message form my C# application, that won't happen. The error message return to me is:

A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

Where I have gone wrong. What Do I need to do to get the error returned by the raiserror

Your `RAISERROR` defines a severity of 20, which makes SQL Server terminate the current process...... use something between 11 and 19 instead! Then your process will stay in place, and you should be able to fetch the contents of your error from SQL Server — marc_s, Aug 27 '13 at 05:13
I'm guessing that the OP has not even properly opened a connection to the database. — dcaswell, Aug 27 '13 at 05:17
@marc_s- But magically I was able to get the error randomly. But very rarely. Is that possible to happen. Without changing the severity level, Is there any way to handle this from my c# application side? If I change the severity level, it works — New Developer, Aug 27 '13 at 05:18

score 1 · Answer 1 · answered Aug 27 '13 at 05:20

1

Try to change severity value -

RAISERROR ('Cannot procees', 16, 1) WITH LOG

answered Aug 27 '13 at 05:20

Devart

119,203
23
166
186

Yes. that is works. But I am searching a way whether I can handle this from my C# application side without changing the sensitivity level – New Developer Aug 27 '13 at 05:22
Just try to use `try\catch` block. And read this topic: http://stackoverflow.com/questions/1122925/what-do-the-different-raiserror-severity-levels-mean – Devart Aug 27 '13 at 05:26
The code is already inside `try\catch`. I am getting that error message from `e.message` – New Developer Aug 27 '13 at 05:37
`MSDN`: Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message. My two cents: for user errors use severity level 16. – Devart Aug 27 '13 at 05:45
The reason I am using level as 20 is I need to stop running the script at that point without proceeding. If I use 16 it will `raiserror` and continue the script. – New Developer Aug 29 '13 at 04:16

score 0 · Answer 2 · answered Aug 27 '13 at 05:15

0

Microsoft have an article on this, How to Retrieve Values in SQL Server Stored Procedures. This should give you an idea on how to resolve your problem.

answered Aug 27 '13 at 05:15

Ajay

6,418
18
79
130

Cannot get SQL error message

2 Answers2