We're hoping to upgrade shortly to v14, and we have a very, very large corpus of custom objects in our database, so my primary concern with the upgrade is if anything in our customizations is going to not be compatible with SQL Server 2014.
I know this happened during the last SQL Server upgrade for us: a large number of reports, agent jobs and the like caused all sorts of curious havoc, and it came down to 2008 not allowing implicit truncation of strings.
We are on a brutally tight upgrade schedule, so I was hoping to hear from any early adopters if they'd run across any big differences between SQL versions such as that example.
Thanks,
Gawain
New Issue. This issue is particular to being a RAMP user, to be sure, but could affect anyone who has made the same assumptions I have.
Background: most tables in Tessitura have a set of "audit" columns, like this:
create_loc varchar(16),
created_by varchar(8),
create_dt datetime,
last_update_by varchar(8),
last_update_dt datetime
I have duplicated this in all of my custom tables, of which there are maybe 50?
When these tables are updated by one of my stored procedures I make sure to modify the appropriate rows, using a few built in SQL functions: user_name() for created_by and last_update_by, host_name() for create_loc, and CURRENT_TIMESTAMP for the dates.
Generally when run in Tessitura you would get your userid (e.g. 'gawainl'), since those are synonymous with the sql user associated with the account. If run by an administrative account, you would get 'dbo'. So you can generally tell when something in Tessitura was created or update by a procedure, as the columns will show 'dbo' instead of a userid.
But in updating to v14, RAMP is also tightening up security on the admin accounts handed out to licensees (wise), and not giving them full access. However now I'm getting a different user name back for user_name(): "KRIOSTESS\calpuser18".
But the space for a user name in all those tables is still varchar(8).
This behavior is all over the place in my code. Most of the SQL Server Agent Jobs I've attempted have failed on account of this. Pretty much any procedure is going to fail when run outside of a Tessitura Client. Updating any custom System Table outside of the System Tables pane in Tessitura will fail, since they all use triggers to update those columns. I'm kind of astonished that anything works, since all customer account updates fire about 7 custom stored procedures. Maybe those show the userid of the logged in user of the client.
Anyway, the solution is simple enough, just labor intensive, so I thought I’d put out a warning.
Gawain Lavers said:But the space for a user name in all those tables is still varchar(8).
An embarrassing, Windows-95-era limitation. "Sorry, new user, your Tess username can't match your AD username because... ¯\_(ツ)_/¯"
Ugh: so I was looking to replace instances of user_name() (as per above), but it looks like in some places I used the built in USER, which is going to be a lot harder to search for.
Which brings up: at some point I was looking to organize my SQL code into a solution, but was dissuaded, I think with the claim that solutions or projects were being dropped by SSMS. Am I delusional? Looking back into it, they seem to be alive and well.
Gawain Lavers said:at some point I was looking to organize my SQL code into a solution, but was dissuaded, I think with the claim that solutions or projects were being dropped by SSMS.
That would be sad for me, since I just started using them. :-)
I had grand plans at first, but ultimately my most-used SSMS solution is a "stuff I have open all the time" solution, a nice labor-saver for reboots/SSMS restarts.