change string length on table in AX -> drop & create table in SQL
sjakalaka
If I change the length of an existing string field on a table in Ax +
perform a synchronise on this table. I would expect Ax to send an an ALTER
TABLE statement to SQL.
Yesterday I tried this on the custTable and found out that Ax does not send
an ALTER TABLE statement. It does the following:
CREATE TABLE "DBO".X77X (with the new length for my string field)
INSERT INTO X77X SELECT all fields FROM CUSTTABLE
DROP TABLE CUSTTABLE
EXECUTE SP_RENAME X77X, CUSTTABLE
+ recreate of the indexes & constraints.
In the end the result is as expected and no data is lost.
But is there any reason why Ax would not send a ALTER TABLE?
I can't think of any reason at the moment ...
Why am I asking this?
We are using the Ax DB to store confidential info (salaries) but also want
to feed a datawarehouse from from the Ax DB. Therefor we've set up individual
rights on SQL level (AOS user can access all tables, other named users have
restricted acces). If we set up individual acces on a table on SQL level, and
Ax drops and recreates the table (as in the case above) the rights that were
set up on the table are lost. Result: procedures to get data from Ax and put
in in the DW fail becaus the user account was no longer granted access to the
table.
I'm talking about Ax 4 SP1 here.
But Ax 3 was the same I think.
Haven't checked this in Ax2009 yet.
So what's the question here:
Why doesn't Ax send a 'simple' ALTER TABLE command?
Amar Nalla [MSFT]
AX does the increase in length using the below scheme because there are
often other database restrictions that might prevent issuing of ALTER
statements. The ALTER statement might fail in the database.
But in Ax 2009, we do allow increasing of string field length without
droping and recreating the table.
Thank you,
Amar Nalla [MSFT]
This posting is provided "AS IS" and confers no rights or warranties.
"sjakalaka" <sjak...@discussions.microsoft.com> wrote in message
news:350E8927-5D32-4FF9...@microsoft.com...