2

I recently had to move a PowerPivot report from one terminal server to another. I was able to get the PowerPivot Data Connections up and running without much trouble. When I click Test Connection, it is successful. Part of this involved changing the provider (within PowerPivot) from SQLNCLI10 to SQLNCLI11.

However, the Workbook Connection String does not work. It seems that the workbook is still looking for the SQLNCLI10 provider.

Can someone please explain (or direct me to some documentation) how to update the Workbook Connection String?

whysgai
  • 21
  • 1
  • 5
  • Have you thought about opening the excel file in windows explorer and going through the multiple files looking for instances of the old data connection? Change the extension to `.ZIP`. It isn't necessarily an elegant solution but should work. – wbeard52 Jan 05 '16 at 02:05
  • I poked around in all the ziped files, but couldn't find anything. I'm not terribly familiar with the ins and outs, so most of what I was doing was running ctrl+f to see if I could find any key phrases, but I found nothing. Which doesn't seem quite right to me. – whysgai Jan 06 '16 at 17:30
  • Turns out that Notepad's find function isn't as good as I would like. I was able to manually update the connection string that way. – whysgai Jan 15 '16 at 14:56

2 Answers2

-1

Look on the Data ribbon, in Connections. This opens the Workbook Connections window, listing the connections. Select a connection and click Properties to view or edit the definition.

fixer1234
  • 27,064
  • 61
  • 75
  • 116
Mike Honey
  • 2,512
  • 2
  • 11
  • 12
  • 1
    The definition tab is greyed out, except for the password check-box and the Authentication Settings button. There is a note at the bottom that says "Some properties cannot be changed because this connection was modified using the PowerPivot Add-In." And I cannot seem to change things from the PowerPivot side, either. – whysgai Jan 12 '16 at 14:59
-1

After a lot of trial and error, I found that after you have changed the connection string in PowerPivot, you have to go to the Design ribbon, then choose Table Properties, update the preview and then click Save. After that, it's possible to save the new properties.

Excellll
  • 12,627
  • 11
  • 51
  • 78
Magnus Lander
  • 1