Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Recursive Stored Procedure

Author  Topic 

Starting Member

2 Posts

Posted - 2001-12-28 : 16:01:41
I am having problems executing a recursive stored procedure via ADO that works fine in Query Analyzer.

I have two tables, one the parent and the other the child.
I have a stored procedure that populates the child table from the parent table via a recursive routine - used to report hierarchical data for an outline. (In all, there are about 25 records in the parent and about 5 levels of recursion are performed.) I have a second stored procedure that deletes all child records then calls the first procedure to populate the child table with new records.

When I execute the second, or the first with the appropriate parameters from within Query Analyzer (QA) all works well. However, when I call these same procedures from an Active Server Page (ASP) via ADO the population of the child table only results in about half of the records being entered into the child table. I get no error messages or any indication that anything wrong happened. Yet, if I go back to QA and rerun the same stored procedure all works fine.

I can't find or see anything wrong with my code or stored procedures. Does anybody have an indication as to why this is happening?

Thanks in advance for your help,

Steve Seier

SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-30 : 02:03:54

I've no idea what's causing your problem, but you might want to read some of our articles on hierarchies (do a search on "tree") to see if there is a better approach.

BTW, how are you calling this stored procedure? With an ADO Command object? Connection.Execute? Have you checked both the Connection object as well as the Err object for possible errors?

1000 Posts, Here I come! I wonder what my new title will be...
Go to Top of Page

Starting Member

2 Posts

Posted - 2001-12-31 : 08:33:16
Problem resolved.

I was using the ADO recordset.execute method as I do for nearly all my calls to execute stored procedures. However this method produced this result. I changed from recordset.execute to ADO command.execute with an explict close on the command when finished and it works properly. (Documented in an old ADO 2.1 manual)

Why does it do this? I have no idea but it solved my problem and it works.

Thanks to all for your help - Steve

Go to Top of Page

- Advertisement -