Forum Discussion
Solved
Data Connection Command Text - Reverts On Save, Open
I've been given a workbook containing multiple SQL data connections to QA.
I've changed a parameter in the SQL Command Text in each of the data connections.
When I save the worksheet, my changes are removed and the data connection command text is reverted to the original content.
For example, I have this as the original clause:
Where SQLTable.PeriodType=2 AND TimeStamp >= (GETDATE()-60)
I have changed this to:
Where SQLTable.PeriodType=2 AND TimeStamp >= (GETDATE()-28)
Both queries runs perfectly fine, and the spreadsheet's contents are updated to reflect it.
I then Save the file, close it, and reopen the file. The (GETDATE()-28) has been restored to (GEDATE()-60).
Any changes I make to any of the data connections are reverted. For example, I have added clauses to the query, and these are removed. I have removed clauses, and these are just added back in.
I have tried exporting a connection file containing the command text I wish to preserve, and enabling "Always use connection file" -- this makes absolutely no difference. My changes are reverted when re-opening the workbook.
This is extremely frustrating. There is no apparent reason for this to be happening.
Hi Robert,
I guess you load your data into the data model, it has it's own import mechanism. Such connection could be changed by PowerPivot interface.
And without data model using SQL query you have no connection with odc file. You may create new workbook using existing odc, but all changes made in SQL query within the workbook won't be returned to odc file.
I'm not sure how exactly connections work, above is only my guess what it could be.
Hi Robert,
I guess you load your data into the data model, it has it's own import mechanism. Such connection could be changed by PowerPivot interface.
And without data model using SQL query you have no connection with odc file. You may create new workbook using existing odc, but all changes made in SQL query within the workbook won't be returned to odc file.
I'm not sure how exactly connections work, above is only my guess what it could be.
Thanks Sergei,
It looks like you're right in that PowerPivot allows me to manage the datamodel, and changes will stick post-save.
I'm pretty confused by this though, because I spoke to the developer who wrote this spreadsheet and set up the external connections and he did not use the PowerPivot add-in to manage his data model.
Edit: I misspelt your name! Sorry!
