August 26, 2007 at 7:31 am
Hi,
It’s possible to get data in Excel 2003/2007 pivot tables by connecting to Analysis Services over HTTP as described here:
http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx
I tried it and it works fine.
I’d like now to do the same for relational data. This way and for small datasets, the user can work offline after loading the pivot table.
I’ve managed to create a sql server web service and consume it in a test .Net application by following the instructions there:
http://davidhayden.com/blog/dave/archive/2006/03/30/2896.aspx
(using SQL Server 2005 and an HTTP endpoint)
I tried then to consume the same web service in Excel 2007 through external connections / Sql Server and give “http://localhost/contacts?WSDL” as the SQL Server. Excel said that the server didn’t exist.
Is it really possible to get Excel to connect to SQL Server 2005 through the HTTP protocol (as if it were connecting to it over the local area network) and if so how?
Any help will be appreciated!
August 27, 2007 at 9:21 am
Not saying that it's not possible but i think you have two different approaches happening here. When you set up your AS connection via the web/http, the OLEDB driver understands that it's a url and works appropriately with the end point. It's more a case of 'working over http' than being a web service. The web service you set up in SQL sounds more like a 'traditional' .net WS, that is, SOAP requests/responses over the http protocol.
Maybe you could check the SQL OLEDB driver online help to see if it accepts http connections? Alternatively you could take some of your .net code and put this in to Excel and consume the WS data in code and populate the pivottable via code.
Steve.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply