Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

sp_OAGetProperty @obj, 'responseText', @response OUT cannot handle large output Expand / Collapse
Author
Message
Posted Thursday, July 14, 2011 3:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 4:41 PM
Points: 112, Visits: 278
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.
Post #1141571
Posted Friday, November 4, 2011 10:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 4, 2013 12:07 PM
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
Post #1200756
Posted Friday, November 4, 2011 11:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
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
Post #1200789
Posted Friday, November 4, 2011 12:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 12,910, Visits: 32,020
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1200829
Posted Friday, November 4, 2011 12:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 4, 2013 12:07 PM
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.
Post #1200846
Posted Friday, November 4, 2011 1:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 12,910, Visits: 32,020
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1200851
Posted Sunday, December 30, 2012 6:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 9:53 AM
Points: 6, Visits: 39
Hi Lowell,

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

Thanks in advance.
Post #1401278
Posted Monday, December 31, 2012 5:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 12,910, Visits: 32,020
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1401361
Posted Monday, December 31, 2012 11:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 9:53 AM
Points: 6, Visits: 39
Thanks so much. Very tidy solution.
Post #1401458
Posted Wednesday, January 2, 2013 11:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 4, 2013 12:07 PM
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


Post #1401996
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse