Procedure zDT_UPR00100U error: Invalid object name

It's a common practice to setup a test copy of a production database so that you can use recent, live data to test transactions, configurations, changes, and debug code.

After restoring a copy of your production database into a test database, you should run the script offered in KB 871973 (PartnerSource login required) (which I call the 'refresh test company script'). This SQL script supposedly updates all references to the database name so that everything points to your test database.

Recently while working with a test database in GP 10, I found one database reference that is not updated by the 'refresh' script. It starts with an error while trying to create or retrieve an Employee record:

A save operation on table 'UPR_MSTR' cannot find the table.

When I traced the SQL that was causing the error, I tested it and got this error message:

Msg 208, Level 16, State 1, Procedure zDT_UPR00100U, Line 2
Invalid object name 'PROD.dbo.UPR00100'.

It took me a while for me to realize that "zDT" means it is a trigger, but once I figured that out, I went to the UPR00100 table, opened the triggers folder, and modified the zDT_UPR00100U trigger.

Sure enough, there were references in the trigger that included the old production database name (PROD.dbo.UPR00100).

I simply removed the PROD.dbo reference, executed the Alter script, and everything worked fine after that.

ALTER TRIGGER [dbo].[zDT_UPR00100U] ON [TEST].[dbo].[UPR00100]
AFTER UPDATE AS /* 10.0.320.0 */ set nocount on BEGIN UPDATE UPR00100
SET DEX_ROW_TS = GETUTCDATE() FROM UPR00100, inserted WHERE UPR00100.EMPLOYID = inserted.EMPLOYID END set nocount off

So if you have to work with employee records in a test environment, something else to remember!