如何避免 sp_OACreate 限制? [英] How to avoid sp_OACreate limits?

问题描述

Declare @Object as Int; 
Declare @ResponseText as Varchar(8000); 
Declare @Url as Varchar(MAX);

set @Url = 'http://mysite.ru/cgi-bin/my_xml.cgi'

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; 
Exec sp_OAMethod @Object, 'open', NULL, 'get', @Url, 'false' 
Exec sp_OAMethod @Object, 'send' 
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT 
Exec sp_OADestroy @Object

SELECT @ResponseText

URL 中的 XML 长度为 4210 并且 @ResponseText 返回 NULL ，当我将长度更改为 3970 @ResponseText 时返回我的数据.sp_OACreate 是否有限制 400 ?如果是，是否可以避免?

XML length in url is 4210 and @ResponseText return NULL , when I change length to 3970 @ResponseText return me data. Does sp_OACreate have limit 400 ? If yes if it possible to avoid ?

推荐答案

尽管您的帖子主题如此，我认为问题可能出在 sp_OAMethod 而不是 sp_OACreate 本身.

despite the subject of your post i think that the issue is likely with sp_OAMethod and not sp_OACreate itself.

恕我直言，应该不惜一切代价避免从 sql 代码访问网络，但这只是我的意见，因为我不喜欢 RDBMS '浏览网络'的想法.^^

also IMHO accessing the web from sql code should be avoided at all costs but this is just my opinion because i don't like the idea having a RDBMS 'surfing the web'. ^^

要规避 sp_OAMethod 的限制，您可以尝试详细说明 msdn.

to circumvent the limitation of sp_OAMethod you can try to elaborate an answer present on msdn.

你的代码应该变成这样:

your code should become something like this:

Declare @Object as Int; 
Declare @ResponseText as Varchar(8000); 
Declare @Url as Varchar(MAX);

set @Url = 'http://mysite.ru/cgi-bin/my_xml.cgi'

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; 
Exec sp_OAMethod @Object, 'open', NULL, 'get', @Url, 'false' 
Exec sp_OAMethod @Object, 'send' 
--Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT 

INSERT #temptable ( appropriatefield )
EXEC @Result = sp_OAGetProperty @Obj, 'YourPropertyName' 

Exec sp_OADestroy @Object

该解决方案需要一个具有适当结构和数据类型的临时表来存储远程页面产生的值，这应该允许您获得超过 4k 的数据.

the solution requires a temp table with appropriate structure and datatype to store the value produced by the remote page and this should allow you to get more than 4k of data.

这篇关于如何避免 sp_OACreate 限制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！