Filter on Dynamics NAV 2009 SP1 SQL Server

Arseni
Arseni Member Posts: 9
edited 2011-05-30 in SQL General
Hello to everyone,

We have updated a database from 3.70 (native) to Dynamics NAV 2009 SP1 on SQL Server 2008 SP1.
We have the new database with the Latin1_General_100_CS_AS Collation. If we filter in the table 7012 - Purchase Price, field "Item No." with the filter "S*" to show all the records that start with S, the sytem returns all the records that start with S. If we move the cursor with the up & down arrows the system goes through the records and all seems ok. However if you press CTRL + HOME or CTRL + End (to go to the first or last record) all the records disappear (like the filter does not return any records).

This behavior is only on SQL 2008 SP1 (I've checked in two different servers) instead of Native or SQL 2005 everything works fine.

I've changed the databse collation to SQL_Latin1_General_CP1_CS_AS and seems to work correctly.
Why it works fine with the same collation in SQL 2005 and SQL 2008 does not?

Thanks!!
Arseni Hernandez

Comments

  • Imranx101
    Imranx101 Member Posts: 21
    Go to purchase price table in sql server and check if there is any null value in item no. field or some thing.
  • Arseni
    Arseni Member Posts: 9
    I'm going to check it.

    But, why it works fine with the same collation that in SQL 2005 and with the collation of SQL 2008 does not?
    Arseni Hernandez
  • lzr
    lzr Member Posts: 264
    Same problem here, SQL 2008 R2 and NAV 2009 SP1. We also have issues filtering on S* in reports (gives no result what so ever). We are using Finnish, Swedish collation. Very strange
    Navision developer

Categories