02-24-2015 08:11 PM - edited 02-25-2015 07:44 PM
I was wondering what defines which SQL tables are available through the OLE DB provider?
I expect it's a bit of an odd question so a bit of background - I'm mucking about with Act! on a test database, fiddling with custom dashboards, Excel data connections, and SQL. I'd been having trouble getting some of the more specific results I was after - couldn't get stuff like SELECT TOP(1) column_name etc to work in the XML, so I figured I'd try making a new database view and just accessing that.
I noted that the tables available through the OLE DB provider in Excel were a bit limited, though, and new views don't show up. So, possibly a SQL newbie question, but can you expand the list of tables available to the ACT! OLE DB Provider for Reporting 2.0?
I wasn't sure which subforum this question belonged to the most.
Edit: Might also add that if anyone knows a consultant who works with this, I wouldn't mind a link.
03-09-2015 09:12 PM
All the tables are available uysing the OLEDB Provider. The provider makes the logical connections to the various individual tables that make up the, for example, contact table allowing you to simply reference the one table rather than the individual tables and make the necessary connections yourself.
03-09-2015 10:57 PM
Thanks for your reply.
You say that all the tables are available - what about views?
Due to lack of success with subqueries and logic such as SELECT TOP(1) when writing queries in Act.Dashboard.DataChart.xml, I tried making a database view.
I can utilize this database view easily in Excel by connecting an SQL server data source and authenticating using ACTREADER.
However I can't even see this view if I connect using OLE DB 2.0, and attempting to SELECT from the new view in Act.Dashboard.DataChart.xml causes the query in question to fail resulting in an empty dashboard section.
What this means is that I can current provide more complex and comprehensive data to users of Excel than I can to users of the actual Act program!
Ideally I would like to be able to select from the new view I have created in SQL Server Management Studio via OLE DB 2.0. Is this possible, or am I limited to the views created by Act?