|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
T-SQL 258 XML Iteration Boundary?Here is the scenario… I have to get a set of particular records for a particular time frame in a MSSQL 2000 table, then I have to pass them one at a time to a web service that returns additional information as xml for each applicable record from a legacy VFP database, still in use. (hate VFP, but have no choice its a huge legacy system that is taking forever to get phased out and is in use 24x7). The web service approach has been the better way to go then linked servers to get VFP data needed by a MSSQL proc to get or do anything in VFP that would require a lot of dynamic SQL otherwise… There are never a lot of records about 1000. Everything works fine until I get to the magic number 257, then everything after that beginning with 258 returned from the web service call is null… All the values passed to the web service are good and if I run the one’s that came back null by themselves they work as anticipated… The web service works fine and have tested it a number of different ways via C# and VFP; it never fails to return a valid value until it is in MSSQL… I get the exact same results with different MSSQL Servers & IIS Servers, running local or over a internal network. I have debugged this in the MSSQL 2000 debugger locally & over our internal network and utilizing the Visual Studio 2003 & Visual Studio 2005 T-SQL debugging capabilities. Every single time no matter what records are involved; 257 iteration is good, 258 iteration is null and everything after that is null as well. Its obvious that I am hitting some kind of boundary and I am pretty sure it is a MSSQL one, but I cannot put my finger on it just yet… Maybe some kind of subtle configuration issue with MSSQL that has eluded me thus far… I am limited to sending one record at a time to the FoxPro Web Service or a linked server to the VFP side. The web service out performs the linked servers even if there is an extra hop involved in the process. In addition, the linked server approach would involve some tedious dynamic SQL… ugly… Yes I could probably break it up into smaller batches, as a workaround but that would not tell me what the problem is. Any useful, applicable comments, suggestions etc., would be most welcome. Thanks in advance. "LvBohemian" <LvBohem***@discussions.microsoft.com> wrote in message What program is calling the web service? How is the web service news:3F06F989-4D7D-4372-A1DC-8E634A0E35CF@microsoft.com... > T-SQL 258 XML Iteration Boundary? > > Here is the scenario. > I have to get a set of particular records for a particular time frame in a > MSSQL 2000 table, then I have to pass them one at a time to a web service > that returns additional information as xml for each applicable record from > a > legacy VFP database, .. . . implemented? David Hi
From your post it is not clear if this is the same data set you are using each time you make the call? e.g. if you sorted the dataset in the opposite direction, would it fail on the same record or the same number of records? Also if you paused (say for 2 minutes) after processing 257 records, does this make a difference? John Show quote "LvBohemian" <LvBohem***@discussions.microsoft.com> wrote in message news:3F06F989-4D7D-4372-A1DC-8E634A0E35CF@microsoft.com... > T-SQL 258 XML Iteration Boundary? > > Here is the scenario. > I have to get a set of particular records for a particular time frame in a > MSSQL 2000 table, then I have to pass them one at a time to a web service > that returns additional information as xml for each applicable record from > a > legacy VFP database, still in use. (hate VFP, but have no choice its a > huge > legacy system that is taking forever to get phased out and is in use > 24x7). > The web service approach has been the better way to go then linked servers > to get VFP data needed by a MSSQL proc to get or do anything in VFP that > would require a lot of dynamic SQL otherwise. > There are never a lot of records about 1000. > Everything works fine until I get to the magic number 257, then everything > after that beginning with 258 returned from the web service call is null. > All the values passed to the web service are good and if I run the one's > that came back null by themselves they work as anticipated. > The web service works fine and have tested it a number of different ways > via > C# and VFP; it never fails to return a valid value until it is in MSSQL. > I get the exact same results with different MSSQL Servers & IIS Servers, > running local or over a internal network. > I have debugged this in the MSSQL 2000 debugger locally & over our > internal > network and utilizing the Visual Studio 2003 & Visual Studio 2005 T-SQL > debugging capabilities. > Every single time no matter what records are involved; 257 iteration is > good, 258 iteration is null and everything after that is null as well. > Its obvious that I am hitting some kind of boundary and I am pretty sure > it > is a MSSQL one, but I cannot put my finger on it just yet. > Maybe some kind of subtle configuration issue with MSSQL that has eluded > me > thus far. > I am limited to sending one record at a time to the FoxPro Web Service or > a > linked server to the VFP side. The web service out performs the linked > servers even if there is an extra hop involved in the process. In > addition, > the linked server approach would involve some tedious dynamic SQL. ugly. > Yes I could probably break it up into smaller batches, as a workaround but > that would not tell me what the problem is. > > Any useful, applicable comments, suggestions etc., would be most welcome. > Thanks in advance. > > LvBohemian (LvBohem***@discussions.microsoft.com) writes:
Show quote > I have to get a set of particular records for a particular time frame in I can't understand what that boundary would be.> a MSSQL 2000 table, then I have to pass them one at a time to a web > service that returns additional information as xml for each applicable > record from a legacy VFP database, still in use. (hate VFP, but have no > choice its a huge legacy system that is taking forever to get phased out > and is in use 24x7). > The web service approach has been the better way to go then linked servers > to get VFP data needed by a MSSQL proc to get or do anything in VFP that > would require a lot of dynamic SQL otherwise > There are never a lot of records about 1000. > Everything works fine until I get to the magic number 257, then everything > after that beginning with 258 returned from the web service call is null >... > Its obvious that I am hitting some kind of boundary and I am pretty sure > it is a MSSQL one, but I cannot put my finger on it just yet. Anyway, it is pretty difficult to say anything at all without any knowledge of the code. As I understand it, data is taken from SQL Server and presented to FoxPro by means of a web service, and then data is brough back to SQL Server. But it is not clear to me how data passes. You mention XML in the subject line, but you never mention where the XML comes in. How is data brought fourth and back from SQL Server to rhe web service? Do SQL Server talk to the web service directly, or is there some other piece of codes that performs this communication? These NULL that you get back, where do they appear? When you get data from SQL Server to pass over to FoxPro? When you get data back from FoxPro? Or when you update the tables in SQL Server? -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx I am making soap calls from MSSQL 2000, latest sp's etc., on windows 2000
advanced server latest sp's etc... I am creating the soap object in mssql using sp_OACreate, sp_OADestroy etc., and passing values to the web method in the fox pro web service which returns the results in xml, to mssql... which are further processed by sp_xml_preparedocument, sp_xml_removedocument etc... on the 258th interation the returned results are always null, although, the vfp web service is returning results. This is the one piece of a very large process that is failing... sp_xml_preparedocument receives a value from the vfp web service but returns an empty (null) xml document on the 258th interation no matter what servers are involved... it has to be some process boundary or something simular that I am bumping up against. Are you saying that if you look at the document you're passing into
sp_preparedocument it has the XML data you expect but the call to OpenXML returns NULL's? If so, it sounds like sp_prepare document is failing for some reason and you're ignoring the error. Look at the 258th document and see if there's something unique about it. You could also be running out of memory or running into a COM error. -- Show quoteThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm "LvBohemian" <LvBohem***@discussions.microsoft.com> wrote in message news:6AD9FCFA-C6AB-4271-A403-BE6D06090DAD@microsoft.com... >I am making soap calls from MSSQL 2000, latest sp's etc., on windows 2000 > advanced server latest sp's etc... > > I am creating the soap object in mssql using sp_OACreate, sp_OADestroy > etc., > and passing values to the web method in the fox pro web service which > returns > the results in xml, to mssql... > > which are further processed by sp_xml_preparedocument, > sp_xml_removedocument > etc... > > on the 258th interation the returned results are always null, although, > the > vfp web service is returning results. This is the one piece of a very > large > process that is failing... > > sp_xml_preparedocument receives a value from the vfp web service but > returns > an empty (null) xml document on the 258th interation no matter what > servers > are involved... > > it has to be some process boundary or something simular that I am bumping > up > against. > > > > yes, and nothing is unique about the 258th iteration...
I get the same error using different databases, web servers, local or network... different data... no errors, just nulls... nothing shows up in any sql logs, iis logs & or windows event logs either... test has been run in isolation with nothing else running on multi-processor systems with gigs of ram, the actual data is very small. seems like it might be something related to consecutive calls to sp_prepare document, just can't seem to pin point it at the moment... And the apparent inability of T-SQL to utilize conditional breakpoints, either in the SQL 2000 debugger or the 2003 and 2005 Visual Studio IDE’s and not at all in SQL Server Management Studio made it difficult to get to this point… "Roger Wolter[MSFT]" wrote: Are you saying that if you look at the document you're passing into sp_preparedocument it has the XML data you expect but the call to OpenXML returns NULL's? If so, it sounds like sp_prepare document is failing for some reason and you're ignoring the error. Look at the 258th document and see if there's something unique about it. You could also be running out of memory or running into a COM error. -- This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm LvBohemian (LvBohem***@discussions.microsoft.com) writes:
Show quote > I am making soap calls from MSSQL 2000, latest sp's etc., on windows 2000 How about this, store all the XML documents that you receive into a table> advanced server latest sp's etc... > > I am creating the soap object in mssql using sp_OACreate, sp_OADestroy > etc., and passing values to the web method in the fox pro web service > which returns the results in xml, to mssql... > > which are further processed by sp_xml_preparedocument, > sp_xml_removedocument etc... > > on the 258th interation the returned results are always null, although, > the vfp web service is returning results. This is the one piece of a > very large process that is failing... > > sp_xml_preparedocument receives a value from the vfp web service but > returns an empty (null) xml document on the 258th interation no matter > what servers are involved... > > it has to be some process boundary or something simular that I am > bumping up against. as-is. Then write a stored procedure which accepts a text parameter and that does the prepare/OPENXML/remove stuff. Then run a query that selects the XML documetns. Then add "EXEC your_sp N'" to the beginning of each line and a closing ' to the end. No, this is not a suggestion for a cheesy workaround, but it would be interesting to see if you still get the same error. If so, the sp_OA stuff is factored out. I would first double-check that you are actually calling sp_xml_removedocument for each document, though. Checking the return code for sp_xml_preparedocument as Roger suggested is a good idea too. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx LvBohemian (LvBohem***@discussions.microsoft.com) writes:
> bug in my code... It can happen to anyone. :-)> > sp_OADestroy was not getting called explicitly... Glad to hear that you found the issue. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics