Author Topic: Adding Unicode support to existing SQL Server Database  (Read 3517 times)

0 Members and 2 Guests are viewing this topic.

gsamna

  • Member
  • **
  • Posts: 6
    • View Profile
Hi,

having been a user of TP for a while I had switched to MS SQL Server database years ago. I understand that Unicode support would now be possible for filenames. The fact that for me it doesn't work might depend on the fact that the coresponding columd for the name in the database seems to be defined as varchar(255). Could I just redefine this to nvarchar(MAX) in the existing database (without bricking it) to fix the problem or is there another more advisable instruction, script or other?

Thank you all in advance for your help with this.

Gregor

gsamna

  • Member
  • **
  • Posts: 6
    • View Profile
Adding Unicode support to existing SQL Server Database
« Reply #1 on: 2016-03-05 13:13:07 »
Ok, I just did it (no risk no fun, right?).

So far everything seems to be working. I had to go with nvarchar(255) as max would have not allowed to index the field. Nor really an expert so I first thought there was no 255 option as it was not in the drop down list ...haha.

Still interested in hearing from you if here are any caveats I should be aware of.

And if anybody has the same issue just drop me a reply and I will happily post a more detailed description of how I proceeded (without warranty and to be followed at your own risk!!!)

ne small thing I noticed is that the search (Find - Fle name mask) won't work with special characters. However if you cancel it and start it again it substitutes all special characters with ? and then it does find the files. Might be on purpose as if you have varchar in your database instead of varchar that's what TP gets out of the database for the name.

Still have to figure out how to get rid of the orphans that I have in the database from before (other than scanning for orpans, but htat would eliminate all orphans and I have some that are there on purpose and I want to keep).

gsamna

  • Member
  • **
  • Posts: 6
    • View Profile
Adding Unicode support to existing SQL Server Database
« Reply #2 on: 2016-03-06 04:44:30 »
I looked at the most recent database creation script. It looks like all old varchar columns should now be nvarchar. This explains (obviously) the issue I described with find in my previous post.

My problem is that from what I have seen an existing column cannot just be converted without dropping the table and recreating it (which is not a good thing if it is already populated with data).

I had to do it by creating a new column, copying over from the old column, renaiming old to new, deleting the old one and dealing with eventual indexes.

Does anybody have a script to do this??? Maybe Cerious has a script to convert an older database???

tpRox

  • Member
  • **
  • Posts: 3
    • View Profile
Adding Unicode support to existing SQL Server Database
« Reply #3 on: 2017-05-02 18:17:34 »
A year late, but it's a slow day at work.

SELECT
'ALTER TABLE '+TABLE_NAME+' ALTER COLUMN '+COLUMN_NAME+' nvarchar('+RTRIM(CAST(CHARACTER_MAXIMUM_LENGTH as char))+') '+
(CASE WHEN IS_NULLABLE = 'YES' THEN 'null' ELSE 'not null' END) xRunThese
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 1=1
AND DATA_TYPE = 'varchar'

Run this on the database, it won't make any changes to tables or columns, what it will do is generate sql commands you can then copy paste execute and that will change the column data types from varchar to nvarchar while preserving column length and nullable type.

You should have a backup before making any changes like this. You can selectively convert columns by not executing the entire result set. It'd be a good idea to not be trusting, look over the code and understand what it's doing before executing.