SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Making direct HTTP request from sql server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 08/02/2002 :  09:34:14  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

USA
15636 Posts

Posted - 08/02/2002 :  09:39:06  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

United Kingdom
846 Posts

Posted - 08/02/2002 :  14:57:07  Show Profile  Visit jasper_smith's Homepage  Reply with Quote
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
http://msdn.microsoft.com/code/default.asp?url=/code/sample.asp?url=/msdn-files/027/001/655/msdncompositedoc.xml ]

 

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

United Kingdom
2 Posts

Posted - 09/03/2010 :  12:53:41  Show Profile  Reply with Quote
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
http://www.webtropy.com/articles/sql-stored-procedure.asp?SQL

Regards!

Dan
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000