distal-attribute
distal-attribute
distal-attribute
distal-attribute

MS Sql Update Field Names

User, date Message
Written by sbaer
2 months ago
Category: Running SQL scripts
3 posts since Sat, 10 May 14
I tried to update the column name for a field in a table in SQL Server. I received the following error.

SQL Error (4902): Cannot find the object "dbo.state" because it does not exist or you do not have permissions.

The SQL code generated was this:

ALTER TABLE "dbo"."state" ALTER COLUMN "circuit_court1" VARCHAR(255) NULL;

I saw a recommendation to use the sp_rename for changing a field name in SQL Server.

EXEC sp_RENAME 'table_name.old_field_name','new_field_name','COLUMN'

That command worked. Wanted to let you know in case there was a away to implement that into your program.
Written by ansgar
2 months ago
4936 posts since Fri, 07 Apr 06
I was working on exactly that situation some time ago. The "EXEC sp_rename" approach was not working on all MSSQL versions. But when you tell me which version you have then probably I can make a version conditional.
Written by sbaer
2 months ago
3 posts since Sat, 10 May 14
I was using this on SQL Server 2012 Developer Edition. The blog I found it on said they were using SQL Server 2008 R2, but I don't have that setup for me to test it.
Written by sbaer
2 months ago
3 posts since Sat, 10 May 14
Going through the "Other Versions" on this page:
http://technet.microsoft.com/en-us/library/ms188351(v=sql.105).aspx

it seems like the sp_rename should work on versions from 2005 up.
Written by ansgar
2 months ago
4936 posts since Fri, 07 Apr 06
Ok, then we probably need an alternative for MSSQL 2000, if the current implementation does not do it.
 

Please login to leave a reply, or register at first.