Calling sp_rename on a table kills database connection in Sybase

Question

I'm trying to rename a table using the following syntax

sp_rename [oldname],[newname]

but any time I run this, I get the following [using Aqua Datastudio]:

 Command was executed successfully

 Warnings: ---> 
   W (1): The SQL Server is terminating this process.
          <--- 
 [Executed: 16/08/10 11:11:10 AM] [Execution: 359ms] 

Then the connection is dropped (can't do anything else in the current query analyser (unique spid for each window))

Do I need to be using master when I run these commands, or am I doing something else wrong?

Answer 1

You shouldn't be getting the behaviour you're seeing. It should either raise an error (e.g. If you don't have permission) or work successfully. I suspect something is going wrong under the covers.

Have you checked the errorlog for the ASE server? Typically these sorts of problems (connections being forcibly closed) will be accompanied by an entry in the errorlog with a little bit more information.

The error log will be on the host that runs the ASE server, and will probably be in the same location that ASE is installed into. Something like /opt/sybase/ASE-12_5/install/errorlog_MYSERVER

Answer 2

try to avoid using "sp_rename". Because some references in system tables remain like old name. Someday this may cause some faulties if you forget this change.

I suggest;

select * into table_backup from [tableRecent]
go
select * into [tableNew] from table_backup
go
drop table [tableRecent] -- in case of backup you may not drop that table
go
drop table table_backup -- in case of backup you may not drop that table
go

• to achieve that; your database has an option "select into/bulkcopy/pllsort"
• if your ata is huge, check your free space on that database.

and enjoy :)