August 25, 2009 at 12:25 pm
I'm having trouble finding a specific answer to this question. I have a stored procedure in SQL Server 2005 that is going to return results (actually, several result sets) using FOR XML. The application calling the stored procedure is using Enterprise Library to retrieve any and all results (I have no say in this). I just need the XML results as a string. The results will probably be more than 4,000 characters long. I found documentation in Books Online that suggests if I use FOR XML PLAN, TYPE (for example) that the entire string will be returned rather than just the first 4,000 characters, but that does not seem to be working. How do I get the entire XML as a string?
Thank you,
Andrew
August 25, 2009 at 12:32 pm
this is often a matter of how you use what you get back, rather that what is being returned from the stored procedure. I've returned results that are substantially bigger than that using the FOR XML syntax.
In general with something that big, you usually want to switch to trying to handle this as a stream of data than something with a definite size. I would suspect you will get substantially better results if you pursue that kind of approach on the receiving end (i.e. in the code calling the proc).
Otherwise it's gonig to be difficult to help with no specifics.
Can it be a stringReader (which is a way to deal with a string stream of indefinite size)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 25, 2009 at 12:42 pm
Each XML string is going to a SQL Server 2000 stored procedure as a parameter. I know that linking the two servers would be a much more straightforward way to accomplish what I'm trying to do, but I'm not allowed to do that.
August 25, 2009 at 12:53 pm
SQL 2000 will take TEXT and NTEXT as input parameters for stored procedures so you can pass in values over 4000 characters.
August 25, 2009 at 1:06 pm
Actually, it looks like this part of the code was working -- it just looked like it wasn't because Visual Studio cut off the string value in an odd spot when I was looking at variables while debugging. And, yes, I'm successfully passing the XML to the SQL Server 2000 procedure. I'm having other errors that I believe to be unrelated, but I think this one is taken care of. So, thanks!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply