Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
BOR15K
BOR15K
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 445
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.
rory_haynie
rory_haynie
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 6
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
Elliott Whitlow
Elliott Whitlow
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6208 Visits: 5314
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
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14887 Visits: 38887
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!

rory_haynie
rory_haynie
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 6
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.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14887 Visits: 38887
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!

mmilodragovich
mmilodragovich
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 39
Hi Lowell,

This will solve a problem I am having.
Are you able to share your WebReader.zip again?

Thanks in advance.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14887 Visits: 38887
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!

mmilodragovich
mmilodragovich
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 39
Thanks so much. Very tidy solution.
rory_haynie
rory_haynie
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 6
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search