looking for someone to support with OLE DB queries from Excel not updating

[Mary Martin]

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)

[chrisbunyan]

Hi Mary,

Whats the error you are getting?

[Mary Martin]

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)

[chrisbunyan]

What is the SQL you are using for o.status, is this a link to the opportunity table?

[chrisbunyan]

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

[Mary Martin]

Hi Chris,  

yes to both

thanks very much for looking at this

[Mary Martin]

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 

[chrisbunyan]

Could you ppost the SQL you are using as I could try and test it

[Mary Martin]

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

[chrisbunyan]

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