Friday, October 10, 2014

SQL Database, Table or Field names get changed automatically with the use of special characters

Already there are enough number of blog posts available for this topic, however today I came across a similar scenario and though to write a blog post about it. 

If you have opened NAV DB using SQL server, you might have noticed few special characters used in NAV database has been replaced in SQL side. 

Let's see which characters get changed,
  • In Microsoft Dynamics NAV 2009 : ."\/'
  • In Microsoft Dynamics NAV 2013 & 2013 R2 : ."\/'%][ 
You can check this character list from 2 different ways. 
1. Using Dynamics NAV development environment
2. Using SQL query

Dynamics NAV

Follow the following steps to get this character list from Microsoft Dynamics NAV side, 
1. Open Dynamics NAV Development Environment
2. Open the Database
3. Navigate to File Menu 
4. Go to Database Menu
5. Then click "Alter..."

This will open up a new window (Refer below image)


6. In that window go to "Integration" tab. 

In there you will see the special characters that will get replaced in the SQL side (Convert Identifiers). 

SQL Query

Follow the following steps to get this character list from SQL side, 
1. Open SQL Server Management Studio
2. Log to Server
3. Open a new query window and write this following query
     USE DBNAME
     GO
     SELECT * FROM [DBNAME].dbo.[$ndo$dbproperty]
4. Execute the query and result will look like follow.



** Do not change this values unless you really wants to and make sure you know exactly what you are messing up with. 

Thank you and Regards,
Tharanga Chandrasekara.

1 comment:



  1. IN NAV 2009 as well as NAV 2013, Convert Identifiers is Editable while altering database. In NAV2009, if I try to add % in this field, all fields with % get replaced with character underscore(_) in SQL Server.
    In NAV 2013 also, this field is editable. In NAV2013, If I try to remove % character from this field, it does not reflect changes in SQL Server field name. % character does not reflect back in field names. This happens successfully in NAV 2009 after taking database to single user mode.
    Can anyone help on this?

    ReplyDelete