Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Making direct HTTP request from sql server

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-02 : 09:34:14
sandeep writes "Is there any way to call http request direct from sql server.

I do not want to use xp_cmdshell with httpcmd.exe or my own application(exe).

I am looking for some stored procedure in built into sql server to make query so my syntax should be something like

{stored_procedure} "http://localhost/myfile.asp"

It will be good if i can get the output from my http call in some local variable like

Set @result = {stored_procedure} "http://localhost/myfile.asp"


Thanks in advance
sandeep"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-02 : 09:39:06
I can only guess that you want to retrieve the contents of a web page and insert it into a SQL Server table. There's nothing in SQL Server that does this automatically, you indeed have to use an xp_cmdshell call to a command to retrieve the page, dump it into a disk file, and then import the file using BULK INSERT, bcp or DTS.

There are ways of doing this in ASP code to retrieve other web pages, parse their contents, and then use ADO to insert the data into SQL Server. I've done it for UPS tracking numbers, and it's pretty easy. You'll have to provide more detail on what you need to do EXACTLY before I can go any further.

Go to Top of Page

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-02 : 14:57:07
Here's one way (well 2 ways actually - since this page is >8000 chars we need to use a temp table so we can use the text datatype.However for smaller amounts of data you can uncomment the commented code and comment out the references to the temp table to get the data into the @text variable). One use I have found is for getting XML - you can then use sp_xmlpreparedocument and OPENXML to get it into SQL - there's an example below this one.
[you need to download the WinHttp SDK for this to work
[url]http://msdn.microsoft.com/code/default.asp?url=/code/sample.asp?url=/msdn-files/027/001/655/msdncompositedoc.xml[/url] ]

 

DECLARE @url varchar(300)
DECLARE @win int
DECLARE @hr int
-- DECLARE @text varchar(8000)
CREATE TABLE #text(html text NULL) /* comment out to use @text variable for small data */

SET @url = 'http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=18425'

EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5',@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_OAGetProperty @win,'ResponseText',@text OUTPUT
-- IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OADestroy @win
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

select * from #text

drop table #text
go


--here's an xml example


CREATE PROCEDURE get_ebrif_data @url varchar(500)
AS
SET NOCOUNT ON

DECLARE @object int
DECLARE @hr int
DECLARE @idoc int
DECLARE @source varchar(255)
DECLARE @description varchar(255)
DECLARE @xml varchar(8000)

DECLARE @ebrif_data TABLE (
message varchar(100) ,
ersc varchar(10) ,
erst varchar(10) ,
elid varchar(10) ,
epww varchar(10) ,
etrn char(1) ,
etol char(1) ,
ecap char(1) ,
erty char(1) ,
eaux char(1) ,
escv char(1) ,
epan varchar(18) ,
eexm char(2) ,
eexy char(2) ,
estm char(2) ,
esty char(2) ,
eiss char(1) ,
escs char(3) ,
eval char(4) ,
eotr varchar(18) ,
ecur char(3) ,
eref varchar(25) ,
epac varchar(11) ,
esta char(1) ,
eaut varchar(8) ,
eeam varchar(100) ,
eerr char(4) ,
edat char(8) ,
etim char(6) )


EXEC @hr = sp_OACreate 'WinHttp.WinHttpRequest.5', @object
OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END

EXEC @hr = sp_OAMethod @object,'Open', NULL, 'GET',
@url , 'false'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END

EXEC @hr = sp_OAMethod @object,'Send'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END

EXEC @hr = sp_OAGetProperty @object, 'ResponseText', @xml
OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END

EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END

SELECT @xml = RIGHT(@xml,(len(@xml)-38)) /* Trim XML version tag */

EXEC sp_xml_preparedocument @idoc OUTPUT, @xml

INSERT @ebrif_data
SELECT * FROM OPENXML (@idoc,'/root',2)
WITH( message varchar(100) './logon/message',
ersc varchar(10) './auth/MerchantData/ersc',
erst varchar(10) './auth/MerchantData/erst',
elid varchar(10) './auth/MerchantData/elid',
epww varchar(10) './auth/MerchantData/epww',
etrn char(1) './auth/TransactionData/etrn',
etol char(1) './auth/TransactionData/etol',
ecap char(1) './auth/TransactionData/ecap',
erty char(1) './auth/TransactionData/erty',
eaux char(1) './auth/TransactionData/eaux',
escv char(1) './auth/TransactionData/escv',
epan varchar(18) './auth/TransactionData/CardData/epan',
eexm char(2) './auth/TransactionData/CardData/eexm',
eexy char(2) './auth/TransactionData/CardData/eexy',
estm char(2) './auth/TransactionData/CardData/estm',
esty char(2) './auth/TransactionData/CardData/esty',
eiss char(1) './auth/TransactionData/CardData/eiss',
escs char(3) './auth/TransactionData/CardData/escs',
eval char(4) './auth/TransactionData/eval',
eotr varchar(18) './auth/TransactionData/eotr',
ecur char(3) './auth/TransactionData/ecur',
eref varchar(25) './auth/TransactionData/eref',
epac varchar(11) './auth/TransactionStatus/epac',
esta char(1) './auth/TransactionStatus/esta',
eaut varchar(8) './auth/TransactionStatus/eaut',
eeam varchar(100) './auth/TransactionStatus/eeam',
eerr char(4) './auth/TransactionStatus/eerr',
edat char(8) './auth/TransactionStatus/edat',
etim char(6) './auth/TransactionStatus/etim')

EXEC sp_xml_removedocument @idoc

SELECT * from @ebrif_data

RETURN
go



HTH
Jasper Smith

Edited by - jasper_smith on 08/02/2002 14:59:02
Go to Top of Page

fiach
Starting Member

2 Posts

Posted - 2010-09-03 : 12:53:41
Hi,

I've managed to do this Please see:
http://networkprogramming.spaces.live.com/blog/cns!D79966C0BAAE2C7D!836.entry

The HTML is limited to 8000 bytes and non-unicode text, but I think that could be fixed.

I've checked all standard stored procs, and HTTP requests aren't native
[url]http://www.webtropy.com/articles/sql-stored-procedure.asp?SQL[/url]

Regards!

Dan
Go to Top of Page
   

- Advertisement -