This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hi all, When running some tests I noticed a call to a stored procedure was not failing as expected. The sproc in question returned a result set which is mapped to a LS entity. After some trial and error, I found that if raiserror was called before the select statement which returned the data there was an exception. But, if raiserror was called after the select statement there was no exception raised in my code. When I called the sproc directly with the same parameters using the MSSQL Management studio the sproc call failed as expected. I have hacked up a 'quick n dirty' sample based on the Northwind database to explain my query and that is attached to my post. Bryn |
|
|
This appears to be an ADO.NET issue. It appears that SqlCommand.ExecuteReader does not surface the error if the RAISERROR happens after a successful SELECT. You can verify this by calling the stored procedure directly from ADO.NET, without involving LightSpeed. This means LightSpeed has no way of knowing that RAISERROR was called (at least, no way that I can see, unless I'm missing some error indicator on SqlCommand or SqlDataReader) -- as far as .NET is concerned, there was no error! I don't know whether this behaviour of .NET is a bug or by design, but LightSpeed will remain consistent with the ADO.NET behaviour. |
|
|
Hi Ivan, Had a digg into the old C# DIY repository code to see how it previously was done. I found that the exception is fired when you move to the next result set. Really nasty Ado.Net sample code below which you should be able to cut n paste into my previous sample. Bryn SqlConnection conn = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI"); conn.Open(); SqlCommand cmd = new SqlCommand("CustomerListWithRaiserrorAfter", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@City", SqlDbType.NVarChar, 15); cmd.Parameters["@City"].Value = "London"; SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Console.WriteLine(string.Format("{0}-{1} {2}", rdr["CompanyName"], rdr["ContactTitle"], rdr["ContactName"])); } rdr.NextResult(); conn.Close(); |
|
|
Ah, right, thanks for the pointer. I guess that makes sense. Kind of. Ish. Anyway, I've put it in, so beginning with the next nightly build you should get the expected exception for a RAISERROR after a successful SELECT. |
|