Everyone, Just a couple of comments on this thread (including lots of comments that have come in over the past few hours).
First, it is not a surprise to us that there are differences in lengths in the DB and in the Remedy length under some conditions. This is actually a designed behavior of the system (which I will explain). There are other conditions under where a difference is benign -- but not expected. Finally, there may be conditions where a difference is NOT benign and is definitely not expected. First, the majority of cases, the field length in the DB will match the field length in the AR System definition. Now, in the case of mismatches.... For those cases where the DB length is GREATER than the length in the AR System definition, this is a situation that is expected and as designed. It occurs when you have a character field that has a maximum length such that a varchar or nvarchar or equivalent type in the database you are using is created (generally 4000 to 8000 bytes in the currently supported databases) and you make a change to the AR System definition to make it SHORTER. In this case, we shorten the length definition at the AR System level but do not restructure the database. Several reasons for this. One, the database stores varchar and nvarchar (and the like) types using only the space you need. So, it does not take the full length definition. This means that the same amount of space is used in the database for both lengths -- it is about the data content. Two, it saves a DB restructure operation from being performed to restructure the column to match the new length. So, the end result is a more efficient operation with no downside from the perspective of data storage. It also has a side effect of not truncating data for cases where customers have changed the length, realized that it is not correct, and then made it bigger again. NOTE: If you make the length LONGER -- even by 1 byte -- from what the AR System thinks it currently is (regardless of what the DB length actually is as that length may be longer from a previous shortening of the length), we DO cause a DB restructure and will change the length of the DB column to match the AR System definition. So, for systems that have been around a while and have lots of different upgrades and customizations that have been performed, you may end up with quite a few cases where the AR System length is shorter than the DB length. All interaction with the system and create/modify/merge operations will enforce the AR System length for all new records. The second scenario is join forms. We do have field limit definitions for join fields, but many of the characteristics are from the underlying field. This includes the field type and length and other things. But, in the metadata tables, we copy the definition of the underlying fields into the join definition when we create the join. I suspect you are seeing the field length difference on join fields as an issue where the underlying field has changed length after the join was created. That value is NOT used in the metadata for the join field -- yes, it is there, but it is not used. The actual value is the value on the underlying field definition not on the field at the join level. Now, this is the benign difference. It is different but it doesn't matter as the value has no meaning in the join. Sure, the system should be propogating that change as a "documentation" change in the metadata, but as the metadata are internal tables for internal to the system use, it probably was not thought to be worth the time and energy to propagate a piece of information that didn't matter. It does not cause any runtime or definition time issue. It will be visible only to someone trying to deep dive and interpret the metadata tables directly. Finally, we come to the situation where if you have it is definitely NOT expected and can cause odd errors in the system (although it should not cause crashes or hangs but it can definitely cause odd SQL errors). If you have a situation where the AR System length is LONGER than the DB length for a field of type char/varchar/nvarchar and the field is a regular field and not a join or a view form, this is not as expected. This would be an inconsistency that we cannot account for and where there could be issues generating odd SQL errors during runtime. Again, I would expect that to be the worst result, not a crash or hang but just odd errors when something is longer than the DB length but not than the AR length (and some DBs would just quietly truncate the value to the DB length). I hope this helps to clarify what you should see in the metadata tables if you do start playing around with our internal definitions. One situation that is fully as expected (shorter AR System length that DB length). One situation where the value doesn't really matter and is not really used (AR System length different than underlying table length when the field is on a join form -- look at the length of the actual field that the join field points to as that is the real length). And, unclear whether any example shows this case, One situation that is an issue if it occurs where the DB length is SHORTER than the AR System length. Hopefully, this explanation helps clarify what you are seeing and offers an explanation about why it is all OK -- unless that is you are seeing the third situation and then we should take a look at how that could have happened on your system (and a quick workaround to simply increase the length of the field by 1 byte which forces the AR System to change the length to that new length and then to reduce the length by 1 byte to get back your original restriction (and the DB length will be 1 more than your AR System definition). Doug Mueller -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of LJ LongWing Sent: Thursday, February 23, 2012 9:20 AM To: [email protected] Subject: Not Matching Lengths List, Our Dev server started crashing a few days ago and we had a VERY hard time trying to identify the cause. After several days of trial and error, one of the developers on the team noticed that a field length in the DB was not the same as a field length in Remedy. After further troubleshooting it was found that the server stopped responding after doing a query/insert into that table using that field ID....the only 'fix' I could find to get them back in sync was to delete the field and re-create it. We haven't had the server crash since I did that....so it got me thinking that there might be other fields with similar problems, so I started looking...I was shocked by what I found and want to run it by some other people on the list to see if they come across similar results. The Query I have provided below is running just fine on SQL Server 2008 R2...I don't know the equivalent for Oracle...but I would like to know if other people find a large amount of columns where the two values don't match. With the below Query I came up with 382 mismatches, many of which are on system generated forms, but a majority of which are on my custom forms. This is an OLD system...it's been around since at least 6.0...and upgraded and modified in almost every major revision since its inception. We are currently on 7.6.4 SP1....and I can confirm with a form that I created yesterday...I created a 255 character field...then shrunk it to 30, and it is on my list of mismatches....curious about your results...please reply back :) select a.name, f.fieldName, fc.maxLength as 'RemedyMaxLength', c.CHARACTER_MAXIMUM_LENGTH as 'DBMaxLength' from arschema a inner join field f on f.schemaId = a.schemaId inner join field_char fc on a.schemaId = fc.schemaId and f.fieldId = fc.fieldId inner join INFORMATION_SCHEMA.COLUMNS c on c.TABLE_NAME = 'T'+CAST(a.schemaId AS char) AND 'C'+CAST(fc.fieldId as char) = c.column_name where fc.maxLength != c.CHARACTER_MAXIMUM_LENGTH AND c.CHARACTER_MAXIMUM_LENGTH != 2147483647 and f.fieldId != 1 Name Field Name RemedyMaxLength DBMaxLength AR System Currency Label Catalog Short Description 4 254 AR System Currency Ratios Short Description 4 254 Alert Events UNUSED 5 254 Server Events Unused_Descr 3 254 Server Statistics Short Description 3 254 User Password 30 255 User Computed Grp List 255 4000 AR System Administrator Preference Short Description 128 254 AR System User Central File Short Description 128 254 AR System User Preference Short Description 128 254 ReportSelection OriginalFormServer 128 254 ReportType Short Description 128 254 FB:Alarm Events Monitor ID 15 254 FB:History Short Description 128 254 _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
