Excel Pivot Table Using Web Service

I am trying to create an Excel 2007 pivot table using a SQL query via a web service.  I am struggling with how to write the connection string to access my SQL database via a http connection.  I can run the query within the network, but when I replace the server address with a http address it won't run.

Here's the current Connection String:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ECONOMIC;Data Source=HOUSVR110\TOPDATA200;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WIN7X64NO2;Use Encryption for Data=False;Tag with column collation when possible=False

Instead of the source being HOUSVR110, it needs to be an HTTP address and the ip of my sql server.

I understand that odc (Office Data Connection) may not be the correct connection type, but am at a loss with what would be the correct one.

Any help would be appreciated.

Who is Participating?
go to Carl Prothman's web site at  
you should be able to find the connection string for your particulars there.  Examples shown below:

 OLE DB Provider for mySQL
oConn.Open "Provider=MySQLProv;" & _
           "Data Source=mySQLDB;" & _
           "User Id=myUsername;" & _
For more information, see:   API - OLE DB and Snippet

For Excel PivotTable

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
    .Connection = "OLEDB;" & _
                  "Provider=MSOLAP;" & _
                  "Location=myServerDataLocation;" & _
                  "Initial Catalog=myOLAPDatabaseName"
    .MaintainConnection = True
    .CreatePivotTable TableDestination:=Range("A1"), _
                      TableName:= "MyPivotTableName"
End With

If you have the SQL server port open then just use the IP address. Keep in mind that integrated authentication (SSPI) is unlikely to work over the internet.  
threeputtedAuthor Commented:
I appreciate the help.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

so... err.... did you find the syntax to connect to your SQL Server over http? Thats actually a contradiction - http is a web protocol and SQL Server uses its own different protocol.
threeputtedAuthor Commented:
The ado connection appears to be the right way to go.   I have an application I am developing which I would like to access the sql database via a web service.   The web service is an https: so it's still not working right with the pivot tables and I am not sure it's possible.   I have the web service running fine for retrieving data and performing queries.  Just can't get it right for the pivot tables.

If you have any ideas, I would appreciate it.
see this link:   http://office.microsoft.com/en-us/sharepointserver/ha101054761033.aspx
Excel Services in MOSS (ie. enterprise version of SharePoint 2007) will allow users to view
published Excel pivot tables in a web browser. But they won't be able to freely change the fields on rows and columns - the report designer can convert pivot table filters into drop-down report parameters, if that degree of interactivity suffices for user needs.

MOSS also comes with SQL Filter and KPI Web Parts, which connect to Analysis Services.

Deepak Puri
Microsoft MVP - SQL Server
Also see link below for 3rd party shareware:
threeputtedAuthor Commented:
Thanks for the comment puppydogbuddy.  I appreciate it.  Unfortunately, the rub is that I am trying to make this work in Excel 2007.  I haven't figured out how to route the connection string through https and my webservice which authenticates.

The current app is designed like this... a stand alone app, webservice.exe, which manages the user login, password and server address (https).  Then I have a com addin in Excel which connects to the webservice and manages the forumlas (queries).  Then the formulas are being called from a xlam addin which manages the menubar and formula names.  The design needs work still, but it's functional.

I would like the ability to have excel pivot tables access the sql server through the webservice.exe, or even if I could regulate with some advanced https:// connection string, it would work.

Thanks again.
threeputtedAuthor Commented:
I understand I can do this with a vpn connection, but I wanted to keep this standalone and not have to mess with a vpn.

Did you try a sufficient number of connection strings from the Carl Prothman site other than the ones I posted?  
See this link:      http://office.microsoft.com/en-us/excel/HP051993041033.aspx
Prerequisites for retrieving data    To retrieve data from a Web site, you need to be connected to your intranet or the Internet.

To retrieve other types of external data, you must install Microsoft Query and the appropriate open database connectivity (ODBC) (Open Database Connectivity (ODBC): A standard method of sharing data between databases and programs. ODBC drivers use the standard Structured Query Language (SQL) to gain access to external data.) drivers or data source drivers (data source driver: A program file used to connect to a specific database. Each database program or management system requires a different driver.). Query provides drivers for many types of external data, including Microsoft SQL Server, Microsoft Access, and text file databases.

See this link:  http://office.microsoft.com/en-us/excel/HP052673981033.aspx

Source data for PivotTable lists    You can create an interactive PivotTable list from an existing PivotTable report or from other Excel data. If you use other Excel data and select PivotTable functionality in the Publish dialog box, Excel creates a PivotTable list on the Web page for you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.