reforest
asked on
ALTER COLUMN WITH COMPRESSION not working
Hi all experienced programmers,
I have created a table in code that has a text column, but I realized later that I wanted it to have Unicode Compression set to yes. I have users that are already running my program, and therefore they already have this column created in their table. I tried to alter the table (which contains data) for my next release using the following code:
with MyQuery do
begin
Close;
SQL.Clear;
SQL.Add('ALTER TABLE MyTable ');
SQL.Add('ALTER COLUMN Name CHAR(25) WITH COMPRESSION');
ExecSQL;
end;
However, the Unicode Compression for the Name column is still set to NO. What else should I try?
I also don't know how to set 'Allow Zero Length' to NO.
Your help is much appreciated.
I have created a table in code that has a text column, but I realized later that I wanted it to have Unicode Compression set to yes. I have users that are already running my program, and therefore they already have this column created in their table. I tried to alter the table (which contains data) for my next release using the following code:
with MyQuery do
begin
Close;
SQL.Clear;
SQL.Add('ALTER TABLE MyTable ');
SQL.Add('ALTER COLUMN Name CHAR(25) WITH COMPRESSION');
ExecSQL;
end;
However, the Unicode Compression for the Name column is still set to NO. What else should I try?
I also don't know how to set 'Allow Zero Length' to NO.
Your help is much appreciated.
ASKER
Yes MS Access.
I have tried using 'WITH COMPRESSION' when I first create the table and it works. Unfortunately, I already have users with the table created already so I have to alter it.
I have tried using 'WITH COMPRESSION' when I first create the table and it works. Unfortunately, I already have users with the table created already so I have to alter it.
ASKER
Thanks. I checked a few more things and this is definitely working on an empty table but not on a table which contains data.
If this is just something that won't work, maybe someone can suggest a workaround (how to copy the data from my existing table to a new table with the correct properties then delete the old table and rename the new one)?
If this is just something that won't work, maybe someone can suggest a workaround (how to copy the data from my existing table to a new table with the correct properties then delete the old table and rename the new one)?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the advice on the workaround. Too bad there's no easy way.
I see that "WITH COMPRESSION" for the first time, and I don't remember having seen it for ms sql server...