Forum Discussion
Get source queries from PowerPivot/1103 Compatibility Mode
Hi,
A little bit of cross-posting but not getting much traction on StackOverflow, and hoping someone here might provide some clarity: excel - VBA - ModelTable get all SQL queries - Stack Overflow
As in the above link, I am looking to document our legacy Excel workbooks; we have about 50 over the estate which are connected to our SQL database via PowerPivot.
I'm able to extract the measures quite easily through the Model object and DMVs but still unable to get the source queries of the tables; each file contains one or two connections, with multiple tables attached. I'm open to any method that will help us extract this information.
Any help greatly appreciated!
4 Replies
select * from $SYSTEM.TMSCHEMA_PARTITIONS
select * from $SYSTEM.TMSCHEMA_EXPRESSIONS
lbendlin unfortunately the TMSCHEMA_* DMVs aren't accessible in a 1103 compatibility mode model so those queries aren't an option in this instance. For whatever reason, that looks to be the latest compatibility mode available via PowerPivot in Excel.
Any suggestions for 1103? I've tried to run a DISCOVER_XML_METADATA query but unable to get the XMLA connection to open correctly when pointing at the local model.
No idea - is the compatibility level higher in newer Excel versions?
