How to Change PowerPivot Connection From Oracle to SQL Server

Question

I created a powerpivot table that was connected to an Oracle database. Everything has migrated to sql server. I attempted to modify the connection by performing the following:

1. Selecting "Existing Connections"

2. Selecting the Oracle Connection

3. Selecting "Advanced"

4. I attempted to select SQL from the providers drop down at the top, but only ORACLE providers were listed, SO I went down to the "Source" section and changed the provider there. I selected SQLNCLI10, filled in the required items and then selected "Test Connection" which was successful.

5. I selected OK which returned me to the list of existing connections.

Here is where the issue comes in, the connection does not retain the SQL Connection, it reverts back to Oracle.

Help!

Answer 1

Hi Whalensdad,

To different kinds of Datasource, please create the new connection string rather than rely on the older SQL connection string. If you have any other issue, please feel free to let us know.

Thanks,

---

Challen Fu

TechNet Community Support

Answer 2

Hi Whalensdad,

To different kinds of Datasource, please create the new connection string rather than rely on the older SQL connection string. If you have any other issue, please feel free to let us know.

Thanks,

---

Challen Fu

TechNet Community Support

Ok, so you're telling me to create a new connection. If I do that:

1. How do I connect the powerpivot table to the NEW connection?

2. If I CAN'T Connect the PowerPivot to the new connection, How can I connect ALL of my charts, pivot tables, measures, etc to the new PowerPivot tables.

Answer 3

It appear to me that you can keep ALL of my charts, pivot tables, measures, etc on your Excel workbook, but you have to import the data records from SQL into PowerPivot, then give the same name as your original tables' name.

---

Challen Fu

TechNet Community Support

Answer 4

Unfortunately that does not work (I tried). All of the Pivot Tables in Excel Point to the Oracle version of the PowerPivots and not the SQL. These are the steps I did.

1. Created new PowerPivots using SQL connections giving tables the same name as the Oracle versions with _SQL appended to the end.

2. Renamed the Oracle versions of the PowerPivot tables with _Oracle appended to the end of the name

3. Removed the _SQL from the PowerPivot SQL tables

4. Looked at the pivot tables in Excel and they are connected to the _Oracle versions of the PowerPivots.

Answer 5

Hi there,

Do you have SQL Native provider or SQL OLEDB provider installed on the machine that you opened your workbook on?

Chu

---

-- This posting is provided "AS IS" with no warranties, and confers no rights