I am having trouble renaming a table in SQL Server 2000, using ASP.
Here is the code
---------------------------------------------------------
<html>
<body>
<!--#include file="Open_Conn.asp"-->
<!--#include file="adovbs.inc"-->
<%
Dim sSQL
sSQL = "ALTER TABLE test1_Answers RENAME TO test2_Answers;"
cn.Execute sSQL
sSQL = "ALTER TABLE test1_Grades RENAME TO test2_Grades;"
cn.Execute sSQL
%>
</body>
</html>
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Incorrect syntax near the keyword 'TO'.
/create_table.asp, line 11
-----------------------------------------------------------
I've tried everything, i'm wondering of you need something like:
cn.Execute sSQL, 2, 2
But, I haven't found anything to work yet. Has anyone every renamed a table using SQL Statements. Copying to a new table and deleting the old I can't figure out either.isn't it just
sSQL = "ALTER TABLE test1_Answers RENAME test2_Answers;"
no TO
or
RENAME TABLE tbl_name TO new_tbl_nameNot working
really, this works for me
ALTER TABLE table_name RENAME table_name2sp_rename
Changes the name of a user-created object (for example, table, column, or user-defined data type) in the current database.
Syntax
sp_rename [ @objname = ] 'object_name' ,
[ @newname = ] 'new_name'
[ , [ @objtype = ] 'object_type' ]
Arguments
[@objname =] 'object_name'
Is the current name of the user object (table, view, column, stored procedure, trigger, default, database, object, or rule) or data type. If the object to be renamed is a column in a table, object_name must be in the form table.column. If the object to be renamed is an index, object_name must be in the form table.index. object_name is nvarchar(776), with no default.
[@newname =] 'new_name'
Is the new name for the specified object. new_name must be a one-part name and must follow the rules for identifiers. newname is sysname, with no default.
[@objtype =] 'object_type'
Is the type of object being renamed. object_type is varchar(13), with a default of NULL, and can be one of these values.
Value Description
COLUMN A column to be renamed.
DATABASE A user-defined database. This option is required when renaming a database.
INDEX A user-defined index.
OBJECT An item of a type tracked in sysobjects. For example, OBJECT could be used to rename objects including constraints (CHECK, FOREIGN KEY, PRIMARY/UNIQUE KEY), user tables, views, stored procedures, triggers, and rules.
USERDATATYPE A user-defined data type added by executing sp_addtype.
Return Code Values
0 (success) or a nonzero number (failure)
Remarks
You can change the name of an object or data type in the current database only. The names of most system data types and system objects cannot be changed.
When you rename a view, information about the view is updated in the sysobjects table. When you rename a stored procedure, information about the procedure is changed in the sysobjects table.
sp_rename automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY KEY constraint, the primary key is also automatically renamed by sp_rename.
Important After renaming stored procedures and views, flush the procedure cache to ensure all dependent stored procedures and views are recompiled.
Stored procedures and views can be dropped and re-created quickly because neither object stores data. For best results renaming textual objects, drop and re-create the object by its new name.
Permissions
Members of the sysadmin fixed server role, the db_owner and db_ddladmin fixed database roles, or the owner of the object can execute sp_rename. Only members of the sysadmin and dbcreator fixed server roles can execute sp_rename with 'database' as the object_type.
Examples
A. Rename a table
This example renames the customers table to custs.
EXEC sp_rename 'customers', 'custs'
B. Rename a column
This example renames the contact title column in the customers table to title.
EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'
Here is the code
---------------------------------------------------------
<html>
<body>
<!--#include file="Open_Conn.asp"-->
<!--#include file="adovbs.inc"-->
<%
Dim sSQL
sSQL = "ALTER TABLE test1_Answers RENAME TO test2_Answers;"
cn.Execute sSQL
sSQL = "ALTER TABLE test1_Grades RENAME TO test2_Grades;"
cn.Execute sSQL
%>
</body>
</html>
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Incorrect syntax near the keyword 'TO'.
/create_table.asp, line 11
-----------------------------------------------------------
I've tried everything, i'm wondering of you need something like:
cn.Execute sSQL, 2, 2
But, I haven't found anything to work yet. Has anyone every renamed a table using SQL Statements. Copying to a new table and deleting the old I can't figure out either.isn't it just
sSQL = "ALTER TABLE test1_Answers RENAME test2_Answers;"
no TO
or
RENAME TABLE tbl_name TO new_tbl_nameNot working
really, this works for meALTER TABLE table_name RENAME table_name2sp_rename
Changes the name of a user-created object (for example, table, column, or user-defined data type) in the current database.
Syntax
sp_rename [ @objname = ] 'object_name' ,
[ @newname = ] 'new_name'
[ , [ @objtype = ] 'object_type' ]
Arguments
[@objname =] 'object_name'
Is the current name of the user object (table, view, column, stored procedure, trigger, default, database, object, or rule) or data type. If the object to be renamed is a column in a table, object_name must be in the form table.column. If the object to be renamed is an index, object_name must be in the form table.index. object_name is nvarchar(776), with no default.
[@newname =] 'new_name'
Is the new name for the specified object. new_name must be a one-part name and must follow the rules for identifiers. newname is sysname, with no default.
[@objtype =] 'object_type'
Is the type of object being renamed. object_type is varchar(13), with a default of NULL, and can be one of these values.
Value Description
COLUMN A column to be renamed.
DATABASE A user-defined database. This option is required when renaming a database.
INDEX A user-defined index.
OBJECT An item of a type tracked in sysobjects. For example, OBJECT could be used to rename objects including constraints (CHECK, FOREIGN KEY, PRIMARY/UNIQUE KEY), user tables, views, stored procedures, triggers, and rules.
USERDATATYPE A user-defined data type added by executing sp_addtype.
Return Code Values
0 (success) or a nonzero number (failure)
Remarks
You can change the name of an object or data type in the current database only. The names of most system data types and system objects cannot be changed.
When you rename a view, information about the view is updated in the sysobjects table. When you rename a stored procedure, information about the procedure is changed in the sysobjects table.
sp_rename automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY KEY constraint, the primary key is also automatically renamed by sp_rename.
Important After renaming stored procedures and views, flush the procedure cache to ensure all dependent stored procedures and views are recompiled.
Stored procedures and views can be dropped and re-created quickly because neither object stores data. For best results renaming textual objects, drop and re-create the object by its new name.
Permissions
Members of the sysadmin fixed server role, the db_owner and db_ddladmin fixed database roles, or the owner of the object can execute sp_rename. Only members of the sysadmin and dbcreator fixed server roles can execute sp_rename with 'database' as the object_type.
Examples
A. Rename a table
This example renames the customers table to custs.
EXEC sp_rename 'customers', 'custs'
B. Rename a column
This example renames the contact title column in the customers table to title.
EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'