I upsized from Access to SQL Server 7. One table had a max width of 255 (Access limits for a text field). I want...
to increase this to 500. Can you help me with the SQL to change the max width to 500, without dropping any data. I use a database manager on Brinkster, and do not have enterprise manager.
Even without Enterprise Manager, you can still do it if you have some way to submit plain SQL.
My first attempt would be --
alter table theTable alter column theTextfield varchar(500)
Now, not every database system is going to allow you to alter column definitions that way -- most support ALTER TABLE but only some support ALTER COLUMN. I apologize for not knowing whether SQL/Server 7 does. I suppose I could go look in the Microsoft documentation, but first I'd have to find it, then I'd have to download it, and then I'd be too tired...
So if the above approach does not work, try this --
select thePK , convert(varchar(500),theTextField) as theTextField into copyTable from theTable
drop table theTable
select * into theTable from copyTable
drop table copyTable
The SELECT INTO syntax is often useful for making temporary tables for reporting, creating quick backups of tables before running mass updates, et cetera, or, as in this case, applying a modification to the table's structure.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.