Increasing the Timeout period

  • SeeCoolGuy

    SSCrazy

    Points: 2871

    I'm making a webservice call using sp_OAMethod. I've recently been

    getting some timeouts because the service may take a little bit longer

    than expected. something in the area of about a minute to a minute and a half. I searched in BOL and did not find a method for

    increasing my timeout wait status? anybody have this info handy?

    -- Francisco

  • This was removed by the editor as SPAM

  • Lori-138565

    Ten Centuries

    Points: 1289

    From BOL:

    Right-click a server, and go to Properties

    Click the Connections tab

    Under Remote server connections in the Query time-out (sec,0 = unlimited) box, choose an value between 0 and 2147483647 to set the max no of seconds SQL Server will wait before timing out

    Hope this helps


    Thanks,

    Lori

  • stax68

    SSChampion

    Points: 11711

    Or:

    exec sp_configure 'remote query timeout', 300 --5 mins
    go
    reconfigure
    go

    I prefer to use TSQL whenever possible, and always save the script.

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • SeeCoolGuy

    SSCrazy

    Points: 2871

    Thank you all for responding, I'm inluding the code that I use to call the webservice: as you can see it's basically a webservice call, where do I add the timeout extension?

    CREATE PROCEDURE stp_007_CreateOrder (@sXML as NTEXT, @SO AS VARCHAR(512) = '' OUTPUT) AS

    DECLARE @object int

    DECLARE @output varchar(255)

    DECLARE @hr int

    DECLARE @src varchar(255), @desc varchar(255), @Result AS VARCHAR(500)

    DECLARE @WebSvcNameSpace AS VARCHAR(255)

    DECLARE @WebSvcWSDLURL AS VARCHAR(2000)

    SET @WebSvcNameSpace = 'http://Service.org//WebServiceOrder'

    SET @WebSvcWSDLURL = 'http://127.0.0.1/webservice/webserviceorder.asmx?WSDL'

    DECLARE @MSG AS VARCHAR(255)

    --Create the object

    EXEC @hr = sp_OACreate 'MSSOAP.SOAPClient30', @object OUT

    if @hr 0

    BEGIN

    SET @MSG = 'OACreate'

    GOTO spOAErrors

    END

    --Get Method

    EXEC @hr = sp_OAGETPROPERTY @object, N'MSSOAPINIT',NULL , @WebSvcWSDLURL

    if @hr 0

    BEGIN

    SET @MSG = 'MSSOAPINIT'

    GOTO spOAErrors

    END

    --Call the object's property and return the value

    EXEC @hr = sp_OAMethod @object, 'XMLCreateOrder',@Result OUT, @sxml

    if @hr 0

    BEGIN

    SET @MSG = 'XMLCreateOrder'

    GOTO spOAErrors

    END

    SET @SO = @Result

    spOAErrors:

    IF @hr 0

    BEGIN

    Declare @Message as Varchar (2000)

    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

    SET @Message = @src + ' ' + @desc + @MSG + ': REDBACK Service Did Not accept the Order.'

    RaisError (@Message,17,1)

    END

    --Destroy the object

    EXEC @hr = sp_OADestroy @object

    -- Francisco

  • sswar

    SSC Rookie

    Points: 39

    Put the time out after the MSSOAPINIT call and before the actual method call, with the following statement:

    -- Time out is set for 10 minute

    EXEC @hr = sp_OASetProperty @object,'ConnectorProperty', 600000, 'Timeout'

    IF @hr <> 0

    BEGIN

    EXEC sp_OAGetErrorInfo @object

    RETURN

    END

    Hope this will solve your problem.

    Thanks

Viewing 6 posts - 1 through 6 (of 6 total)

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