Community
Showing results for 
Search instead for 
Do you mean 
Reply

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

New Member
Posts: 6
Country: United_Kingdom

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

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)

Nickel Super Contributor
Posts: 424
Country: United_Kingdom

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

Hi Mary,

Whats the error you are getting?

Highlighted
New Member
Posts: 6
Country: United_Kingdom

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

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)

Nickel Super Contributor
Posts: 424
Country: United_Kingdom

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

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

 

Nickel Super Contributor
Posts: 424
Country: United_Kingdom

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

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

New Member
Posts: 6
Country: United_Kingdom

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

Hi Chris,  

 

yes to both

 

thanks very much for looking at this

 

New Member
Posts: 6
Country: United_Kingdom

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

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 

Nickel Super Contributor
Posts: 424
Country: United_Kingdom

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

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

New Member
Posts: 6
Country: United_Kingdom

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

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

Nickel Super Contributor
Posts: 424
Country: United_Kingdom

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

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