Can't run Toolkit report 104001Table Information!

QuickWEQuickWE Member Posts: 80
edited 2015-09-27 in NAV Three Tier
Hi professional,

I don't know if anyone has the experience with following errors when run the report 104001Table Information:

"The following SQL error was unexpected.
The nvarchar data type cannot be selected as DISTINCT because it is not comparable.
Statement(s) could not be prepared."

Please also see the attached picture;

I notice it report called table information but it table disappear when I'd converted database from lower versions to NAV2013 R2. Therefore, my issue might be separated into 2 subjects as:

1. Run it report and found upon error
2. Why table information disappear in NAV database which is i assume it may lose or missing during covert database but
not really sure how that missing.

I would greatly appreciate if one of you help to advise in the solutions to solving it problem.

Kind Regards,
Jai

Comments

  • vremeni4vremeni4 Member Posts: 323
    Hi,

    The report 104001 Table Information is part of the Upgrade toolkit.
    This report uses virtual table Table Information.

    All virtual tables are not shown in the object designer.
    One way to see all virtual tables :
    1. Start object designer go to Page and click on the "New" button.
    2. In the field Table drill down, when a new window appears scroll all the way down.
    3. you will see there are many virtual tables in the range 2000000028

    Regarding the DISTINCT error message there are two options.
    1. All objects in NAV have to have unique name. If you have a table called XYX in object designer then there must not be another object with the same name.
    2. Second option is that you use old Build of NAV 2013 R2, The lowest Build number should be 36366. Preferably CU 13.
    All build number below 36366 can cause data loss and they have some nasty bugs.
    see here for more details
    http://blogs.msdn.com/b/nav/archive/2014/03/27/table-synchronization-paradigm-in-microsoft-dynamics-nav-2013-r2.aspx

    I hope this helps.
    Thanks.
  • QuickWEQuickWE Member Posts: 80
    vremeni4 wrote:
    Hi,

    The report 104001 Table Information is part of the Upgrade toolkit.
    This report uses virtual table Table Information.

    All virtual tables are not shown in the object designer.
    One way to see all virtual tables :
    1. Start object designer go to Page and click on the "New" button.
    2. In the field Table drill down, when a new window appears scroll all the way down.
    3. you will see there are many virtual tables in the range 2000000028

    Regarding the DISTINCT error message there are two options.
    1. All objects in NAV have to have unique name. If you have a table called XYX in object designer then there must not be another object with the same name.
    2. Second option is that you use old Build of NAV 2013 R2, The lowest Build number should be 36366. Preferably CU 13.
    All build number below 36366 can cause data loss and they have some nasty bugs.
    see here for more details
    http://blogs.msdn.com/b/nav/archive/2014/03/27/table-synchronization-paradigm-in-microsoft-dynamics-nav-2013-r2.aspx

    I hope this helps.
    Thanks.

    Many Many Thanks for your replies I will tried following your suggestion ASAP. :lol:
  • QuickWEQuickWE Member Posts: 80
    vremeni4 wrote:
    Hi,

    The report 104001 Table Information is part of the Upgrade toolkit.
    This report uses virtual table Table Information.

    All virtual tables are not shown in the object designer.
    One way to see all virtual tables :
    1. Start object designer go to Page and click on the "New" button.
    2. In the field Table drill down, when a new window appears scroll all the way down.
    3. you will see there are many virtual tables in the range 2000000028

    Regarding the DISTINCT error message there are two options.
    1. All objects in NAV have to have unique name. If you have a table called XYX in object designer then there must not be another object with the same name.
    2. Second option is that you use old Build of NAV 2013 R2, The lowest Build number should be 36366. Preferably CU 13.
    All build number below 36366 can cause data loss and they have some nasty bugs.
    see here for more details
    http://blogs.msdn.com/b/nav/archive/2014/03/27/table-synchronization-paradigm-in-microsoft-dynamics-nav-2013-r2.aspx

    I hope this helps.
    Thanks.
    Unfortunately, My build is 37563 it 's high enough. And By the way I can run this report on the CRONUS Database but at Customer Database (With Same objects) it cannot be run this report.
  • vremeni4vremeni4 Member Posts: 323
    Hi,

    Is it possible that this database where you get the error message was upgrade from another version?
    If this is the case did you run SyncNAV-Tenant command.

    Thanks.
  • QuickWEQuickWE Member Posts: 80
    vremeni4 wrote:
    Hi,

    Is it possible that this database where you get the error message was upgrade from another version?
    If this is the case did you run SyncNAV-Tenant command.

    Thanks.
    Thanks for your answer I will tried to do that. :P
  • QuickWEQuickWE Member Posts: 80
    vremeni4 wrote:
    Hi,

    Is it possible that this database where you get the error message was upgrade from another version?
    If this is the case did you run SyncNAV-Tenant command.

    Thanks.

    I already SyncNAV-Tenant but the error message does still occurs. :cry::cry:
  • vremeni4vremeni4 Member Posts: 323
    Hi,

    One more thing that you can try.
    Export all tables and import them again.
    If that does not help, the only way is to take a backup of database and to restore it as a new database.

    Thanks,.
  • GreatScott000GreatScott000 Member Posts: 40
    Has anyone found a solution to this problem? I have converted my DB to build 42610 and still have the problem. I can't delete the table objects and reimport them because this is client data upgrade.
Sign In or Register to comment.