Calling a URL from a stored proc

Question

Is there a way to call a web url from a stored procedure.

We are trying to take an approach mentioned in the following article, but it mentions calling the accessing the server to be used using the web application. I want to be able to find out what server to use in a stored proc.

http://sqlcat.com/sqlcat/b/technicalnotes/archive/2010/02/08/microsoft-sql-server-2008-analysis-services-consolidation-best-practices.aspx

Please guide.

Thanks

-Sarah

Answer 1

Hi Sarah, can you explain a bit more about what you are trying to do?  The HTTP redirection section of that article is in terms of a fairly complicated Anaylsis Services load balancer.  Are you trying to create a large solution such as that or something more specific in terms of T-SQL syntax?

Thanks,
Sam Lester (MSFT)

---

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 2

Hi Sam

we are trying to impliment a load balancing scanrio on our existing servers for SSAS. We are trying to explore if the load balancing technique mentioed on page 14 of this article (figure 4) would work.

The webservice gets called from the web page whihc in directly shows which server to hit to get the record set from the query.  What I need to know if how to call the webservice from the stored proc, or the URL returned from the webpage to get the server name, so that I can use that server name in my MDX in the stored proc which gets called like this

exec(N'select * from openquery
 ('+ @ServerName +',''' + @MDXCode + ''');'
 )

In my proc eventually I need a value for the string variable @ServerName, that I will get from the approach mentioned in that article. But I do not know how to get and call the webservice from the stored proc.

Your help would be really appreciated.

Thanks much

-Sarah

Answer 3

Hi Sarah,

You may be interested of the .NET integration in SQL Server. You can create a CLR stored procedure and can call a web service with it. 

I hope it helps.

Janos

---

There are 10 type of people. Those who understand binary and those who do not.

My Blog | Hire Me