sp_OAGetProperty @obj, 'responseText', @response OUT cannot handle large output

  • 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.

  • 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

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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.

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Hi Lowell,

    This will solve a problem I am having.

    Are you able to share your WebReader.zip again?

    Thanks in advance.

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks so much. Very tidy solution.

  • 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

  • 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

  • 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 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply