SQL Server Performance Forum – Threads Archive

Nested Stored Procs (sort of a .NET question)

Hello, I have searched the forums but not really found anything on this. We had an interesting problem come up that I would appreciate if anyone can shed some light on. Some background: We have a Classic ASP Web Application, which runs with a SQL 2005 DB layer, and several .NET 1.1 and .NET 2.0 COM-callable components. One of the problems we had with SQL 2005 and Classic ASP is that perfectly functional Stored Procedures, which returned results if EXECuted in QA, would work; however, when called directly from Classic ASP / ADO, would not return a recordset (an example is a SQL 2005 proc that has an IF EXISTS condition, would return results from QA but not through ADO, maybe because ADO is too old to handle these newer procs?). We worked around the issue by encapsulating ADO.NET into a .NET 2.0 COM-Callable, Assembly, essentially creating a data proxy which returns the results of the stored proc to .NET and then populates the results as properties of the .NET component, which are then available to Classic ASP. Ok that is the background. The issue I came accross yesterday that I do not understand, is this: There is a stored proc, which calls another stored proc, which in turn calls a 3rd proc (nested 3 deep). When run in QA logged in as our Webuser (read permissions only), the proc works without issues, and returns the proper value on success. However when called through the .NET data proxy, it returns a SQL error to the effect of "ROLLBACK TRANSACTION does not have a corresponding BEGIN TRANSACTION". This makes me think it is getting to the ROLLBACK for some reason other than the T-SQL in the procedure, which, again, works from QA and does not return this error. Furthermore, when I comment out the EXEC of the 3rd nested procedure, the whole thing works like it’s supposed to. My question is why does it do this? Is there a limit to how many levels deep you can call a Stored Procedure from .NET that is not imposed from within SQL itself? We got the thing working again, because not being able to call the 3rd proc doesn’t affect functionality – it just sends a report email – but really not having an idea of why is a bit maddening. All thoughts appreciated in advance, thanks!
– DK

---

ADO is not exactly old. And even old ASP should not have problems calling an SP and receiving results. The Connection or Command object of ADO should have something like a ReturnsRecords property – make sure it is set to true before executing.

---

---

i think you are limited to 32 nested level AKTHAR

---

What is the setting for XACT_ABORT? You can take help of TRY/CATCH block and no use of the @@error variable. This increases the readability of the code and much simple to write. The fundamental rule to use TRY/CATCH is that the CATCH block should immediately follow the TRY block.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

Please move to SQL 2005 section.

---

]]>