Renaming a database

I've set my database to single user mode,and run:
sp_rename 'oldname' 'newname'
which works just fine.
Now I want to rename the oldname_data.mdf to
newname_data.mdf
To do that, I'm trying to detach the database, then go to
Explorer, rename the file and then reattach the database.
sp_detach_db 'newname'
returns an error:
Server: Msg 3702, Level 16, State 1, Line 1
Cannot drop the database 'newname' because it is currently
in use.
EXEC SQL DISCONNECT newname <or> 'newname'
returns an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'newname'.
How can I determine what has the database "in use" and
break that connection? (I'm on a 7.0 box in this case)
TIA MikeFirst open a query analyzer and connect to the SQL Server
in question. Then run this command:
SP_HOW2
search in the result and find any spid that currently
connected to the database and use this command ti kill
those users:
KILL <spid number>
That sould resolve your problem.
This posting is provided "AS IS" with no warranties, and
confers no rights.
http://www.microsoft.com/info/cpyright.htm
>--Original Message--
>I've set my database to single user mode,and run:
>sp_rename 'oldname' 'newname'
>which works just fine.
>Now I want to rename the oldname_data.mdf to
>newname_data.mdf
>To do that, I'm trying to detach the database, then go to
>Explorer, rename the file and then reattach the database.
>sp_detach_db 'newname'
>returns an error:
>Server: Msg 3702, Level 16, State 1, Line 1
>Cannot drop the database 'newname' because it is
currently
>in use.
>EXEC SQL DISCONNECT newname <or> 'newname'
>returns an error:
>Server: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'newname'.
>How can I determine what has the database "in use" and
>break that connection? (I'm on a 7.0 box in this case)
>TIA Mike
>.
>|||I guess I'm not the only one with typo's today:) I think
that should be SP_WHO2
Vern
>--Original Message--
>First open a query analyzer and connect to the SQL Server
>in question. Then run this command:
>SP_HOW2
>search in the result and find any spid that currently
>connected to the database and use this command ti kill
>those users:
>KILL <spid number>
>That sould resolve your problem.
>This posting is provided "AS IS" with no warranties, and
>confers no rights.
>http://www.microsoft.com/info/cpyright.htm
>
>>--Original Message--
>>I've set my database to single user mode,and run:
>>sp_rename 'oldname' 'newname'
>>which works just fine.
>>Now I want to rename the oldname_data.mdf to
>>newname_data.mdf
>>To do that, I'm trying to detach the database, then go
to
>>Explorer, rename the file and then reattach the database.
>>sp_detach_db 'newname'
>>returns an error:
>>Server: Msg 3702, Level 16, State 1, Line 1
>>Cannot drop the database 'newname' because it is
>currently
>>in use.
>>EXEC SQL DISCONNECT newname <or> 'newname'
>>returns an error:
>>Server: Msg 170, Level 15, State 1, Line 1
>>Line 1: Incorrect syntax near 'newname'.
>>How can I determine what has the database "in use" and
>>break that connection? (I'm on a 7.0 box in this case)
>>TIA Mike
>>.
>.
>|||That is correct ;-) it is SP_WHO2.
Thanks for correction.
This posting is provided "AS IS" with no warranties, and
confers no rights.
http://www.microsoft.com/info/cpyright.htm
>--Original Message--
>I guess I'm not the only one with typo's today:) I think
>that should be SP_WHO2
>Vern
>>--Original Message--
>>First open a query analyzer and connect to the SQL
Server
>>in question. Then run this command:
>>SP_HOW2
>>search in the result and find any spid that currently
>>connected to the database and use this command ti kill
>>those users:
>>KILL <spid number>
>>That sould resolve your problem.
>>This posting is provided "AS IS" with no warranties, and
>>confers no rights.
>>http://www.microsoft.com/info/cpyright.htm
>>
>>--Original Message--
>>I've set my database to single user mode,and run:
>>sp_rename 'oldname' 'newname'
>>which works just fine.
>>Now I want to rename the oldname_data.mdf to
>>newname_data.mdf
>>To do that, I'm trying to detach the database, then go
>to
>>Explorer, rename the file and then reattach the
database.
>>sp_detach_db 'newname'
>>returns an error:
>>Server: Msg 3702, Level 16, State 1, Line 1
>>Cannot drop the database 'newname' because it is
>>currently
>>in use.
>>EXEC SQL DISCONNECT newname <or> 'newname'
>>returns an error:
>>Server: Msg 170, Level 15, State 1, Line 1
>>Line 1: Incorrect syntax near 'newname'.
>>How can I determine what has the database "in use" and
>>break that connection? (I'm on a 7.0 box in this case)
>>TIA Mike
>>.
>>.
>.
>

Rename existing SQL 2K to new name

Can an existing instance of SQL 2K be renamed from "Local Machine" to
"NewName"? There is only one SQL Server 2K installed on this machine. It can
be reinstalled if necessary since there is no databases added yet. Thank you
for any help you may be able to give me.
RGPHI,
No need of reinstalling, you can use the below stored procedures to rename
SQL server.
sp_dropserver <servername>
go
sp_addserver <newservername>,local
Thanks
Hari
MCDBA
"riley" <riley3@.microtek-sales.com> wrote in message
news:O4DxZbQwDHA.2340@.TK2MSFTNGP12.phx.gbl...
> Can an existing instance of SQL 2K be renamed from "Local Machine" to
> "NewName"? There is only one SQL Server 2K installed on this machine. It
can
> be reinstalled if necessary since there is no databases added yet. Thank
you
> for any help you may be able to give me.
> RGP
>|||Thank you very much for the response. It has been very helpful.
Riley
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OHpMYfRwDHA.1740@.TK2MSFTNGP12.phx.gbl...
> HI,
> No need of reinstalling, you can use the below stored procedures to rename
> SQL server.
> sp_dropserver <servername>
> go
> sp_addserver <newservername>,local
> Thanks
> Hari
> MCDBA
> "riley" <riley3@.microtek-sales.com> wrote in message
> news:O4DxZbQwDHA.2340@.TK2MSFTNGP12.phx.gbl...
> > Can an existing instance of SQL 2K be renamed from "Local Machine" to
> > "NewName"? There is only one SQL Server 2K installed on this machine. It
> can
> > be reinstalled if necessary since there is no databases added yet. Thank
> you
> > for any help you may be able to give me.
> > RGP
> >
> >
>

Rename a column

Is it safe to use DBCC RENAMECOLUMN(@.table, @.oldname, @.newname)
or is better to drop the column and recreate it?
Thanks in advance?DBCC RENAMECOLUMN is undocumented so the answer is, no, it is not
"safe".
In SQL Server 7.0 and 2000 use sp_rename to rename a column. No need to
drop and re-create it.
David Portas
SQL Server MVP
--|||Since that's undocumented, why not try sp_rename:
exec sp_rename 'tbl.ColOldName', 'ColNewName', 'column'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"x-rays" <xrays@.discussions.microsoft.com> wrote in message
news:F190B789-8C8F-4FFE-8581-AE2641792F75@.microsoft.com...
> Is it safe to use DBCC RENAMECOLUMN(@.table, @.oldname, @.newname)
> or is better to drop the column and recreate it?
> Thanks in advance?|||Hi
DBCC RENAMECOLUMN is not documented, so don't use it.
Rather use sp_rename.
Regards
Mike
"x-rays" wrote:

> Is it safe to use DBCC RENAMECOLUMN(@.table, @.oldname, @.newname)
> or is better to drop the column and recreate it?
> Thanks in advance?|||Yeah, sure I can use sp_rename, thank you all replying.
"x-rays" wrote:

> Is it safe to use DBCC RENAMECOLUMN(@.table, @.oldname, @.newname)
> or is better to drop the column and recreate it?
> Thanks in advance?|||You should be very careful when renaming columns as you could break existing
views, stored procedures and user defined functions if any of these objects
use the column.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"x-rays" wrote:
> Yeah, sure I can use sp_rename, thank you all replying.
> "x-rays" wrote:
>