[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

stored procedure to post to another server

Posted on 2006-05-20
20
Medium Priority
?
984 Views
Last Modified: 2010-05-18
can anyone tell me how I can make an sql stored procedure that post to another server.
Basically I want to pass in the function the url to call and let it return the response

in asp I do it like this -


function mypost(url)
set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP")
xmlhttp.open "GET", url, false
xmlhttp.send "" 
tresponse= xmlhttp.responseText
set xmlhttp = nothing
mypost=tresponse
end function
0
Comment
Question by:esthera
  • 11
  • 9
20 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16731533
check out this link:
http://sqljunkies.com/WebLog/amachanic/articles/category/338.aspx

the paragraph "validate URL"...

0
 
LVL 1

Author Comment

by:esthera
ID: 16779946
not clear on how that sends to a url -- is it better then this script that I found --

do you know how in thsi script i could read the response??  how do I call it and then read the return the response?


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_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
if @hr <> 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp.3.0
failed', 16,1) return end


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
Raiserror(@msg, 16, 1)
return
GO
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16779964
>how do I call it and then read the return the response?
well, that depends alot on from where you call it?

in TSQL you can check out this:

declare @response varchar(8000)
declare @url varchar(500)
set @url = 'some url to test goes here'
exec HTTP_POST @url , @response output
select @url as url, @response as Response
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 1

Author Comment

by:esthera
ID: 16779967
i tried teh one in your article as well but it doesn't appear to actually hit the server.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16779975
exec @hr = sp_OAMethod @obj, send, NULL, ''

should be:
exec @hr = sp_OAMethod @obj, 'send', NULL, ''
0
 
LVL 1

Author Comment

by:esthera
ID: 16779997
@response as Response gives me an error -- it says it must be = something

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16780013

declare @response varchar(8000)
declare @url varchar(500)
set @url = 'some url to test goes here'
exec HTTP_POST @sUrl = @url, @response = @response output
select @url as url, @response as Response
0
 
LVL 1

Author Comment

by:esthera
ID: 16782704
is there anyway to url encode the string??  I replaced spaces with + but for some reason the url does not work going through this function but if I print it and paste it in the browser directly then it works.
0
 
LVL 1

Author Comment

by:esthera
ID: 16783020
i found that problem -- it was a problem that the url was too small.
but i'm having a different issue
my response is only showing the first  256 characters. (the response field is a varchar of 8000)  do you know why???
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16783043
in query analyzer, check the view->options there is a setting that limits by default the output per column to 256, which you can increase.
0
 
LVL 1

Author Comment

by:esthera
ID: 16783068
also when running it from my php script I get  an error:

Warning: mssql_query() [function.mssql-query]: message: EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'. (severity 14) in /var/www/html/processcommon.php on line 164

Warning: mssql_query() [function.mssql-query]: message: sp_OACreate MSXML2.ServerXMLHttp.3.0 failed (severity 16) in /var/www/html/processcommon.php on line 164


what permmisions do I need to change?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16783088
you have to grant the execute permissions on the sp_OAxxxx functions to the login/user you use to run those queries.
0
 
LVL 1

Author Comment

by:esthera
ID: 16783183
thanks for all your help - now if for some reason it errors on sending (server down or something) how would I know in my sp?
(basically if it doesn't send I want to update my table that it didn't send)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16783199
you have to analyze the value of @response after the procedure call.
0
 
LVL 1

Author Comment

by:esthera
ID: 16783222
so if it didn't hit waht woudl @response be -- blank? null?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16783242
well, I don't know. you should test with a "wrong" url and see what the outcome is
0
 
LVL 1

Author Comment

by:esthera
ID: 16783286
Server: Msg 50000, Level 16, State 1, Procedure HTTP_POST, Line 52
sp_OAMethod Send failed

now if there is an error -- I don't want the sql to error but I want it to just set an error variable = true --- can this be done in sql

I know in asp I would use on error resume next and then check the err object
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 16783317
here a small rewrite:
  there are now 2 output values:
  @status -> negative value means error
  @response -> in case of error, error message, otherwise the response

CREATE procedure HTTP_POST
( @sUrl varchar(200)
, @response varchar(8000) output
, @status int output )
As

Declare @obj int
declare @hr int

exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
-- exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
if @hr <> 0 begin
  set @status = -1
  set @response = 'sp_OACreate MSXML2.ServerXMLHttp.3.0 failed'
  return
end

exec @hr = sp_OAMethod @obj, 'open', NULL, 'POST', @sUrl, false
if @hr <>0 begin
  set @status = -2
  set @response = 'sp_OAMethod Open failed'
  exec @hr = sp_OADestroy @obj
  return
end

exec @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type',
'application/x-www-form-urlencoded'
if @hr <>0 begin
  set @status = -3
  set @response = 'sp_OAMethod setRequestHeader failed'
  exec @hr = sp_OADestroy @obj
  return
end

exec @hr = sp_OAMethod @obj, send, NULL, ''
if @hr <>0 begin
  set @status = -4
  set @response = 'sp_OAMethod Send failed'
  exec @hr = sp_OADestroy @obj
  return
end

exec @hr = sp_OAGetProperty @obj, 'status', @status OUT
if @hr <>0 begin
  set @status = -5
  set @response = 'sp_OAMethod read status failed'
  exec @hr = sp_OADestroy @obj
  return
end

if @status <> 200 begin
  set @response = 'sp_OAMethod http status ' + cast(@status as varchar(20) )
  exec @hr = sp_OADestroy @obj
  return
end

exec @hr = sp_OAGetProperty @obj, 'responseText', @response OUT
if @hr <>0 begin
  set @status = -6
  set @response = 'sp_OAMethod read response failed'
  exec @hr = sp_OADestroy @obj
  return
end

exec @hr = sp_OADestroy @obj
return
GO
0
 
LVL 1

Author Comment

by:esthera
ID: 16862787
I keep getting the error -- sp_OAMethod read response failed


any advice?
0
 
LVL 1

Author Comment

by:esthera
ID: 16883334
angelIII can you help me with the above error -- i'm willing to add points.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question