[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 536
  • Last Modified:

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.
0
reforest
Asked:
reforest
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is this ms access?
I see that "WITH COMPRESSION" for the first time, and I don't remember having seen it for ms sql server...
0
 
reforestAuthor Commented:
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.
0
 
reforestAuthor Commented:
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)?
0
 
Geert GruwezOracle dbaCommented:
add a new column to the table with compression
copy the data in the old column to the new column
delete the old column
rename the new column to the old column

-> ye old workaround routine
0
 
reforestAuthor Commented:
Thanks for the advice on the workaround. Too bad there's no easy way.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now