Can't save column type change
-
Hi,
I am trying to change a type of a field in a table from 'text' to 'varchar' in SQL Server 2005. When I change in Management studio and click save I am getting an error. But I can change it in SQL Server 2005 Express by taking the same steps. The database is exactly of the 'live' one. I recently backed the database up and restored it in Express locally for development.
I am loged in as an Administrator in both. Anything might cause this?
Help would be greatly appreciated, thank you.
-
If you give us the error you get would helpf us help you 😀
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton -
I am not sure what error you get, but check the max length of the "Text" field and if it's higher than the "varchar" value you are setting it to will throw you an error.
To measure the length of TEXT fields the function is DATALENGTH(text_field). Len will not work for text fields.
---------------------------------------------------------------------------------------
It begins by taking the first step. -
Yes sorry. The error is:
1: When I click after change to Save, the Save box shows "The following tables will be saved to your database. Do you want to continue?" (Lists 4 tables that have referential integrities to it)
2: I click Yes then getting Validation Warnings: "Warnings were encountered during the pre-save..." (Lists one table with Warning: Data might be lost converting column 'colName' from 'text')
3: I click Yes again and this time the same warning but for the table I am trying to change
4: I click Yes again and getting this error: "Errors were encountered during the save process. Some database objects were not saved" (Lists a table that has a referential integrity with message: "tableName table - Unable to delete relationship 'FK_... Timeout expired..."
I have successfully changed the type in my local SQL Server 2005 Express database, which is on machine. Also the database on machine is the same as on the Server (backed it up and restored it locally).
Thank you
-
Is anyone else using the database (the "live" one)?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton -
I have never successfully edited a column type through the GUI..
Just do it via T-SQL
ALTER TABLE tablename
ALTER COLUMN columname VARCHAR(MAX)
-
Can you do it in the GUI and rather than apply script out the T-SQL and post it on here along with an overview of the schema of the table you wish to change and the tables being referenced.
Hope this helps,
Rich[p]
[/p] -
The "timout expired" might be a problem with SSMS. If you are doing it from your machine (not on the physical server) you can adjust the timeout to a longer time. Default is set to 30 seconds I believe.
Go to SSMS > Tools > Options > Click on "Designers".
There should be a setting for "Transaction time-out after:" change that to maybe 60 seconds and see what it does.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton -
Thanks guys for your replies.
How about the point 4 on "Unable to delete relationship FK..." Do I have to delete the relationship and then change it? But it worked with the same relationship on SQL Server Express (locally)?
Yes, there were quite few people using 'live'. Do you think that might cause the error?
-
Thank you all for your help. It's been sorted. The problem was with other users still logged in.
Thanks again
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply