Community
Showing results for 
Search instead for 
Do you mean 
Reply

Excel OLE DB - Problem with Activities

New Member
Posts: 10
Country: Switzerland

Excel OLE DB - Problem with Activities

Hi there

 

I have tried to import data into Excel from ACT. This works well for History or Opportunities but always produces an error on Activities. Is there a way to get the activities into Excel?

 

I am using ACT 2011 and Excel 2010.

 

Many thanks!

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: Excel OLE DB - Problem with Activities

New Member
Posts: 10
Country: Switzerland

Re: Excel OLE DB - Problem with Activities

Hi Mike

 

I tried both...To give some more insight:I try to use the Advanced OLE DB Queries (http://kb.sagesoftwareonline.com/ci/fattach/get/23793) to get out contacts and their linked history, opportunities and activities. This works well for history and opportunities but not for activities. 

 

Why do I need this? Our management would like to have a report covering the following: not depending on any data range they would like to see per contact the last two activities and also the last two history entries. As the ACT reporting does not allow this according to my knowledge the way via Excel seemed ok to me. But if there are any other reasonable ways to do so, e.g. good software, I am happy to try those.

 

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: Excel OLE DB - Problem with Activities

First, you MUST use OLEDB2 (V1 didn't support Activities)

Three questions:
1. What error do you get?
2. Have you tried with the demo database ... just to see if it's a data issue?
3. Have you tried using ACTDIAG to rebuild the OLEDB (especially if it worked with the demo)?
New Member
Posts: 10
Country: Switzerland

Re: Excel OLE DB - Problem with Activities

Mike,

 

1. The error was as follows: The query did not run or the database did not open.

2. It works with my database if I create an empty copy of it.

3. Yes I have tried ACTDiag

 

As the connection would work with an empty copy of the database it is a bit frustrating because how could I find the activitiy or the activities which create the error?

Bronze Contributor
Posts: 1,276
Country: USA

Re: Excel OLE DB - Problem with Activities

FYI, I haven't been able to get OLEDB2 working with Activities at all.  They have a bad named column which is making the queries barf.  You can't even open the table using SQL Management Studio.  I've posted on one of the other forums that this is an issue.  I like being able to look at activities with Dashboards and I can't.

Patricia Egen
Certified ACT! Consultant, Trainer, Developer
www.egenconsulting.com
(423) 875-2652

Follow us on:


Visit our Popular ACT Wizards Page Wizards Here!

Employee
Posts: 1,163
Country: USA

Re: Excel OLE DB - Problem with Activities

Have you been unable to get it to work only in 2011 or also in 2012. I currently have 2012 SP1 installed and I'm able to run queries against the activity table without any issues, I recall being able to do the same in 2011 as well. 

 

For schufi, since it's occurring in a database, but not the empty copy, I'm assuming it's an issue with an activity, you could try modifying the query to see if you can narrow down the errant activity.

 

Patricia, what error message are you receiving when you attempt to query the activity table?

Matthew Wood
Act! SDK Support
Community Moderator
Bronze Contributor
Posts: 1,276
Country: USA

Re: Excel OLE DB - Problem with Activities

Hi Matthew.  Here's what I see when I use ODEDB2 and a query tool:  In this case I'm simply doing select all columns just to look as the view.

A .NET Framework error occurred during execution of user-defined routine or aggregate "PRC_CLR_GET_ACTIVITIES":

System.Data.SqlClient.SqlException: Invalid column name 'INVITATION'.
Invalid column name 'IS_INVITATION'.

I haven't tried this on 2012 yet.  But it failed from the beginning on 2011.  This is on my own database.  I tried it on the demo database and it works ok, so I'm wondering if it's something with my data.  However, the message doesn't give me a clue as to what is the problem because it's a Procedure call that appears to be failing.

Patricia Egen
Certified ACT! Consultant, Trainer, Developer
www.egenconsulting.com
(423) 875-2652

Follow us on:


Visit our Popular ACT Wizards Page Wizards Here!

Employee
Posts: 1,163
Country: USA

Re: Excel OLE DB - Problem with Activities

For the original poster, seeing as how the query executes in an empty copy of your database but not in one that contains data, I'm going to assume there's some problem with an activity, it might be worthwhile to create a backup, restore it and clear old activities, then attempt to connect to it again and see if you're able.

 

Patricia, I've seen this now two other times, first would you mind testing this in 2012 and see if you have the same results. Second so we can get all of this issues with the activity table aggregated if you wouldn't mind posting your results here, I'd appreciate that very much. 

Matthew Wood
Act! SDK Support
Community Moderator
Tuned Listener
Posts: 8
Country: Canada

Re: Excel OLE DB - Problem with Activities

Pat,

 

I am having the 'barfing' problems with Act! Premium v18.  Crystal and, Toplinedash are both crapping out when accessing Activities.  The culprit is the PRC_CLR_GET_ACTIVITIES.

 

Has a solution been found?  Please help!

 

Frank Buell

Montreal