InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 13:08:12 GMT20http://www.sqlservercentral.com/Forums/Topic1141571-392-1.aspxHey guys, Thanks again Lowell for all your help, for what its worth this is how I got my xml file reader to build the response xml from chunks of a text reader. It's been trucking along for a quite a while with no issues. *knock on wood*[code="sql"]EXECUTE @hResult = sp_OACreate ''Scripting.FileSystemObject'' , @objFileSystem OUTIF @hResult <> 0 BEGIN EXEC sp_OAGetErrorInfo @objFileSystem, @ErrorSource OUT, @ErrorDesc OUT SET @ErrorFailPoint = ''Creating FSO'' GOTO DestroyFSO RETURN END SET @FileNameAndPath = @Path + ''\'' + @FileName -- Read fileEXECUTE @hResult = sp_OAMethod @objFileSystem, ''OpenTextFile'', @objTextStream OUT, @FileNameAndPath, 1, false, 0--for reading, FormatASCIIIF @hResult <> 0 BEGIN EXEC sp_OAGetErrorInfo @objFileSystem, @ErrorSource OUT, @ErrorDesc OUT SET @ErrorFailPoint = ''Opening Reponse File'' GOTO Destroy RETURN END SET @ResponseText = '''' WHILE @hResult = 0 BEGIN EXECUTE @hResult = sp_OAGetProperty @objTextStream, ''AtEndOfStream'', @YesOrNo OUTPUT IF @hResult <> 0 BEGIN EXEC sp_OAGetErrorInfo @objTextStream, @ErrorSource OUT, @ErrorDesc OUT SET @ErrorFailPoint = ''Checking AtEndOfStream'' GOTO Destroy RETURN END IF @YesOrNo <> 0 BREAK EXECUTE @hResult = sp_OAMethod @objTextStream, ''Read'', @Chunk OUTPUT, 4000 IF @hResult <> 0 BEGIN EXEC sp_OAGetErrorInfo @objTextStream, @ErrorSource OUT, @ErrorDesc OUT SET @ErrorFailPoint = ''Reading Chunk'' GOTO Destroy RETURN END SET @ResponseText = @ResponseText + ISNULL(@Chunk, '''') END EXECUTE @hResult = sp_OAMethod @objTextStream, ''Close'' IF @hResult <> 0 BEGIN EXEC sp_OAGetErrorInfo @objTextStream, @ErrorSource OUT, @ErrorDesc OUT SET @ErrorFailPoint = ''Closing Response File'' GOTO Destroy RETURN END -- Record response info SET @ResponseXml = CAST(@ResponseText AS XML)Destroy: EXEC sp_OADestroy @objTextStreamDestroyFSO: EXEC sp_OADestroy @objFileSystem[/code]Wed, 02 Jan 2013 11:29:25 GMTrory_hayniehttp://www.sqlservercentral.com/Forums/Topic1141571-392-1.aspxThanks so much. Very tidy solution.Mon, 31 Dec 2012 11:33:17 GMTmmilodragovichhttp://www.sqlservercentral.com/Forums/Topic1141571-392-1.aspx[quote][b]mmilodragovich (12/30/2012)[/b][hr]Hi Lowell,This will solve a problem I am having.Are you able to share your WebReader.zip again?Thanks in advance.[/quote]my original link in the other article is still good, as well as the download links... See this post, which has a link to the dll and the working example TSQL code i wrote way back in 2008;if you have any trouble, just post here again and we can help.:[b][url]http://www.sqlservercentral.com/Forums/Topic453789-5-1.aspx?Highlight=WebReader[/url][/b]now, four years plus later, i've got a CLR which does the same thing, but returns a varchar (max);that's how I do it nowadays;the code is trivial as far as complexity, if you want to switch to CLR:TSQL Code using the new function:[code]declare @results varchar(max)SELECT @results = dbo.CLR_WebQuery('http://www.sqlservercentral.com/Forums/Topic453789-5-1.aspx?Highlight=WebReader')[/code]vb.NET code of the CLR itself:[code="plain"] 'requires 'Imports System.Net <Microsoft.SqlServer.Server.SqlFunction()> Public Shared Function CLR_WebQuery(ByVal URL As String) As SqlChars ' varchar(8000) = SqlString, varchar(max) = SqlChars Dim request As WebRequest = HttpWebRequest.Create(URL) Using response As WebResponse = request.GetResponse() Using dataStream As Stream = response.GetResponseStream() Using reader As New StreamReader(dataStream) Dim responseFromServer As String = reader.ReadToEnd() Return New SqlChars(New SqlString(responseFromServer)) End Using End Using End Using request = Nothing End Function[/code]Mon, 31 Dec 2012 05:25:03 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1141571-392-1.aspxHi Lowell,This will solve a problem I am having.Are you able to share your WebReader.zip again?Thanks in advance.Sun, 30 Dec 2012 18:19:40 GMTmmilodragovichhttp://www.sqlservercentral.com/Forums/Topic1141571-392-1.aspxGlad i got you pointed in the right direction;If you can use CLR, i think there's a couple of ways you could do it that way; I had a Web Service i built once as a response to a post here where i passed the web service an URL, and it reutrne dthe html of the page found at the url; then i used a CLR to call the web service and return the varchar(max) string; I'm also under the impression you can do it with Service Broker as well, but i've never done that at all.Fri, 04 Nov 2011 13:03:29 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1141571-392-1.aspxThanks Lowell, That is the kind of code I was looking for and I am pretty certain that it will work! Also thanks for not restating the easy answer "drop sp_OA.." I mean if I've googled long enough to find this thread then obviously I know there are alternatives but like everything there are huge pros / cons to all approaches (SSIS, Executable, Services, SQLJob) and for our scenario this T-SQL approach was chosen as the best and so far is getting the job done.Have a nice weekend.Fri, 04 Nov 2011 12:53:52 GMTrory_hayniehttp://www.sqlservercentral.com/Forums/Topic1141571-392-1.aspxPretty sure sp_OACreate is limited to an NVarchar(4000) max parameter;I had written something in SQL 2000 which harvested web pages, and had to grab slices of the results from my Inet object in slices of 4K characters.here's a link to that old thread:[b][url]http://www.sqlservercentral.com/Forums/Topic453789-5-1.aspx?Highlight=WebReader[/url][/b]Fri, 04 Nov 2011 12:22:01 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1141571-392-1.aspxAs far as I know you cannot get around this using a single property. You might be able to break a return value down and return it in several properties, or some kind of index passed in..You might be able to try a SQLCLR wrapper around the COM object. Either way I suggest moving away from OLE Automation using the sp_OA methods.CEWIIFri, 04 Nov 2011 11:23:09 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic1141571-392-1.aspxHi we have hit the same limitation. Have you found a way to receive the ResponseXML.XML when it is bigger than VARCHAR(8000)?ThanksFri, 04 Nov 2011 10:31:41 GMTrory_hayniehttp://www.sqlservercentral.com/Forums/Topic1141571-392-1.aspxHi All,This is quite weird, so thought you can have some ideas, please:I use HTTP POST to send and XML file to my system and to get another XML as a response.I found out that if a response XML is bigger than X characters ( haven't found yet how much is X)my response fails. I defined @response as VARCHAR(MAX) and I use SQL 2008.I know that on a server side the created XML is of a correct structure, it just fails to pull it back if it goes over the X size.Any ideas, please?Thanks.Thu, 14 Jul 2011 03:28:31 GMTZeev Kazhdan