12-22-2009 12:32 PM
A user is getting this message when trying to report on custom subentities using the new ACT! 2010 OLEDB provider. The database has two different custom subentities, and any query against either of them gives this error message. A web search indicates that the problem may be some settings on the SQL database, but as far as I know we don't have access to modify these settings for an ACT! database.
I have backed up and restored the db on another install, and the same error appears. I've tried rebuilds in actdiag, including the ole/db rebuild, with no effect.
Has anyone seen a similar behaviour? Any suggestions?
01-13-2010 07:27 AM
Does this only happen with queries against the custom tables?
Are you specifying specific fields in your query or doing a "Select * "? This can give different results when something is weird...
Is there anything funky about either the names of the custom tables and/or name(s) of fields in the tables? This can cause unusual parsing in the provider too.
Perhaps the Act! Profiler may help you get to the bottom of it.
It is in the Dependencies folder of the install CD/media.
01-13-2010 01:19 PM
The error message is appearing only for select statements against the two custom subentities. All others (contact, group, etc) work properly. The only thing unusual about the names of the subentities is that one of them includes a space ("Program Activities"). The other does not ("Interviews").
I am including the field list in the select statement. The results shown in the profiler are the following, for a couple simple examples:
Time Stamp Message Type Process ID Machine Message
2010/01/13 14:59:39.246 Error 2024 1. 0. 0.127 An unknown or internal error has occurred. (SQL: "select "Program Activities".ProgramActivitiesID,"Program Activities".CREATE_DATE from "Program Activities"")
2010/01/13 14:59:48.671 Error 2024 1. 0. 0.127 An unknown or internal error has occurred. (SQL: "select Interviews.RECORD_MANAGER,Interviews.InterviewsID from Interviews")
Your suggestion about trying "select *" instead does appear to avoid the error. This may give us a partial workaround; the problem is that these select statements are generated rather than hand-written, so it is not great to have to modify them to replace the field list.