Navision 3.7 and SQL Collation issues

Styvie

Good day all,

I have a weird issue with a Navision 3.7 SQL database. We have about 8 companies, i.e. 8 tables per named table in Navision.

I would like to create a view for an external application which has all the data from all the companies in it, i.e.

Select No_,Name , 'Company 1' as Company from [Company 1$Resource]
UNION ALL
Select No_,Name , 'Company 2' as Company from [Company 2$Resource]

I have many such views to do....

That would work fine, except that I now find that some of my tables fields have different collations ?

For instance company 1 has collation Latin1_General_CS_AS and another has collation Latin1_General_CI_AS

There are so many examples in this database of this happening, and I am confused as to how this happens. I am 100% convinced that this has not been done manually, and it must have happened when the companies were created etc. Possibly the DB Collation was changed in between these 2 companies being created.

Has anyone else experienced this ? And is there any risk of this disrupting code from within Navision.. we are running W1 , English , with only normal characters (i.e. no accents, etc). Would like to do a full run through the db and make all the collations standard, but not 100% sure this is risk free either...

Any comments ?

kine

Try to alter the collation for the database within Navision, may be that it will alter all collation on all tables to one value.

Styvie

Yeah, am going to try that.... just worried it may not finish overnight... the db is about 150gb, so just worried it takes too long... was going to do it with a script... bit by bit....

Going to try on a test copy i have of the db...

Will post my results...

ara3n

The collation is defined per database. How can the tables in the same db have differnt colation.

Styvie

You can have differing collation per colums in SQL 2000... so DB collation can differ to column collation...

DB collation is used as a default collation for the column, but i have cased where columns are differing collations..

ara3n

were they changed manually? I don't think Navision has any capability to change them.

Styvie

I somehow doubt they were changed manually. I think possibly someone changed the DB Collation via Enterprise Manager.. which does not update all the columsn collations as navision does when changing a collation.

So ... all companies tables created before this collation was changed have one collation, and any companies / tables created since then have a different collation. Navision creates new tables using the default database collation, and Enterprise Manager, does not re-update all the old data.

I have since tested, changing the collation of the database through Navision, will change the collation of each column in the database. This allows me to get back to one standard collation, but will take time, as each column needs to be resorted, and re-indexed.

Hopefully once done I will be back to one collation for my db.

ara3n

Good luck. Thanks for the info.