The Application Compatibility Toolkit Connector (ACT Connector) assists administrators with collecting the necessary computer and application compatibility information to help plan for a Windows deployment. In order to install the ACT Connector, a prerequisite is The Microsoft Application Compatibility Toolkit 5.5 (ACT 5.5), now replaced by a newer version, ACT 5.6.

 
I have seen many blog posts asking if the ACT Connector is compatible with ACT 5.6 referencing many issues with the configuration or null or empty data returned after an ACT Connector Report is run.

 

 

 

When you attempt to configure the ACT connector to connect to an ACT 5.6 database using the ‘Configure Your ACT Server’ wizard, you will receive the noted error message below.  It is apparent that there is a dependency on the dbo.Application_Report_vw when configuring the ACT Connector.  From what I found, there are ACT database tables that were modified in ACT 5.6. You must use the Workaround below that utilize a series of scripts referenced in this post.

 

Issue:

When Configuring ACT Connector using SCCM 2007 ConfigMgr Console, there is an error stating that dbo.Application_Report_vw cannot be found and refers to errors on invalid column names. The ACT Connector reports reference the dbo.Application_Report_vw view to report information. Without this view, the reports do not function or return invalid data.  It appears that you are unable to connect to the database view because of some changes between ACT 5.5 and ACT 5.6 databases.

Error:

[-2146232060] Invalid column name ‘myRating’.
Invalid column name ‘vendorRating’.
Invalid column name ‘microsoftRating’.
Invalid column name ‘numCommGreen’.
Invalid column name ‘numCommYellow’.
Invalid column name ‘numCommRed’.
Cannot find the object ‘Appilcation_Report_vw’, because it does not exist or you do not have permission

 

 

 

Cause:

After installing the ACT Connector you need to configure the ACT Server. When using the ConfigMgr Console to ‘Configure ACT Server’, the view in the ACT database ‘dbo.Application_Report_vw’ is inadvertently removed by the ‘Configure ACT Server wizard’ for an unknown reason.
 
Solution:
Prerequisites:
·         ACT is Installed and configured
·         ACT Connector is Installed, but not configured
  1. Dont Configure ACT Connector using ConfigMgr Console
  2. Ensure dbo.Application_Report_vw exists in ACT db
    • Script below to recreate Application _Report_vw
  3. Ensure Permissions on ACT
    • Script below to recreate permissions
  4. Use Script found here to Configure the ACT Connector http://blogs.technet.com/b/configurationmgr/archive/2009/07/01/support-for-act-5-5-with-the-act-connector.aspx
    • example: C:\scripts>ActConfig.vbs SVR02 SVR SVR02 ACT

 

 

 

Script to Recreate dbo.Application_Report_vw:
 

USE [ACT]
GO
/****** Object: View [dbo].[Application_Report_vw] Script Date: 03/21/2012******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Application_Report_vw]
AS
SELECT A.AppName
,AR.OSID
,AR.AppID
,CASE WHEN (AR.myRating32 = 0 OR AR.myRating32 IS NULL) THEN 4
ELSE AR.myRating32
END AS myRating
,CASE WHEN (AR.CompatibilityRating32 = 0 OR AR.CompatibilityRating32 IS NULL) THEN 100 — Unknown – No data
WHEN (AR.CompatibilityRating32 = 1) THEN 100 — Pledge Intent to support
WHEN (AR.CompatibilityRating32 = 10) THEN 60 — can not Install
WHEN (AR.CompatibilityRating32 = 20) THEN 60 — Does not work
WHEN (AR.CompatibilityRating32 = 30) THEN 60 — No support
WHEN (AR.CompatibilityRating32 = 40) THEN 60 — Significant issues
WHEN (AR.CompatibilityRating32 = 50) THEN 50 — Minor Issues
WHEN (AR.CompatibilityRating32 = 60) THEN 40 — Supported with fix
WHEN (AR.CompatibilityRating32 = 70) THEN 40 — Windows Update
WHEN (AR.CompatibilityRating32 = 80) THEN 30 — Works As IS
WHEN (AR.CompatibilityRating32 = 90) THEN 20 — Vendor Signed Support
WHEN (AR.CompatibilityRating32 = 99) THEN 10 — Games for Windows Logo
WHEN (AR.CompatibilityRating32 = 100) THEN 10 — Certified
ELSE 100
END as vendorRating
,0 as microsoftRating
,AR.Works32 as numCommGreen
,AR.PartiallyWorks32 as numCommYellow
,AR.DoesNotWork32 as numCommRed
FROM dbo.Application_Report AR WITH (NOLOCK)
JOIN dbo.Applications A WITH (NOLOCK)
ON AR.AppID = A.Identity_Hash
GO
 
 

Script to set permissions on ACT db and views:
 
USE [ACT]
GRANT CONNECT to guest
GRANT SELECT ON Application_Report_vw to guest
GRANT SELECT ON Applications_vw to guest
GRANT SELECT ON Machine_Installed_App_vw to guest
GRANT SELECT ON Machines_vw to guest
GRANT SELECT ON Deployment_OS_vw to guest
GRANT SELECT TO [NT AUTHORITY\ANONYMOUS LOGON]