#ActCRM and Attachment Files Anomalies

One of the great things with Act! is the ability to attach files to any entity like a Contact, Company or Opportunity etc. We received a database recently from client that they wanted cleansed, upgraded and re-designed. As this dB was quite an old system that had “been through the wars” and several upgrades over the years (I think it started life as an Act! 2000 dB!) there was a fair amount of cleansing to do for us within the tech team.

dB cleansing is usually not only a complex task but also time intensive where we run a dB through various internal tools and scripts developed by us over the years. One region within most Act! dBs that requires our intense gaze is upon the Activities and then the Attachments as these sections touch so many areas with an Act! dB.

One interesting thing about Act! attachments is that when you delete a Contact, the attached files within the dB are not physically removed from the system. Attached files reside in the Attachments folder of the supplemental folders, e.g. if your dB is called Act2014Demo at the location of the ADF and ALF files you will find a folder called .\ACT2014Demo-database files and within that a folder named Attachments.

Act! supplemental folders

It is generally accepted as a rule of thumb to carry out system purging of cleared activities etc on a fairly regular basis to reduce lock-ups in the dB and negative impacts on overall dB performance, unfortunately there is no native facility to remove unattached or orphaned files from the Attachments folder. Having too many orphaned files not only is a waste of physical space on your hard drive, but can also slow Act! down since the Indexing server within Act! that allows you to perform Universal Searches will list and index the contents of these orphaned files as well.

The real problem comes in trying to work out which files are correctly attached and which are not so the following simple script shows an example of a SQL statement that lists all the files that are currently not attached to a record in Act! and so can be safely removed.

declare @files table (ID INT IDENTITY, Filename varchar(250))
insert into @files execute master.dbo.xp_cmdshell 'DIR "C:\Users\Public\Documents\ACT\ACT Data\Databases\ACT2014Demo-database files\Attachments\" /a /b'
select  F.Filename[OrphanFiles], (select COUNT(F.filename) from @files[F] where F.Filename not in (select DISPLAYNAME from TBL_ATTACHMENT))[OrphanCount]
from @files[F]
where F.Filename not in (select DISPLAYNAME from TBL_ATTACHMENT)

What’s interesting here is that you can ask SQL to run a cmd line utility to list the contents of a folder. There are many DBAs that would balk at the idea of leaving this security gap wide open, however since we are working in the “lab” this is something that we do fairly routinely. If this ability has been turned off, then your only option then is to create a custom CLR for your SQL instance which is quite a bit more involved.

What we usually do with this SQL statement is embed it within a Powershell script with literates through the file list and deletes each file. The result is an Act! dB with a nice new hair-cut.

An Automated Weekly History Report Email for #ActCRM Using PowerShell

I just want to apologise firstly that I haven’t been updating my blog for a little while and this could be my first post this month, but I’ve been in hospital for a little while and then on medication which has left me a little…incapacitated!

Today’s post brings in a couple of technologies and techniques together to produce an email that contains  a simple table showing the Histories created by all Users during the past week. This is actually a very simplified request that we received a little while ago from a client who was going away on holiday for a couple of weeks but still wanted to keep a tab on how their team were performing.

We’re going to use Microsoft’s PowerShell to run a SQL statement against the Act! SQL instance and then use a SMTP connection to the internal SMTP server (Exchange Server in our client’s case) to them email it out on a schedule set by nothing more exciting than the Windows Scheduler!

Now there are add-ons that will do this in a much more WYSIWYG and point-‘n’-click manner, but my view on this is; where’s the fun in that and isn’t it cheaper for the client to use the native skills of their Act! Consultant and existing free technology already present within their architecture?

Below is the script for doing this, I’ve added some comments to explain some of the parts and have tried to segment the scripts into blocks. In the current state, the script would need to be run on the server hosting the Act! dB and the scheduler to be running logged in as either a local server administrator or as a Domain administrator since it uses that to log into the SQL instance.

One word of caution though, if you’re not too comfortable with PowerShell or SQL, I would recommend that you don’t try this on your own and try to liaise with someone who is more comfortable with the technologies before using it on a production dB.

I’ve noticed that the image is a little small so here is the script itself that you could copy and paste if you’re feeling especially lazy!

clear #clears the screen of any other commands etc.
#-----------This is the PS connector to the native SQLCMD of SQL Server
if((Get-PSSnapin -Name sqlserverprovidersnapin100 -ErrorAction SilentlyContinue) -eq $null){
add-PSSnapin sqlserverprovidersnapin100
add-PSSnapin sqlservercmdletsnapin100
}
#--------------------------------------------------------------------------------------------

$dbInstance = ".\act7" #The machine hosting the Act! dB SQL Instance
$actDb = "Act2013Demo" #The Act! dB we want to report on

#----------------SQL Statement----------------------------------------------------------------
$myExp = invoke-Sqlcmd -ServerInstance $dbInstance -Database $actDb -Query "
            SELECT     
                    Count(TBL_HISTORY.HISTORYID)[Count], 
                    TBL_HISTORYTYPE.NAME AS [History Type], 
                    TBL_USER.USERLOGIN AS [User]
                FROM         
                    TBL_HISTORY INNER JOIN
                    TBL_HISTORYTYPE ON TBL_HISTORY.HISTORYTYPEID = TBL_HISTORYTYPE.HISTORYTYPEID INNER JOIN
                    TBL_USER ON TBL_HISTORY.CREATEUSERID = TBL_USER.USERID
                WHERE
                    TBL_HISTORY.CREATEDATE >= DATEADD(WW,DateDiff(WW,0,GetDate())-1,0)
                AND
                    TBL_HISTORY.CREATEDATE <= DATEADD(WW,DateDiff(WW,0,GetDate()),0)            
                GROUP BY
                    TBL_HISTORYTYPE.NAME, TBL_USER.USERLOGIN
            " | Select-Object Count, 'History Type', User
#-----------------------------------------------------------------------------------------------
#---------------Send out Email------------------------------------------------------------------
$emailFrom = 'actAdmin@caldere.com'
$emailTo = 'SalesManager@caldere.com'
$subject = "History Activity Report for week: {0}" -f((Get-Date -UFormat %V)-1)
Send-MailMessage -SmtpServer 'MyMailServer' -From $emailFrom -To $emailTo -Subject $subject -Body $myExp -BodyAsHtml
#------------------------------------------------------------------------------------------------

I’d be genuinely interested to read any comments anyone would like to make on this or if they can find ways to improve this. The original script created for the client was a little more complicated to include CSS styling and further SQL queries specific to their request.