April 21, 2009 at 4:23 am
Hi All,
I m populating the XML resultset from the stored procedure through the temp table.
The procedure returns the XML resultset only for the first time execution. I get the null resultset when i execute the procedure more than once.
Please help in resolving the issue.
Thanks in advance.
Regards
Priya
April 21, 2009 at 4:28 am
Post the procedure?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2009 at 4:46 am
Thanks for your quick response. I have attached the procedure.
Please help in solving this issue.
Regards
Priya
April 21, 2009 at 4:57 am
Firstly this is a problem
If Exists(Select Name from tempdb.sys.objects Where Name like '#Export%' and type = 'U')
Begin
Drop Table #Export
End
That exists will find any instance of that table, even one that another connection created. It's quite possible if this proc can run more than once at a time, the exists will return 1 and the drop will fail because the table that it's found is someone else's and cannot be dropped.
That's a lot of code to try and debug. I suggest that you work through it piece by piece with lots of PRINT statements to see what the row count is on each insert.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply