SQL Server - SQL Server 2000 Job - HTTP Request

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

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

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

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/2026 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?

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/2026 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?

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.

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.

Did you ever find the solution to the -2147211483 problem?
Thanks
Mike