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.