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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Recursive Stored Procedure
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sseier
Starting Member

2 Posts

Posted - 12/28/2001 :  16:01:41  Show Profile  Reply with Quote
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
sseier@hotmail.com

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 12/30/2001 :  02:03:54  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Steve,

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

sseier
Starting Member

2 Posts

Posted - 12/31/2001 :  08:33:16  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000