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