Affected versions: 6.6.1
If you are planning to upgrade to 6.6.1, you need to be aware of an issue in Autogen when you click on the button “Create Source Table”.
You will get an error about an invalid column name '#AutoDropReport#'.
Error message:
Build failed for 'stBPCS_KBPCS31FCV_RTM' in database 'dswOTC'. Executing Rule SQL for Instruction Group "cMap+Target+Source+View+Secondary+Build"-[982e891a-a65e-482b-a363-820f48a491aa]
Instruction "Drop+View+with+TARGETDATABASE+specified"-[ba510e13-55e4-4325-99b1-3cd2362a4bb5]
Failed with the following message:
The service was unable to evaluate the non-query statement. Invalid column name '#AutoDropReport#'.
SQL Statement:
IF EXISTS (SELECT 1
FROM [dswOTC].sys.sysobjects
WHERE [NAME] = 'stBPCS_KBPCS31FCV_RTM'
AND XTYPE = 'V')
AND '1' = 1
AND '1' = #AutoDropReport#
BEGIN
EXECUTE [dswOTC].dbo.sp_executesql
@statement = N'DROP VIEW [dbo].[stBPCS_KBPCS31FCV_RTM]'
END
Root Cause
As you can derive from the SQL statement the column does not exist in the underlying view and cannot retrieve the corresponding value. This issue has already been identified and the following development ticket has been opened and is being addressed by development:
[DSP-9512] - Common: 6.6.1 AE Instructions have invalid references to #AutoDropReport#
Workaround
As a workaround, please execute the following script against the affected system or open a ticket with Product Support if you have any further questions:
USE DSPCommon
--make a backup of the 2 instructions
SELECT * INTO ztInstructionSystem_SQL_Support8175_bkp_20171027
FROM ztInstructionSystem_SQL
WHERE InstructionSystem_SQLID IN ('76549d87-bc2b-44cd-a8d5-78c9ffeac00d', '2dc397ed-56b3-46e2-9413-358997b39271')
--revert the 2 instructions to their 6.6 SQL definition
UPDATE ztInstructionSystem_SQL
SET [SQL] = 'IF EXISTS (SELECT * FROM [#TARGETDCSDATABASE#].sys.sysobjects WHERE [NAME] = ''#DCSHorizontalView#'')
BEGIN
EXECUTE [#TARGETDCSDATABASE#].dbo.sp_executesql @statement = N''DROP VIEW [dbo].[#DCSHorizontalView#] ''
END
IF EXISTS (SELECT * FROM [#TARGETDCSDATABASE#].sys.sysobjects WHERE [NAME] = ''#DCSVerticalView#'')
BEGIN
EXECUTE [#TARGETDCSDATABASE#].dbo.sp_executesql @statement = N''DROP VIEW [dbo].[#DCSVerticalView#] ''
END '
WHERE InstructionSystem_SQLID = '2DC397ED-56B3-46E2-9413-358997B39271'
UPDATE ztInstructionSystem_SQL
SET [SQL] = 'IF EXISTS (SELECT * FROM [#TARGETDATABASE#].sys.sysobjects WHERE [NAME] = ''#VIEWNAME#'' AND XTYPE = ''V'') AND ''1'' = #DropOnBuild#
BEGIN EXECUTE [#TARGETDATABASE#].dbo.sp_executesql @statement = N''DROP VIEW [dbo].[#VIEWNAME#]'' END'
WHERE InstructionSystem_SQLID = '76549D87-BC2B-44CD-A8D5-78C9FFEAC00D'