Loading Web Page Source from T-SQL

Question

Post reply

Loading Web Page Source from T-SQL

doplussee

SSC-Addicted

Points: 446
More actions
August 18, 2011 at 9:45 am

#390217

Hi,
Is it possible to connect to a url and load the web-page source from that url, from within T-SQL?
Thanks.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

doplussee SSC-Addicted Points: 446 More actions · Answer 1

So is this not possible. If no one knows, could anyone direct me to a place where I might be able to find this info?

Thanks.

Lowell SSC Guru Points: 323547 More actions · Answer 2

built in it's not possible...anything outside of a sql database requires something special.

you can do it with sp_OACreate, and get the data back in 4000 byte chunks untiol you have the whole page, i did that in SQL 2000 before they came up with varchar(max).

the better way would be to create a CLR function that retreives the text of a web page based ont he URL you pass it.

it's not a simple process to do from TSQL; there might be alternatives, like writing a program, or maybe using SSIS, depending on what you are trying to do.

for example, if you were just reading the text of the web page, that might be one thing, but if you were trying to save images or zip files tat appear as links int he page, that's something else.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

You can't do this just using T-SQL.

You could call a SP and return a URL from within .NET.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

doplussee SSC-Addicted Points: 446 More actions · Answer 4

I am familiar with reading a file using the OLE support. However, how do I retrieve the page source, and save it as a file? Do I have to envoke an outside tool to do this, i.e. can't be done directly using T-SQL

doplussee SSC-Addicted Points: 446 More actions · Answer 5

Nope... no images. I just want to get the page source, so I can parse it.

Lowell SSC Guru Points: 323547 More actions · Answer 6

ok 3 years ago i posted this forum answer on the same subject, along with vb6 source code and sample stuff to download a web page:

http://www.sqlservercentral.com/Forums/Topic453789-5-1.aspx

read the article and see if it helps. there's an alternative post there someone posted that i never tested.

much later i did the same thing in .NET, but i cannot seem to find my example project. If i find THAT, i'll follow up with another post. the CLR was a little easier to use, as i remember

only had problems i ever had was with misspelled/invalid urls..you end up getting stuff like a 404 answer page, isntead of the page you expected to be able to parse...watch for that, you need to validate the page ahs the content you expected.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

doplussee SSC-Addicted Points: 446 More actions · Answer 7

Lowell (8/18/2011)
ok 3 years ago i posted this forum answer on the same subject, along with vb6 source code and sample stuff to download a web page:
http://www.sqlservercentral.com/Forums/Topic453789-5-1.aspx
read the article and see if it helps. there's an alternative post there someone posted that i never tested.
much later i did the same thing in .NET, but i cannot seem to find my example project. If i find THAT, i'll follow up with another post. the CLR was a little easier to use, as i remember
only had problems i ever had was with misspelled/invalid urls..you end up getting stuff like a 404 answer page, isntead of the page you expected to be able to parse...watch for that, you need to validate the page ahs the content you expected.

Fantastic. Thanks. I will give that a try.