Hi Christopher,
Since 7.0 (one of the patches) there has been a “viewname” column in arschema. This should be used in SQL scripts. The algorithm to truncate a form name to a view name is quite simple and is available as a sample script (AR Table Report) in the Meta-Update Learning, Script Library section of www.softwaretoolhouse.com. You can download the script and read it for the algorithm of you’re interested. That script assumes 7.1+ has the viewname column and uses that algorithm for server < 7.1. Thanks goes to Mark Walters for help with that algorithm especially for other databases. This is nothing new and not related to all the troubles you’ve been having with 7.6.04. It is simply dependent on the order of population of arschema. The first table encountered (being created) that is unique in the 30 characters of its name gets the table’s view name truncated from its table name (with character xlates) and every subsequent table that has its name matched in the first 30 characters gets truncated a bit more (depending on the length of the schema id) and gets its schema id as part of its view name That is why since the addition of the viewname field, it is always better to base your scripts on the value in that fields. Cheers Ben Chernys Senior Software Architect Software Tool House Inc. Canada / Deutschland / Germany Mobile: +49 171 380 2329 GMT + 1 + [ DST ] Email: <mailto:[email protected]> Ben.Chernys _AT_ softwaretoolhouse.com Web: <http://www.softwaretoolhouse.com> www.softwaretoolhouse.com Check out Software Tool House's free Diary Editor. Meta-Update, our premium ARS Data tool, lets you automate your imports, migrations, in no time at all, without programming, without staging forms, without merge workflow. <http://www.softwaretoolhouse.com/> http://www.softwaretoolhouse.com/ From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of strauss Sent: April-29-11 23:37 To: [email protected] Subject: Re: More FUN with 7.6.04 Form Overlays Ah, part of the problem is that the viewName for any schema with a name or (resolvedName) over 30 characters gets truncated and appended with differentiating characters, primarily where the first 25 or so characters are NOT UNIQUE between multiple forms. The trouble is that each server creates different viewNames, so… on one server, upgraded from 7.1/2.1/7.0 to 7.6.04: select count(*) as "BMC.CORE.CONFIG:BMC_ConfigBaseElement" from BMC_CORE_CONFIG_BMC_ConfigBase select count(*) as "BMC.CORE.CONFIG:BMC_ConfigBaseRelationship" from BMC_CORE_CONFIG_BMC_ConfigB159 and on another, clean install of 7.6.04: select count(*) as "BMC.CORE.CONFIG:BMC_ConfigBaseElement" from BMC_CORE_CONFIG_BMC_ ConfigB456 select count(*) as "BMC.CORE.CONFIG:BMC_ConfigBaseRelationship" from BMC_CORE_CONFIG_BMC_ConfigBase Yes, they are reversed; beware! The same viewName on different server may point at completely a different form from within the set of those with common name elements. You will have to edit some of the sql scripts in \BMC Software\Migrator\migrator\DeltaDataMigration\Utilities to fit EVERY server that you use them on – in my case there were differences in AR_7604_Forms_Count.sql, Atrium_7604_Forms_Count.sql, and RKM_7604_Forms_Count.sql. Why am I this far down in the weeds on a Friday afternoon??? Christopher Strauss, Ph.D. Call Tracking Administration Manager University of North Texas Computing & IT Center http://itsm.unt.edu/ From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of strauss Sent: Friday, April 29, 2011 11:48 AM To: [email protected] Subject: More FUN with 7.6.04 Form Overlays Here’s one for you. I was poking around in the Delta Data Migration directories this morning, and found the sql scripts for counting records in forms for each of the applications. Some of them have more errors than correct lines, but that can be fixed; maybe the form names are instantiated differently on other SQL systems (maybe they were created against oracle?), but they were dead wrong for SQL Server 2008 (many form names too long and therefore unrecognized). Also, a lot of the form names were invalid because they were missing underscores – I’m not sure how anyone could have compiled these scripts without ever testing them. If this is what the DDM uses to run queries, there is no chance of it ever working correctly. Anyway, I tried to run a select statement in SQL for a custom integer field on my HPD:Help Desk form to see if it has negative number entries that need to be reset to “0” (see DDM doc pg 19), and found that I could not select that field because it exists only in the overlay. In fact, you cannot select ANY of your custom fields from HPD_Help_Desk, and there is no HPD_Help_Desk__o in the form list, so I wonder where you can go in SQL to query the overlays. HPD:Help Desk__o exists in arschema table with its own schemaId, but it has no T or H tables etc., the custom field columns are part of the T table for HPD:Help Desk itself. Select Outage_Duration from HPD_Help_Desk WHERE Outage_Duration < 0 …is not a valid command – Outage_Duration is not recognized by SQL Server as a valid column in the HPD_Help_Desk table If I try querying the T table directly, that works: Select [C940000006] from dbo.T993 WHERE [C940000006] < 0 So are custom fields in form overlays undefined in the SQL Server namespace?? Christopher Strauss, Ph.D. Call Tracking Administration Manager University of North Texas Computing & IT Center http://itsm.unt.edu/ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
