04-20-2013 10:31 AM
2 problems here:
1) Why are emails showing up as attachments (with recent dates) in 'history' under ACT contacts even though Outlook Sync for Contacts is not enabled? Is there some other setting that would cause that?
2) How do I mass delete email attachment history created on 4/19 and 4/20? I didn't see any option for doing that. I went into ACTDIAG to get a field listing so I could attempt an SQL command, but the field information for the history table is withheld from that listing (and other field listings from ACTDIAG).
04-20-2013 06:07 PM
You'll need to do a join between the history table and the historytype table and then where on the createdate in the history table. You do need to be aware that direct manipulation of the sql does contradict the terms of the EULA and would invalidate your support with Sage/Swiftpage. You can list the sql tbls via:
SELECT * from information_schema.tables WHERE table_type = 'base table' ORDER BY TABLE_NAME
If you're not fully confident with SQL I would urge and caution you once again to avoid directly manipulating the ACT! data via SQL.
04-21-2013 12:02 AM
What you suggested does not list any of the related tables for the ACT database (such as the history table named tbl_history) . I tried to preface this with "use <ACT database>;" but that resulted in a permissions error "server principal ... is unable to access the database under the current security context". It's probably related to not being logged into SQL as user "sa" (don't know what the default "sa" password is)
I find it outrageous that when Sage chooses to withhold a feature from the product, trying to use SQL to make up for what's missing would violate EULA and would invalidate support. In fact, Sage provides a batch file to accomplish deletions of "contact deleted" history entries that contains SQL code (http://kb.sagesoftwareonline.com/app/answers/detail/a_id/28352/kw/ACT%20mass%20delete). Of course, it doens't work - the user will get a "SELECT permission denied..." error trying to run it.
As an alternative, can you suggest a 3rd party add-on product that would accomplish these deletions?
As far as the 'danger' of using SQL - that's what backing up the database is for.
04-21-2013 06:08 AM
The reason the emails are showing up in history is because of the email setup preference setting. This is not the same as synching contacts.
04-21-2013 10:43 AM
The sql runs fine here and on other machines. Are you running it on the server with an Admin account.
I sort of do and don;t agree with you. It would be very difficult for 1st line support to deal with an accidental delete of a key table which would have a dramatic knock on impact on the application. 1st line need to be able to provide support for users and sites where there is no formal IT support or training all the way up to global enterprise sites. What would help would be if they had a waiver system where you sign your life away but 1st line provide support on a best endevours basis maybe.
The query I provided runs via CTE. You do not need to be logged in a an Instance SA, as long as your login has Admin rights you can have access to the SQL instance with admin permissions.
You're perfectly free to disregard my warnings, I would have been re-miss if I didn't put them here. I'm merely attempting to provide free assistance to a public request for help.
04-22-2013 12:35 AM
Yes. I opened a command prompt via 'run as administrator' and I'm logged in as a Windows administrator. I ran sqlcmd -s(local)\act7 and got a 1> prompt and entered the following commands:
1> use CJ_FinalTest;
error message; "server principal ... is unable to access the database under the current security context"
I'm not really familiar with CTE. Is information_schema.tables the derived table and what expression was used to derive it's value (WITH ... AS ..., etc)? I'm not trying to accomplish anything complex here, Even the sample SQL code used by Sage in the KB article I mentioned won't work (I entered all the statements except the 'delete from...' manually). Are you referring to Admin rights as an SQL user or as a Windows user? If you're referring to SQL user, how do I determine what SQL user I'm logged in as?
04-22-2013 02:55 AM
If you're Windows login gives you either domain or local admin rights, the SQL instance inherits those so you don't need the SA pwd from Sage/Swiftpage.
Just to confirm the dB you want to use, it is on the same machine that you are running sqlcmd?
CTE = Common Table Expressions. These are pre written system scripts/funcitons within the SQL server and are very powerful, efficient and fast and allow you to interrogate the SQL server system, such as giving a count of all the Tables within a dB.