This content has been marked as final. Show 2 replies
You will have to do this at the database level. Find the location ID of the location where you have the issues with the mapping caused by the & character. Then delete all entries from the tDataMap table with that ID. You can then reload your mappings without the rogue character issues. If you do not have access to the DB you will have to get a DBA to do this
This TSQL will do the trick. It's not the cleanest as I just wrote it, but it will dynamically remove the & from the data mapping tables and replace any invalid entries with the phrase INVALID.
--Remove && from Data Maps --Charles Beyer (firstname.lastname@example.org) --NOTES : 'Hackish' version for demonstration purposes -- Declare working variables DECLARE @strTableName varchar(255) DECLARE @strSql Nvarchar(500) -- Create cursor to iterate through each Data Map table. Look in special table sysobjects to get a list of the tables. DECLARE crsDataMapTables Cursor For select name from sysobjects where name like 'tdatamap%' and xtype = 'U' Open crsDataMapTables Fetch Next from crsDataMapTables Into @strTableName --Get the name of the first Data Map table and place it into working variable While @@FETCH_STATUS = 0 Begin --While records (table names) exist, execute loop logic print 'Cleaning table : ' + @strTableName --Multi-pass updates to check the SrcKey, TargKey, and WhereClauseValue fields for the invalid character --Dynamic SQL is used below so that we can use the Table Name from the cursor.. Set @strSQL = 'UPDATE ' + @strTableName + ' set SrcKey = ''Invalid'' where SrcKey like (''%&%'')' exec (@strSQL) Set @strSQL = 'UPDATE ' + @strTableName + ' set TargKey = ''Invalid'' where TargKey like (''%&%'')' exec (@strSQL) Set @strSQL = 'UPDATE ' + @strTableName + ' set WhereClauseValue = ''Invalid'' where WhereClauseValue like (''%&%'')' exec (@strSQL) Fetch Next from crsDataMapTables Into @strTableName End --Dispose of Cursor as we are done. Close crsDataMapTables Deallocate crsDataMapTables