Hello,
We upgraded to v15.0.6 around May this year. I discovered in error in a seldom-used custom local table that we have to help capture name tag information for special events. The local table as application access through a constituent's custom tab. When either trying to edit or create a new entry for that name tag, I would receive error 8152 (String or binary data would be truncated). This seemed odd as the 4 fields (Customer name + 3 other detail fields) were all given datatypes of varchar(255) or higher.
When looking at the other columns in SSMS, I saw that there were created_by and last_updated_by fields with char(8) datatypes for each. This still seemed odd as all of our users have usernames with 8 character maximums. I decided to alter the columns in TEST as an experiment to see if this was causing the error and, sure enough, it did. However, not in the way I expected.
We are on RAMP, so I thought that since we opted to use the single signon feature in v15 that my RAMP login would populate those fields, but, instead, "tessitura_app" was the username called by the trigger associated with that local table. This was just my guess. Turned out to be correct, but for a different reason.
Has anyone else run into this with custom local tables? I'm wondering if there is a better workaround than altering the columns? Any suggestions are appreciated.
Tony ;})
Hi Tony, we had two or three triggers on tables that were using old functions for inserting/updating the username and location. If your trigger uses user_name() and host_name() replace with FS_USER() and FS_LOCATION() respectively.
One other thing, I noticed some tables didn't have permissions to impusers copied over after our upgrade.
Cheers,
Kevin
Seconded. Though if you are Version 15.1.1, you will need to use FS_USERNAME() instead of FS_USER().
John
Thank you both for that advice. I updated the two triggers and it works fine again. We'll be looking into upgrading from 15.0.6 to whatever the next 15 is by January. I'll set myself a reminder that I'll need to update to FS_USERNAME() once that happens.
It depends on how extensive your custom tables are (and how many use "audit tables"), but this was a big job for me. And don't forget FS_LOCATION if you haven't hit it yet.
If you are RAMP or moving to Windows Authentication, you will likely find FS_USERNAME unsatisfying. It truncates whatever the db username is down to the first eight characters, but that often still leaves the identity unknown or ambiguous. I've built my own version to get around that:
https://community.tessituranetwork.com/tessitura_software_forums/f/tessitura_technical-9/22895/user_name-fs_user-and-fs_username-in-v15
Hi Gawain,
I appreciate you commenting. I found your post almost immediately after posting mine. For now while we're on v15.0.6, the function is pulling in the application username as desired. Although, since we're planning on upgrading to the newest v15 - whatever that is - around the beginning of January, I've bookmarked your post to help us when this inevitably annoys us in the future. Thanks again!
Gawain's argument certainly is quite valid, but I think it might highly depend upon the organization names. We are on RAMP, and FS_USERNAME() is working perfectly wonderfully for us, no issues at all.
Just in case you want to give it a shot before you look to use the customized version.
Definitely. I figured my first test would be with FS_USERNAME() to see if it behaved as expected.
I think (can't remember now) most client activity is successfully filled in using fs_get_param_from_appname from within FS_USERNAME, but my memory is that it isn't 100%. The main things for me were scripted operations, custom procedures, etc. where this context thing wasn't available: I added in an attempt to match the username to any of the AD names in T_METUSER and then returned the userid for those. and used RIGHT(8) instead of SUBSTRING(1,8), since the last eight letters are more likely to be identifying than the first.