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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.  
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
threeputtedAuthor Commented:
I appreciate the help.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.