SQL Server - SQL Server 2000 Job - HTTP Request

Asked By Srinivas Yanamandra on 02-Apr-04 11:34 PM
Hi, 
I am trying to send SMS messages through SQL server. 
For this I'm using XML interface to make a HTTP request to the remote server. 
The VBScript works fine when I run in the ASP page. 

But when I try to run the same as a job in SQL server it's not working. 

Do I need to make any security checks to make SQL server to allow to send HTTP requests? 

Please do suggest me what to be done, 

Regards, 
Srinivas

What does your VBScript look like? - Asked By Dexter Dotnetsky on 03-Apr-04 02:59 PM

For example, if it says Server.CreateObject, that's not going to work as a SQL Agent job because "Server" isn't there. 

It also depends whether its inline VBScript in the SCript window (in which case you would need a Sub Main wrapper) or whether you are using EXECCMD type for the step
to run a batch file (that could run a .vbs script). 

Also, depending on the identity your SQL Agent jobs run under, the script could still fail with createobject permissions denied.

So there are a number of "ifs" in this equation.  HTH

You could use sp_OACreate - Asked By Peter Bromberg on 03-Apr-04 09:50 PM

to handle it entirely within SQL Server if you wanted.

Here is a stored proc I wrote to do an HTTP "GET" on a passed - in URL

You could modify this to do a POST instead, and have it set any request headers
and send out your SMS message in whatever format is required:

CREATE  PROC dbo.XMLHTTPGET
 @url varchar(300)
 AS
DECLARE @win int
DECLARE @hr int 
  
 CREATE TABLE #text(html text NULL) 
EXEC @hr=sp_OACreate 'MSXML2.XMLHTTP.4.0',@win OUT 
IF @hr <> 0 
EXEC sp_OAGetErrorInfo @win 
EXEC @hr=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 
EXEC @hr=sp_OAMethod @win,'Send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 
/* comment out below to use @text variable for small data */
 INSERT #text(html)
EXEC @hr=sp_OAGetProperty @win,'ResponseText'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win
  EXEC @hr=sp_OADestroy @win 
 IF @hr <> 0 EXEC sp_OAGetErrorInfo @win
 select * from #text

This is what I'm doing - Asked By Srinivas Yanamandra on 03-Apr-04 10:10 PM

Dim xmlDOMDocument

Set xmlDOMDocument=CreateObject("MSXML2.DOMDocument")

Then appending all the tags to the XML document

Then creating a request

Dim xmlRequest
xmlRequest="<?xml version=""1.0"" encoding=""UTF-8""?>" & xmlDOMDocument.xml

Making an http request like

Set oHTTP=CreateObject("Microsoft.XMLHTTP")
oHTTP.open "POST", httpAddress, False

sending the request
oHTTP.Send xmlRequest

Set xmlDOMDocument=Nothing
Set oHTTP=Nothing

I've added the notification, to my email id, but that doesn't give me much information, why the job is failing. It simply says,

JOB RUN:	'SendMSG' was run on 04/04/2004 at 2:59:17 p.m.
DURATION:	0 hours, 0 minutes, 0 seconds
STATUS: 	Failed
MESSAGES:The job failed.  The Job was invoked by User sa.  The last step to run was step 1 (SendMSG).

Can you please tell me what can go wrong here?
But it's running fine whenI run the sam thing as a ASP file with the Server.CreateObject...

Regards,
Srinivas

Do I need to Provide any permission in the sql server for using createObject?
I'm using my VBScript like this - Asked By Srinivas Yanamandra on 03-Apr-04 10:13 PM
Hi, 

At the moment I'm using the VBScript like this......

Dim xmlDOMDocument 

Set xmlDOMDocument=CreateObject("MSXML2.DOMDocument") 

Then appending all the tags to the XML document 

Then creating a request 

Dim xmlRequest 
xmlRequest="<?xml version=""1.0"" encoding=""UTF-8""?>" & xmlDOMDocument.xml 

Making an http request like 

Set oHTTP=CreateObject("Microsoft.XMLHTTP") 
oHTTP.open "POST", httpAddress, False 

sending the request 
oHTTP.Send xmlRequest 

Set xmlDOMDocument=Nothing 
Set oHTTP=Nothing 

I've added the notification, to my email id, but that doesn't give me much information, why the job is failing.  It simply says, 

JOB RUN: 'SendMSG' was run on 04/04/2004 at 2:59:17 p.m. 
DURATION: 0 hours, 0 minutes, 0 seconds 
STATUS: Failed 
MESSAGES:The job failed.   The Job was invoked by User sa.   The last step to run was step 1 (SendMSG). 

Can you please tell me what can go wrong here? 
But it's running fine whenI run the sam thing as a ASP file with the Server.CreateObject... 

Regards, 
Srinivas 

Do I need to Provide any permission in the sql server for using createObject?
Gaveup the idea - Asked By Srinivas Yanamandra on 05-Apr-04 07:14 PM
Hi,

First of all my sincere thanks for all your suggestions.

But I gaveup the idea of this SQL server agent job, as this may lead me into trouble when the Triggers fires the job at the same time.

Anyhow I did not get much time to work further on it.

So I've made a exe in VB and scheduled it. And it's running fine at the moment.

May be it's not a good idea but IT WORKS at the moment.

Thanks once again.
Brilliant - But how can you change it to a POST - Asked By Mark Valente on 08-Jun-04 07:05 PM
I am trying to use a stored procedure triggered by a record update that will POST an XML request then parse the response and update the record.

I tried modifying your sample procedure to do the POST, but I am getting some errors on the Send line:

Specifically I'm trying this:

   EXEC @hr=sp_OAMethod @object, 'Send', @XML

where the @XML is a complete XML Request.

The response is: 

-2147211483

(1 row(s) affected)

Error	Source	Description	HelpFile	HelpID
0x80042725	ODSOLE Extended Procedure	sp_OAMethod usage:  ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]		0


I can't seem to find any documentation for the error numbers, nor any other TRANSACT SQL examples so thanks in advance for any input you have.
Any Luck - Asked By mike dugan on 15-Aug-05 08:43 PM
Did you ever find the solution to the -2147211483 problem?
Thanks
Mike