0

We are having trouble sending a http post via a stored procedure on SQL Server 2000. SQL is the only method we have to send the http post.

We are apparently not sending a [fin , ack] and the connection is not shutting down quick enough. can anyone tell me what the problem is?

CREATE procedure HTTP_POST( @sUrl varchar(200), @response varchar(8000) out)
As

Declare
@obj int
,@hr int
,@status int
,@msg varchar(255)

exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT

exec @hr = sp_OAMethod @obj, 'open', NULL, 'POST', @sUrl, false
if @hr <>0 begin set @msg = 'sp_OAMethod Open failed' goto eh end

exec @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type',
'application/x-www-form-urlencoded'
if @hr <>0 begin set @msg = 'sp_OAMethod setRequestHeader failed' goto
eh end

exec @hr = sp_OAMethod @obj, send, NULL, ''
if @hr <>0 begin set @msg = 'sp_OAMethod Send failed' goto eh end

exec @hr = sp_OAGetProperty @obj, 'status', @status OUT
if @hr <>0 begin set @msg = 'sp_OAMethod read status failed' goto
eh
end

if @status <> 200 begin set @msg = 'sp_OAMethod http status ' +
str(@status) goto eh end

exec @hr = sp_OAGetProperty @obj, 'responseText', @response OUT
if @hr <>0 begin set @msg = 'sp_OAMethod read response failed' goto
eh end

exec @hr = sp_OADestroy @obj
return
eh:

exec @hr = sp_OADestroy @obj
return
GO

I call the stored procedure like so

exec HTTP_POST 'http://123.123.123.123/example.webservice?time=201205021000000'
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Imsopov
  • 155
  • 2
  • 12
  • Can you test outside of SQL (as opposed to production)? If so do you get the same issues if run from VB script? Can you execute a xp_cmdshell and run a script? – Preet Sangha May 21 '12 at 01:07
  • This could be an option, although Is it possible to force a closed connection by using this stored procedure? I think if the connection is forced closed this may solve my problem – Imsopov May 21 '12 at 02:45
  • I don't sorry. I'm just offering a way to isolate the issue first. – Preet Sangha May 21 '12 at 04:04

1 Answers1

1

I'm guessing the issue is with the underlying O/S (Windows); it's likely keeping the TCP connection open for an extended period of time after you dispose of the connection.

To verify, check netstat -a -p tcp before and after executing your sproc; look for a connection and see when it dies. I'd guess the connection disappears well after your sproc is done executing, sitting in a TIME_WAIT state. Windows will keep a connection alive for 2-4 minutes IIRC. One method to possibly counteract this is to add a custom HTTP header (Connection: Close), so that the remote server will close the connection after execution. Some "regular" code looks like this, you'll just need to adapt it to your SQL functions: serverXMLHTTP.setRequestHeader("Connection","Close");

If none of this works, I'd use Wireshark to track the connection and which packets are sent when to help isolate the issue. Good luck

Tom
  • 1,977
  • 1
  • 20
  • 19
  • It was keeping the connection open. I had to move the results from our application into a table to be processed separate. Working fine now – Imsopov Jun 01 '12 at 05:10