December 18, 2008 at 9:22 am
Hi,
Been given the task of executing a call to a remote HTTPS site using T-SQL.
The url is built of various parameters and is basically mimicking a user logging on to the site, filling in
some dropdown boxes and checkboxes and clicking a 'Save' button. This will generate a user
record at the remote site.
So my question is this, can this be done within T-SQL or do I need to use a language such as C#?
A made up example of the url would be...
https://www.MyUserSite.com/servlet/Acme/code=abc&CustId=1234&ProductId='ABC1234'&Qty=3
Looking to implement this in SQL2K but in the very near future it would go onto SQL2005.
Thanks in advance
Dave
December 18, 2008 at 9:29 am
I'm not sure if you can do it in T-SQL, but C# is definitely much better for this task.
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
December 18, 2008 at 9:32 am
You probably want the C# to actually call the website. For logging, you'd also use C# to call a stored proc, which would then store the data in SQL Server.
HTH. Happy Holidays!
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
December 18, 2008 at 9:36 am
Hi,
Yes I thought C# would be the answer.
Is "calling" the right terminology for the action do you think?
Dave
December 18, 2008 at 9:39 am
david.gray (12/18/2008)
Hi,Is "calling" the right terminology for the action do you think?
Or is "visit" more acurate... 🙂
December 18, 2008 at 9:52 am
So I guess there are a couple of basic questions here...
1, is this some sort of API or whatever that you are being forced to interact with and this is the only means by which to do it?
If Not then this opens up all kinds of possibilities.
2, do you already have the information for this user in a database somewhere, and if so, how quickly after it is added to your db does it need to be passed on to this remote site?
If it's 2 then you might want to review the following blog post by rbarryyoung. I saw him do a presentation at a code camp about service broker with a situation where he was passing a new user account info from a local site to a remote one using service broker. Here's a link to the info. http://www.movingsql.com/dnn/Home/tabid/125/EntryID/175/Default.aspx.
Either was as another poster said your best best is probably to handle this with some .net code which will accept the appropriate params for username, name etc then all you have to do is have service broker call that piece of code when you're ready to send it across the wire.
The only other way I could think of to do it, isn't really a TSQL thing, but you could download wget.exe (yeah yeah some people think it's spyware because some spyware has used it in the past, but it's really just a cms line web browser.) You can execute it using xp_cmdline or probably could have service broker call it with the appropriately created string passed as the url...
-Luke.
December 18, 2008 at 10:03 am
Luke L (12/18/2008)
So I guess there are a couple of basic questions here...1, is this some sort of API or whatever that you are being forced to interact with and this is the only means by which to do it?
If Not then this opens up all kinds of possibilities.
2, do you already have the information for this user in a database somewhere, and if so, how quickly after it is added to your db does it need to be passed on to this remote site?
If it's 2 then you might want to review the following blog post by rbarryyoung. I saw him do a presentation at a code camp about service broker with a situation where he was passing a new user account info from a local site to a remote one using service broker. Here's a link to the info. http://www.movingsql.com/dnn/Home/tabid/125/EntryID/175/Default.aspx.
Either was as another poster said your best best is probably to handle this with some .net code which will accept the appropriate params for username, name etc then all you have to do is have service broker call that piece of code when you're ready to send it across the wire.
The only other way I could think of to do it, isn't really a TSQL thing, but you could download wget.exe (yeah yeah some people think it's spyware because some spyware has used it in the past, but it's really just a cms line web browser.) You can execute it using xp_cmdline or probably could have service broker call it with the appropriately created string passed as the url...
-Luke.
Hi,
I've not had chance to speak to the owners of the site as yet so I'm not sure exactly what is behind the url and
what options I have to interact with it. The site is in Australia so I will have ot call them up first thing in the morning.
There are 9 different parameters which need embedding in the url, all of which exist on our database. The data will
be available the day after our end of day procedures have run. There's also no issue with duplicates records being
created as the site will just ignore them.
I've never used service broker to be honest so will give it a look.
I'm now thinking that a simple view containing all the customer records that need updating. This could be read by
C# which will "call" the site with the fully populated url.
Just been on the C# course so it's time to put it into practice 😀
Cheers
Dave
December 18, 2008 at 11:11 am
This sounds very much like the situation RBarryYoung discussed in his talk. In that case he was just packaging up an XML doc and sending across the wire, but my understanding is that you could have service broker do just about anything you wanted...
-Luke.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply