July 14, 2011 at 3:28 am
Hi 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.
November 4, 2011 at 10:31 am
Hi we have hit the same limitation. Have you found a way to receive the ResponseXML.XML when it is bigger than VARCHAR(8000)?
Thanks
November 4, 2011 at 11:23 am
As 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.
CEWII
November 4, 2011 at 12:22 pm
Pretty 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:
http://www.sqlservercentral.com/Forums/Topic453789-5-1.aspx?Highlight=WebReader
Lowell
November 4, 2011 at 12:53 pm
Thanks 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.
November 4, 2011 at 1:03 pm
Glad 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.
Lowell
December 30, 2012 at 6:19 pm
Hi Lowell,
This will solve a problem I am having.
Are you able to share your WebReader.zip again?
Thanks in advance.
December 31, 2012 at 5:25 am
mmilodragovich (12/30/2012)
Hi Lowell,This will solve a problem I am having.
Are you able to share your WebReader.zip again?
Thanks in advance.
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.:
http://www.sqlservercentral.com/Forums/Topic453789-5-1.aspx?Highlight=WebReader
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:
declare @results varchar(max)
SELECT @results = dbo.CLR_WebQuery('http://www.sqlservercentral.com/Forums/Topic453789-5-1.aspx?Highlight=WebReader')
vb.NET code of the CLR itself:
'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
Lowell
December 31, 2012 at 11:33 am
Thanks so much. Very tidy solution.
January 2, 2013 at 11:29 am
Hey 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*
EXECUTE @hResult = sp_OACreate ''Scripting.FileSystemObject'' , @objFileSystem OUT
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objFileSystem, @ErrorSource OUT, @ErrorDesc OUT
SET @ErrorFailPoint = ''Creating FSO''
GOTO DestroyFSO
RETURN
END
SET @FileNameAndPath = @Path + ''\'' + @FileName
-- Read file
EXECUTE @hResult = sp_OAMethod @objFileSystem, ''OpenTextFile'', @objTextStream OUT, @FileNameAndPath, 1, false, 0--for reading, FormatASCII
IF @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 @objTextStream
DestroyFSO:
EXEC sp_OADestroy @objFileSystem
June 5, 2013 at 11:31 am
Ran into the same problem took awhile to figure out, here is a very easy way around the problem. This is on SQL 2008 R2
Declare @Object as Int;
Declare @Response table(responseText text);
EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'get', 'http://www.mysite.com/myxml.xml', 'false'
EXEC sp_OAMethod @Object, 'send'
INSERT INTO @Response EXEC sp_OAGetProperty @Object, 'responseText'
--Select responseText from @Reponse
Declare @xml xml,
@hdoc int; --xml document handle
select @xml = responseText from @Response
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
SELECT *
FROM OPENXML (@hdoc, '/myrootnode/',1)
EXEC sp_OADestroy @Object
EXEC sp_xml_removedocument @hdoc
July 10, 2015 at 7:46 am
jamorton3 (6/5/2013)
Ran into the same problem took awhile to figure out, here is a very easy way around the problem. This is on SQL 2008 R2
Thanks for that! A neat solution, accepting the sp_OA route has been selected. I can't believe that the thread went dead after you posted this two years ago.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply