Community
Showing results for 
Search instead for 
Do you mean 
Reply

Trying to mass delete certain history

Copper Super Contributor
Posts: 48
Country: USA

Trying to mass delete certain history

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).

 

Bronze Elite Contributor
Posts: 2,117
Country: United_Kingdom

Re: Trying to mass delete certain history

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.

Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog
Copper Super Contributor
Posts: 48
Country: USA

Re: Trying to mass delete certain history

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.

Nickel Super Contributor
Posts: 843
Country: United States

Re: Trying to mass delete certain history

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.

Rita Kogstad
RKA Associates
Stamford, CT
Bronze Elite Contributor
Posts: 2,117
Country: United_Kingdom

Re: Trying to mass delete certain history

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.

Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog
Copper Super Contributor
Posts: 48
Country: USA

Re: Trying to mass delete certain history

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;

2> go

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?

 

Bronze Elite Contributor
Posts: 2,117
Country: United_Kingdom

Re: Trying to mass delete certain history

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.

Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog
Copper Super Contributor
Posts: 48
Country: USA

Re: Trying to mass delete certain history

Unfortunately, SQL does not appear to be inheriting those rights.  The db I'm trying to use is located on the same machine that I'm running sqlcmd.

 

 

Bronze Elite Contributor
Posts: 2,117
Country: United_Kingdom

Re: Trying to mass delete certain history

What version of ACT! are you running?
Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog
Copper Super Contributor
Posts: 48
Country: USA

Re: Trying to mass delete certain history

ACT Premium 2013