次の方法で共有

Catch a Sql Server RAISERROR at client side using SqlDataReader.

  • [アーティクル]

The other day I had a question on using RAISERROR in SQL Server and that it was not propagated down to the client.

In other words, something went wrong in, for example a stored procedure, a RAISERROR was created but this was not seen on the client.

In this case the call was made by using a SqlDataReader.

There wasn’t that much information to find about this, so I’d thought I share this here.

As usual, I’ll do this by example; in this case it will be very fictional example. A stored procedure is called in order to return info about a user.

If no user is found for the provided ID, then an RAISEROR is created.

So create a User table, insert a few rows and create the procedure that returns user info if ID is found and a raiserror if not:

-- create user table

create table Users(id int primary key, fname nvarchar(100), lname nvarchar(100))

go

-- insert a few users

insert into Users values (1, 'Carl', 'Carlson')

insert into Users values (2, 'John', 'Johnson')

insert into Users values (3, 'Peter', 'Peterson')

go

-- create stored proc to get userinfo based on ID.

create proc GetUserById(@id int)

as

set nocount on

begin

               select id, fname, lname from Users where id = @id

               if @@rowcount = 0

                             raiserror ('No users found for provided id ', 16, 10)

end

go

-- drop table Users

-- drop procedure GetUserById

And then we can test it from SSMS as well. First with an existing ID.

declare @id int

set @id = 1

exec GetUserById @id

This returns:

id fname lname

1 Carl Carlson

Then with a nonexistent ID.

declare @id int

set @id = 10

exec GetUserById @id

This returns:

Msg 50000, Level 16, State 10, Procedure GetUserById, Line 8

No users found for provided id

So we can see that our raiserror with our own message is returned. So let’s call this from a C# console application using a SqlDataReader, like so:

            string cs = @"Data Source=<your server>;initial catalog=<your database>;integrated security=SSPI";

            try

            {

                using (SqlConnection con = new SqlConnection(cs))

                {

                    con.Open();

                    SqlCommand cmd = new SqlCommand("GetUserById", con);

                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@id", 10);

                    SqlDataReader rdr = cmd.ExecuteReader();

                    while (rdr.Read())

                    {

                        Console.WriteLine("First name: {0}, Last name: {1}", rdr["fname"], rdr["lname"]);

  }

                    con.Close();

                    Console.WriteLine("We are done...");

                }

            }

            catch (SqlException se)

            {

                Console.WriteLine(se);

            }

        }

Here we are providing a nonexistent ID (10) so one would expect a SqlException, but the command window just shows: “We are done...”

If we rerun the application and we monitor the SQL Server with SQL Provider, having enabled the “Errors and Warnings / User Error Message” event we can see that the error is raised.

So how do we get this to work? Well, when using a SqlDataReader it will only iterate over the first result set, which in this case will be the select in the stored procedure.

This result set will be empty since there is no data for ID number 10. So nothing is displayed and the second result set is not accessed.

What we have to do is to move into the next result set in order to get hold of the error.

One way to do this is to as follows, ie, this will keep going over all the returned result sets until the SqlDataReader is closed, which it will be when there are no more result sets returned.

            string cs = @"Data Source=<your server>;initial catalog=<your database>;integrated security=SSPI";

            try

            {

                using (SqlConnection con = new SqlConnection(cs))

                {

                    con.Open();

                    SqlCommand cmd = new SqlCommand("GetUserById", con);

                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@id", 10);

                    SqlDataReader rdr = cmd.ExecuteReader();

                    while (!rdr.IsClosed)

                    {

                        while (rdr.Read())

                        {

                            Console.WriteLine("First name: {0}, Last name: {1}", rdr["fname"], rdr["lname"]);

                        }

                        if (!rdr.NextResult())

                        {

                            rdr.Close();

                        }

                    }

                    con.Close();

                    Console.WriteLine("We are done...");

   }

            }

            catch (SqlException se)

            {

                Console.WriteLine(se);

            }

Run this and you should get your exception:

System.Data.SqlClient.SqlException: No users found for provided id

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlDataReader.HasMoreRows()

   at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)

   at System.Data.SqlClient.SqlDataReader.NextResult()

Hope this helps J

"SQL Server 2008 Books Online (March 2009) - RAISERROR (Transact-SQL)"

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

Comments