0

I am trying to rename the schema for a dozen tables in an old database on SQL Server 2005. I'm using SQL Server Management Studio Express version 9.0. Based on detailed advice found here, as well as several forums on other sites, I've been trying this straightforward command: 

ALTER SCHEMA newschemaname TRANSFER oldschemaname.table1

I consistently get this error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SCHEMA'

It's the same whether I run it directly in the "New Query" window or build it into a stored procedure.

The database's owner is "SA," dating back to when my web host (the physical server's owner) first set up this database years ago. This makes me think there might be a permissions issue, or some obscure mismatch between the owner name and the login I'm using. But if that was true, I'd expect the error to tell me I don't have rights to execute that command.

Is there some obvious syntax error I'm missing, or do I need to delve deeper into the ownership and permissions to get the rights to fix this?

John Meyer
  • 1
  • 2
  • what's the compatibility level of your database? – RBarryYoung Jan 08 '13 at 02:39
  • `ALTER SCHEMA` does work even in compatibility_level=80, so that can't be it. What is the output of `SELECT @@VERSION;`? – Sebastian Meine Jan 08 '13 at 03:00
  • Compatability level is SQL Server 2000 (80). And I was surprised to see that the Select Version command returned SQL Server 2000 - 8.00.2039. I had been led to believe that this had been upgraded to 2005 several years ago. – John Meyer Jan 08 '13 at 16:15
  • Solved! Turns out it was nothing more than me thinking I was working on an SQL Server 2005 box, when in fact I needed to use the command syntax for 2000: sp_changeObjectOwner 'oldschemaname.table1','newschemaname' – John Meyer Jan 08 '13 at 22:48

1 Answers1

0

New Schema name needs to be in brackets otherwise it will fail

Example statement: ALTER SCHEMA [NewSchemaName] TRANSFER dbo.Tracking

Adil B
  • 14,635
  • 11
  • 60
  • 78
Brad M
  • 1