NAV 2013 R2 Case Sensitive

vbtrek

I can't understand why Microsoft are distributing a Case Sensitive version of the CRONUS database with NAV 2013 R2.

As I understand it the only way to create a new database is to start with the cronus database which has all the objects you need then create a new company etc., but, this still leaves you with a case sensitive database.

Is it possible to get a case insensitive cronus database?

kine

What about changing the collation after that?

vbtrek

I have unticked the Case-sensitive option in NAV Developer Environment in File -> Database -> Alter -> Collation tab, but this has not changed the collation of the database (not entirely sure what it has done).

You can't change the collation in SQL Server because you get hundreds of errors like:
The object 'CRONUS UK Ltd_$Job Planning Line$VSIFT$6' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

kine

Have you started the RTC after the change? Or have you run the Sync powershell command? Do not forget that the schema changes are done when RTC connect or when the sync is started manually. Even the change of collation is working in different way in NAV 2013 and I had some problems in earlier builds with this. There is collation name saved in system (internal) table of the tenant database...

vbtrek

Yep I've run the RTC, but in SQL Server the database remains as "Latin1_General_100_CS_AS" collation.

I'm running GB Dynamics NAV 7.1 (7.1.35473.0)

kine

Try to use newest build. I was solving the problem with support and I needed at least 36035 or something like that. Do not forget that there are critical things fixed in the build before 36344...

vbtrek

Ok, I uninstalled 35473, and installed Update 7 which is 7.1.36703, installed a fresh Cronus database from this install, and the default is still case sensitive.

I ran throught the Developer environment and Altered the database to NOT be case sensitive, then started the RTC, then checked in SQL Server and the database is still set with the Collation of Latin1_General_100_CS_AS.

Much appreciate your help, no idea what I'm doing wrong. We're going to try upgrading a Case Insensitive NAV 6 database to NAV 2013 which I imagine the upgrade won't change the collation, but there must be a way to do this directly in NAV 2013.

davmac1

I think you will need a SQL script to change all the objects.
When all the SQL Server objects are case sensitive, you can change the default setting for the database, but that does not change the individual objects.

Too bad the long awaited return of the backup and restore company has still not happened.

One more reason for going to Directions or NAV Tech days where you can talk to Microsoft directly.

kine

:-) Have you looked into the Update 8? http://blogs.msdn.com/b/nav/archive/201 ... eased.aspx

Import/Export-NAVData...

tinoruijs

kine wrote:

:-) Have you looked into the Update 8? http://blogs.msdn.com/b/nav/archive/201 ... eased.aspx

Import/Export-NAVData...

FBK is back! Kind of.

I noticed there's an comment on this update:
Release note The cmdlet help for the syntax of the Export-NAVData and Import-NAVData are not completely correct.
Please refer to below syntax instead:

So solution is given.

vbtrek

I finally got round to trying this, and it wasn't easy. In case anyone is interested here are the steps that worked for me:

• Export-NAVData -DatabaseServer "<SQLServer>" -DatabaseName "CRONUS" -FileName "CronusBackup.bak" -AllCompanies -IncludeApplication -IncludeGlobalData -IncludeApplicationData
• Create a new DB In SQL Server Collation I chose was Latin1_General_100_CI_AS
• Import-NAVData -DatabaseServer "<SQLServer>" -DatabaseName "<MyNewDB>" -FileName "CronusBackup.bak" -AllCompanies -IncludeApplication -IncludeGlobalData -IncludeApplicationData
• Execute SQL:
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  SET ANSI_PADDING ON
  GO
  CREATE TABLE [dbo].[$ndo$cachesync](
  [id] [int] IDENTITY(1,1) NOT NULL,
  [Server Instance ID ] [int] NOT NULL,
  [modifiedtables] [varbinary](max) NULL,
  [timestamp] [timestamp] NOT NULL,
  CONSTRAINT [PK$ndo$cachesync] PRIMARY KEY CLUSTERED
  (
  [id] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  GO
  SET ANSI_PADDING OFF
  GO
• Point the NAV service to New DB
• Give NETWORK SERVICE permission to the database
• Start the Service
• New-NAVCompany -ServerInstance "DynamicsNAV71" -CompanyName "<MyNewCompany>"
• Remove-NAVCompany -ServerInstance "DynamicsNAV71" -CompanyName "CRONUS UK Ltd."
• Sync-NAVTenant -ServerInstance "DynamicsNAV71"

This should leave you with a new database that is Case Insensitive, and a new company.

philipp1984

It is a little bit off-topic but could you please check it:

I restored a Database using import-navdata as you described. The '$ndo$cachesync' was also missing.

And one other thing was missing in my new db: The SQL-Server Trigger [$ndo$objecttracking] on the 'Object Metadata'-Table. I could use the database normal but when I changed something in the development environment the RTC showed the change only after server restart.

I copied the create script for this trigger via SQL Management Studio from another database and created it in the new db. And now everything works.

Are you missing this trigger also?