"The following SQL statement produced an error" followed by "Invalid object name" when installing additional products

Problem

You attempt to install a Microsoft Dynamics GP add-on product and you receive one of the following messages:

"The following SQL statement produced an error: sql_statement";
or
"The following SQL instruction produced an error: sql_statement"

Followed by:

"ERROR [Microsoft][SQL Native Client][SQL Server]Invalid object name: object_name"
or
"ERROR [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name: object_name"

Cause

This error is due to an incorrect version number in your DYNAMICS.dbo.DB_Upgrade table for the add-on in question. Possibly, the add-on was installed and removed a few versions back and all SQL Server related objects dropped. When Dynamics Utilities attempts to upgrade the objects for the inexisting add-on, to your current product versions you will receive the errors indicated above.

Solution

If this is a new install of the add-on and it has not been used before, the best course of action would be to alter the version information within DB_UPGRADE table to force GP to reinstall the add-on on your current product version. Before doing this though, be sure to make a backup of the DYNAMICS database.

You can then update the corresponding add-on product information in the DB_UPGRADE table by running the following SQL statement:

ForceProductIDUpgrade.SQL

-- Created by Mariano Gomez, MVP
-- Code is provided "as is". No warranties expressed or implied
USE DYNAMICS
GO
UPDATE DB_UPGRADE SET db_verMajor=0,
  db_verMinor=0,
  db_verBuild=0,
  db_verOldMajor=0,
  db_verOldMinor=0,
  db_verOldBuild=0,
  db_status=0
WHERE PRODID = theProductID and db_name = 'YourDBName'

NOTE: Replace theProductID with the integer value corresponding to the add-on you are attempting to install, for example, for Fixed Assets use 309. Replace YourDBName with the database name of the company which you are trying to install the add-on.

You must also look for any tables in the DYNAMICS database corresponding to the product you are attempting to install and remove these by issuing a DROP TABLE tableName statement. Many add-ons store version information separately.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/