I'm running on server version 8.1.00 201301251157. Of course, I guess they may've fixed this in a subsequent patch...
-charlie On Fri, Mar 28, 2014 at 1:58 PM, LJ LongWing <[email protected]> wrote: > ** > Charlie, > All I can say is 'WOW' :).....that's just insane. Can you provide your > exact version/patch? > > > On Fri, Mar 28, 2014 at 2:27 PM, Charlie Lotridge <[email protected]>wrote: > >> ** >> If you use ARS views, this could be a problem for you. >> >> It's probably no surprise that if you modify the SQL table (or view) used >> as the "table" for an ARS view, and (effectively) rename one of the columns >> used by one of the ARS view fields, the ARS view breaks. >> >> What I discovered, though, is that if you simply go into the view >> definition modify the "Column" property in the View Information section of >> the properties for that field to have the corrected column name, it DOES >> accept this change back in the server, but the server DOES NOT update the >> data dictionary with this change. So, a subsequent bounce of ARS will >> restore the old, incorrect column name. >> >> The work-around is to delete the field from the view (cut it), save the >> view, then paste it back in and correct the column name. >> >> This SQL query will reveal any such problems in your system (written for >> SQL Server but probably pretty easily adapted to other DBs): >> >> SELECT s.name, f.fieldName, sv.tableName, vm.extField AS "Bad Column >> Name" >> FROM arschema s >> JOIN field f >> ON f.schemaId = s.schemaId >> AND f.fOption != 4 >> JOIN schema_view sv >> ON sv.schemaId = s.schemaId >> JOIN view_mapping vm >> ON vm.schemaId = s.schemaId >> AND vm.fieldId = f.fieldId >> LEFT OUTER JOIN sys.all_objects t >> ON t.name = SUBSTRING(sv.tableName, 1, LEN(CAST(sv.tableName AS >> NVARCHAR(128))) - 1) >> AND t.type in ('U', 'V') -- User Table or View >> LEFT OUTER JOIN sys.columns c >> ON t.object_id = c.object_id >> AND c.name = vm.extField >> WHERE c.object_id IS NULL >> >> Another interesting and impactful bug related to this (and, in fact, how >> I originally discovered there was a problem with my view form), is that if >> you export the bad view form definition and import it into another server, >> it WILL import with only a warning: >> >> Warning (69): Creation of one of the SQL views for the form failed within >> the SQL database. >> Form is created successfully, but the SQL view is not in place.: <view >> name> >> >> where <view name> here is the name of the courtesy SQL view that Remedy >> creates for each form (e.g. the "Customer" SQL view ARS will create for >> your Customer form). >> >> For view forms, the "T table" itself is a SQL view defined on the >> underlying SQL table (or view), and the courtesy SQL view that ARS then >> creates is simply another SQL view on top of the "T table" view. So it >> would appear from the warning that only this final SQL view creation is >> failing. However, a SQL log I captured during such an import revealed that >> the creation of the "T table" view itself was also failing. But apparently >> this is not being noticed by ARS, because it does go on to create the ARS >> data dictionary stuff for the form (i.e. the form WILL appear in your list >> of forms on that server). However, any attempt to actually use the form >> fails with a cryptic message such as: >> >> The SQL database operation failed. : >> Invalid object name 'T435'. (SQL Server 208) (ARERR 552) >> >> Anyway, sorry for the complex description here and hopefully I haven't >> left anyone interested too confused...let me know if you have any questions. >> >> I don't currently have a client, so I have no active support id through >> which I can submit a ticket on this. So, anyone, feel free to do so if >> this affects you. >> >> Thanks, >> Charlie >> _ARSlist: "Where the Answers Are" and have been for 20 years_ > > > _ARSlist: "Where the Answers Are" and have been for 20 years_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
