07-23-2019 06:42 AM
We have an excel dashboard which has successfully loaded data using OLE DB for many months. Its ACT 20 on premise. About a month ago it started giving errors on the status field and we have been unable to get it to work since. I'd really like to find someone who could provide support as we are just about at the limit of our in-house sql expertise. (I hope its ok to post this sort of request)
07-23-2019 07:45 AM
Hi Mary,
Whats the error you are getting?
07-23-2019 07:57 AM
DB_SEC_E_PERMISSIONDENIED (0x80040E09)
then if you continue it comes up with
OLE DB: An unknown or internal error has occurred
Undefined column nae o.status
Dictionary:AddTableNode failed (error = 00040E06)
07-23-2019 08:45 AM
What is the SQL you are using for o.status, is this a link to the opportunity table?
07-23-2019 08:56 AM
also with having the DB_SEC_E_PERMISSIONDENIED error have you tried creating a new connecton in a new spread sheet to see if it's a permission issue in the odc file that is created for rhe connection
07-23-2019 08:59 AM
Hi Chris,
yes to both
thanks very much for looking at this
07-24-2019 01:44 AM
Hi Chris, I should have been a little more specific - sorry
Yes, it's the o.status field in the opportunity table.
and yes, we did try it setting up a new workbook and the same thing happened.
any light you can shed would be appreciated greatly
07-24-2019 01:48 AM
Could you ppost the SQL you are using as I could try and test it
07-24-2019 01:53 AM
Select o.contact as 'Contact', o.company as 'Customer', o.opportunity_name as 'Project No.',o.opportunity_field_1 as 'Project', o.[enquiry details] as 'Enquiry Details', o.[product type] as 'Revenue Stream', o.stage as 'Pipeline Stage', o.status as 'Status', o.total as 'Potential Value', o.weighted_total as 'Weighted Value', o.open_date as 'Date Opened', o.[quotation date] as 'Quotation Date', o.estimated_close_date as 'Estimated Close Date', o.actual_close_date as 'Actual Close Date', o.record_manager as 'Admin', o.probability_of_close as 'Probability', o.[quotation number] as 'Q no.',o.[Invoice date] as 'Exp Invoice Date',company.industry as 'Industry' from opportunity o inner join company on o.company = company.company where status = 'Open' order by open_date
07-24-2019 02:43 AM
Hi Mary,
In a new sprerad sheet try creating a new connection and use the following SQL this will bring back everthing ftom the opportunity table and the company, just to ssee if you get any issues
select * from opportunity o
inner join company on o.company = company.company where status = 'Open' order by open_date