HTTP Request from SQL SERVER - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

HTTP Request from SQL SERVER

Author

Topic

ram33
Starting Member

3 Posts

Posted - 2007-12-10 : 14:20:15

Hi,
I would like to know if there is a stored procedure that I can use in sql server to make a http request and get the data in a XML format? e.g. I want to send a request as @var := sp_some_stored_proc('http://some-website.com') and be able to store the result in @var so that I can load it into the tables.

In oracle you could easily do it using utl_http stored procedure. However, not sure how to make such calls in sql server.

Google search didnot get me to right results instead i was hitting SQL ENDPOINTS etc. stuff.

Thanks,
Ram

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-10 : 15:04:32

see:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=18425

- Jeff
http://weblogs.sqlteam.com/JeffS

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-10 : 17:06:12

note that the approach in the thread Jeff posted is very unreliable:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67557
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63292

in ss2k5 you should use CLR integration for something like this:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88035

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

ram33
Starting Member

3 Posts

Posted - 2007-12-11 : 11:20:41

This is exactly what I am looking for. Its working fine as long as the web page is less than 8000 characters but it when the page is greater than 8000 chars.

I tried using varchar(max) and xml but to no avail. I cannot use text, image.

Any idea how to over come this 8000 limitation?

Thank you spirit1!
Ram

quote:
Originally posted by spirit1

note that the approach in the thread Jeff posted is very unreliable:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67557
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63292

in ss2k5 you should use CLR integration for something like this:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88035

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-11 : 11:34:04

which of the two methods are you reffering to?
in the first one i'm not familiar with the 8000 limitation solution.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

ram33
Starting Member

3 Posts

Posted - 2007-12-11 : 11:47:19

I tried spHTTPRequest stored proc. Thats one that has 8000 limitation when I use 'GET' method.

Here is how I am calling the proc:

declare @xmlOut varchar(8000)
exec spHTTPRequest 'http://10.1.2.1/20Current', 'GET', '', '', '', @xmlOut out
select @xmlOut

As I mentioned, I tried other data types but none of them worked.

Thank you!
Ram

quote:
Originally posted by spirit1

which of the two methods are you reffering to?
in the first one i'm not familiar with the 8000 limitation solution.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources