Stored procedure to pass data to web page via url

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

Transact-SQL (2000)

Stored procedure to pass data to web page via url

Author

Topic

nony
Starting Member

3 Posts

Posted - 2009-02-27 : 08:00:02

I have been trying to write a stored procedure (using MS SQL SERVER 2000) that will retrieve data from a table in the database and pass the data to a web page via GET method. for eg the stored procedure will pass the data to this website www.domainname.com/page.asp?name=$name, where the value in $name is passed from the stored procedure. Please can someone help me out with this or with an alternative way of achieving the same result

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-02-27 : 08:04:46

Can you explain the purpose for doing this?

nony
Starting Member

3 Posts

Posted - 2009-02-27 : 08:49:36

The purpose for doing this is to build a reminder system on a website using an existing sms website from an sms provider. This system is just that a form in the reminder website submits data to the database were the stored procedure in the DB will retrieve the dada (via job schedule) and pass it to the providers webpage. www.providerweb.com/page.asp?name=$name . This webpage collects the passed data and alerts the subscriber. The provider website sends sms to the sms gateway by simply accepting data via http xml object see below :
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
path = "http://121.241.242.107/bulksms/bulksms.asp?username=" & username & "&password=" & password & "&message=" & message & "&mobile=" & mobile & "&sender=" & sender & "&route=" & route & "&type=" & typee

set xml = Server.CreateObject("Microsoft.XMLHTTP")
xml.Open "GET",path, Falses
xml.Send
'sresult = xml.responseText

if xml.responseText= "1701" then
session("success") = "Your message has been sent"
elseif xml.responseText= "1702" then
session("invalid") = "Invalid Username or Password"
elseif xml.responseText= "1703" then
session("route") = "invalid route"
elseif xml.responseText= "1704" then
session("insufficient") = "Insufficient credit"
elseif xml.responseText= "1705" then
session("invalidurl") = "Please go back and follow the right link"
elseif xml.responseText= "1706" then
session("notknown") = "unknown Error occur"
end if
set xml = Nothing
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

This is the area were i need the stored procedure to send the data to for the ("Microsoft.XMLHTTP") object to forward the info to the sms gateway, hence the subscriber is alerted immidiately on his GSM

Hope the explanation is enough

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-02-27 : 10:02:40

Well, you CAN do this in a stored procedure using the sp_OA stored procedures (see Books Online for details) but I recommend against it. For many of the same reasons against doing this:

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

If the web site is unavailable, or your internet connection is down, the process could hang in SQL Server, and if it terminates unexpectedly it could leave COM objects in memory. Using the sp_OA procedures is also a bit clunkier than regular scripting.

It would be better to offload the data and the process to an external program, like a Windows Scripting script. Your original code looks like another ASP page, since your setting Session variables. You wouldn't need that in a Windows script, you can log the responses in regular variables. Other than that the code would be identical. There's more detail here:

http://msdn.microsoft.com/en-us/library/ms950396.aspx

If you absolutely had to keep this in SQL Server, it would be better to set up Service Broker and create a queue to process your requests. It's designed to handle asynchronous processes. I haven't used Service Broker myself, but I'm not sure how your XMLHTTP stuff would fit in. You'd probably want (or need) to rewrite it using .Net assemblies instead.

Hope this helps.

nony
Starting Member

3 Posts

Posted - 2009-02-27 : 10:51:31

I will try it out and see what happpens

Thanks for your responses

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources