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

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 ?

回答1:

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

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'. ^^

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

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.

回答2:

According to this thread on sqlservercentral.com, sp_OACreate is limited to 4000 characters.

A workaround is to split up the read into smaller "chunks" that are then concated together in SQL. Here is a code snippet from the above link, that might help you although it reads XML from file instead of through HTTP:

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

回答3:

This is what I use to overcome the limitation. I use it for a RESTful api communication. I can receive varchar(max) but am still limited on the amount of data I can send. This might get you where you need to be. The top 5 variables are the arguments I use for the sproc.

    Declare @url as varchar(1024)
    Declare @connection_type as varchar(6)='GET' --POST, PUT, GET DELETE
    Declare @post_string as varchar(max)=null
    Declare @response_text as Varchar(max)
    Declare @content_type varchar(254)='application/json'

    Declare @oa_object as Int;
    Declare @err_code as Int
    Declare @result_table Table (xml_result varchar(max))

    Select @post_string=dbo.fn_regex_replace('([ ]{2,10})|\r|\n', @post_string,'') --remove carriage returns and multiple spaces

    Exec @err_code=sp_OACreate 'MSXML2.ServerXMLHTTP.3.0', @oa_object OUT;
    If @err_code<>0
        Set @response_text=dbo.fn_oa_error_message(@oa_object)
    Else
    Begin
        Exec @err_code=sp_OAMethod @oa_object, 'open', NULL, @connection_type, @url,'false','d0b1a0aaed2a529356471de4fe99cae2','8e7aa1a91fa68d06cd027914d3aa1140'
        If @err_code<>0
            Set @response_text='Open '+dbo.fn_oa_error_message(@oa_object)
        Else
        Begin
            Exec @err_code=sp_OAMethod @oa_object, 'setRequestHeader', NULL, 'User-Agent', 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)'
            If @err_code<>0
                Set @response_text='setRequestHeader:User-Agent '+dbo.fn_oa_error_message(@oa_object)
            Else
            Begin
                Exec @err_code=sp_OAMethod @oa_object, 'setRequestHeader', NULL, 'Content-Type', @content_type
                If @err_code<>0
                    Set @response_text='setRequestHeader:Content-Type '+dbo.fn_oa_error_message(@oa_object)
                Else
                Begin
                    Exec @err_code=sp_OAMethod @oa_object, 'send', Null, @post_string
                    If @err_code<>0
                        Set @response_text='Send '+dbo.fn_oa_error_message(@oa_object)
                    Else
                    Begin
                        Set @response_text=null--make sure we don't return garbage
                        INSERT @result_table (xml_result)
                        Exec @err_code = sp_OAGetProperty @oa_object, 'responseText' 
                        If @err_code<>0
                            Set @response_text='responseText '+dbo.fn_oa_error_message(@oa_object)
                        Else
                            SELECT @response_text=xml_result FROM @result_table
                    End
                End
            End
        End
    End
    Exec sp_OADestroy @oa_object 

Although you don't need it, the error handler is below. It helps with troubleshooting.

CREATE FUNCTION
    dbo.n_oa_error_message(@oa_object int)
RETURNS varchar(max)
AS
BEGIN
    Declare @source varchar(255)
    Declare @description varchar(255)   
    exec sp_OAGetErrorInfo @oa_object, @source OUT, @description OUT        
    return 'Error: '+IsNull(@description,'no description')
END

回答4:

I used the following query to solve this issue. The problem is probably not sp_OACreate or sp_OAMethod, but the way to return the @ResponseText. Inserting the data into a table variable instead of using "@ResponseText OUTPUT" is the key. Note that I changed the @Response to VARCHAR(MAX).

DECLARE @TABLEVAR TABLE (responseXml VARCHAR(MAX))
DECLARE @URL VARCHAR(200) 
SELECT @URL = 'http://mysite/php-start/callxml.php'

DECLARE @Response NVARCHAR(MAX)
DECLARE @Xml XML
DECLARE @Obj INT 
DECLARE @Result INT

EXEC @Result = sp_OACreate 'MSXML2.XMLHttp', @Obj OUT 
EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false
EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
EXEC @Result = sp_OAMethod @Obj, SEND, NULL, ''

INSERT INTO @TABLEVAR
EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--, @Response OUT 

EXEC sp_OADestroy @Obj

SELECT @Response = responseXml FROM @TABLEVAR
SELECT @Xml = CONVERT(XML, @Response, 2)

DECLARE @handle INT

EXEC sp_xml_preparedocument @handle OUTPUT, @Xml 

SELECT *
FROM OPENXML(@handle, '/data/record', 2)  
    WITH [dbo].[tblDialogTechTemp]

EXEC sp_xml_removedocument @handle

My query suddenly returned null without changing anything. After changing 'MSXML2.XMLHttp' to 'MSXML2.ServerXMLHTTP', it started to work again. To know more about the difference between these two, see this article and Microsoft documentation.