Increasing the Timeout period

Question

Post reply

Increasing the Timeout period

SeeCoolGuy

SSCrazy

Points: 2902
More actions
May 26, 2006 at 4:08 pm

#169077

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
May 29, 2006 at 8:00 am

This was removed by the editor as SPAM

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

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

Lori-138565 Ten Centuries Points: 1340 More actions · Answer 1

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 More actions · Answer 2

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: 2902 More actions · Answer 3

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 More actions · Answer 4

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