RE: [Axapta-Knowledge-Village] SQL Server - case sensitive/insensitive options

Wed, 02 Nov 2005 05:37:11 -0800

Hi,
thanks for your email.As I understand sql200sever, the new feature is to select collation on database /not only on server/. When I created database fo Axapta I use case insensitive and accent sensitive collation. Whithout this option you can never start ax client.As I'v written ax clinet works fine for several weeks. The problems start when I try to start check/synchronization function. When I create for example the new table in Axapta synchronization works and I can see the new table on sql server propertly.
The message:
"
SELECT column_name, data_type, character_maximum_length, numeric_precision, numeric_scale, is_nullable FROM information_schema.columns WHERE table_name = UPPER('AAA')"
means that I tried to start check/synchronization function on my own table "AAA".
tomas
-----Original Message-----
From: Axapta-Knowledge-Village@yahoogroups.com [mailto:[EMAIL PROTECTED]On Behalf Of Peter Sloth (MBF)
Sent: Tuesday, November 01, 2005 11:20 PM
To: Axapta-Knowledge-Village@yahoogroups.com
Subject: RE: [Axapta-Knowledge-Village] SQL Server - case sensitive/insensitive options

SQL_Latin_General_CP850_BIN is a binary sort and comparison collation, and so not supported by Axapta!
 
The Install Guideline states:

Creating the Database
  • SQL Server 2000 introduces the concept of collations, a collation being a predefined combination of character set and sort order.
  • SQL Server 2000 supports both Windows Collations and SQL Collations. Windows collations are new collations aligned with Windows locales, SQL collations are compatibility collations that result when upgrading from earlier versions of SQL Server.
  • You should select the proper Case Insensitive collation for your language. Axapta is not guaranteed to work with Case Sensitive collations. Case Insensitive collations include the letters CI in their name.
 
This is the important sentence:
 
Case Insensitive collations include the letters CI in their name.
 
So SQL_Latin_General_CP850_BIN  is not CaseInsentitive as it has not the CI abbreviation in the name!
 
That said I think this one problem can be fixed by changing any offending SQL Statement that query information_schema.* to all uppercase as INFORMATION_SCHEMA views are all uppercase named.
 
Searching all Axapta Classes for string ‘information_schema’ reveals examples like:
 

   #define.MSSQLViewSelect     ("select TABLE_SCHEMA from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'tableName'")
 
    #define.OracleViewSelect    ("select table_owner from SYS.ALL_CATALOG where table_type = 'TABLE' and table_name = 'tableName'")
 

which happen to have proper casing – and:
 
            'DECLARE ' +
 
            '    colnames_cursor CURSOR FOR ' +
 
            '    SELECT table_name, column_name, data_type ' +
 
            '    FROM information_schema.columns ' +
 
            '    JOIN sqldictionary ON sqldictionary.sqlname = table_name AND sqldictionary. fieldid = 0 ' +
 
            '    WHERE is_nullable = \'YES\' AND table_schema = USER_NAME() ' +
 
 
 
Which is not properly cased!
 

The offending:
 
SELECT column_name, data_type, character_maximum_length, numeric_precision, numeric_scale, is_nullable FROM information_schema.columns WHERE table_name = UPPER('AAA')
 

I have not been able to find!  I would guess that the offending query searches for database columns that incorrectly allows null ? This was a problem in Axapta 2.1 and so is a redundant check if you have installed any version never than 2.1! Remember to include Axapta Version information !
 
As Axapta is not guaranteed to support CaseSensitive Collations a bug report makes little sense – you could try a feature request for Case Sensitive support.
on behalf of jjens
From: Axapta-Knowledge-Village@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of mbsaxapta
Sent: 1. november 2005 15:27
To: Axapta-Knowledge-Village@yahoogroups.com
Subject: [Axapta-Knowledge-Village] SQL Server - case sensitive/insensitive options

Our customer migrates from SAP to Axapta and several months wants to support both database on the same Sql server. (Windows 2000 server SP4, MS SQL 2000 SP4 enterprise, server collation:   SQL_Latin_General_CP850_BIN)
Therefore I created new Axapta database on customer’s server with database collation CI-AS. Axapta was connected via bmssa login with db_owner privilegies. Everything works well, but when I attempt to import some customizations  - sometimes synchronization doesn’t work correctly. And check/synchronize process from Administration – Periodic – SQL Administration goes down with following messages: “SELECT column_name, data_type, character_maximum_length, numeric_precision, numeric_scale, is_nullable FROM information_schema.columns WHERE table_name = UPPER('AAA')
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'information_schema.columns'.”
I assigned bmssa login as system administrators and via sp_ChangeObjectOwner change object owner from bmssa to dba. I checked owner of information schemas as well. It seems that server still uses case sensitive option.
thanks,
Tomas


__________ Informace od NOD32 1.1270 (20051101) __________

Tato zprava byla proverena antivirovym systemem NOD32.
http://www.nod32.cz


Sharing the knowledge on Axapta.



SPONSORED LINKS
Business finance course Business to business finance Small business finance
Business finance consultant Business finance magazine Business finance schools

YAHOO! GROUPS LINKS



Reply via email to