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_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
