Using SQL server pakages to retrieve remote data once a day

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)

Using SQL server pakages to retrieve remote data once a day

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-02-20 : 07:35:17

Bede writes "Hi,
Is there anyway i can get sql server to rertrive a remote data
(xml from an aspx or asp page) via http?
and perform an insert into the data base once a day?

or should i look further into the at object in NT

ideally id like to have the sql server run the pacakage
(access a webpage) once a day and insert the data into its
database.

Cheers if you can help

Bede"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-20 : 20:36:59

Here's some code that will grab a web page and save it to your hard drive (or network drive):

<%
' specify page to get and disk file name
sURL = " http://yoursite.com/your-page.asp"
sFileName = "c:\cached\your-cached-page.htm"

' create a new parser object instance
Set oHTTP = Server.CreateObject("Microsoft.XMLHTTP")

' check that XMLHTTP object was created
If Not IsObject(oHTTP) Then
Response.Write "Could not create XMLHTTP object"
Response.End
End If

' set the parameters to get the page
oHTTP.Open "GET", sURL, False

' and send the request to the server
oHTTP.Send()

' check if we got a result
If oHTTP.Status = 200 Then

' get the result as a string
sReply = oHTTP.ResponseText

' create a FileSystemObject to write page to disk
On Error Resume Next
Set objFSO = CreateObject("Scripting.FileSystemObject")
If Err.Number = 0 Then Set oFile = objFSO.CreateTextFile(sFileName, True)
If Err.Number = 0 Then oFile.WriteLine sReply
If Err.Number = 0 Then oFile.Close

If Err.Number = 0 Then
Response.Write "Created page"
Else
Response.Write "Could not write disk file"
End If

Else

Response.Write "Cannot open page " & sURL

End If
%>

This is ASP code. If you want, you can remove all references to ASP objects like Server, and remove the Response.Write commands completely, and then paste it into a SQL Server job in an ActiveX job step (or in an ActiveX task in a DTS package). Once you retrieve the web data, you can write it to disk or process it from a string variable, your choice. Since SQL Server 2000 supports XML, you can write it to a drive SQL Server can access and then use the sp_xml procedures to load it. Books Online has more details.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources