Rename database

I have set the database to single user mode and want to rename the database.
However, it shows the following message:
To change the NAME, the database must be in state in which a checkpoint can
be executed.
I have restart the sql server service but it still shows the above message.
What should I do?
Best Regards,
IvanHi Ivan
I get a different error if I am not connected to the single user database in
SQL 2005, but I can rename a database in single user mode if I am connected
to it or if no-one else is connected to it.
I assume you are using SQL 2000? Is anyone else connected to the database?
John
"Ivan" wrote:
> I have set the database to single user mode and want to rename the database.
> However, it shows the following message:
> To change the NAME, the database must be in state in which a checkpoint can
> be executed.
> I have restart the sql server service but it still shows the above message.
> What should I do?
> Best Regards,
> Ivan
>
>|||In SQL2k with single user I can rename database easily.
Please check other settings also.
Regards
Amish Shah|||The database is in Single User mode and I have restart the SQL Server 2000
to ensure no one is connect to the database.
However, it still shows that error message.
Ivan
"amish" <shahamishm@.gmail.com>
'?:1147525165.494414.185770@.j33g2000cwa.googlegroups.com...
> In SQL2k with single user I can rename database easily.
> Please check other settings also.
> Regards
> Amish Shah
>|||Is the database READ_ONLY? It must be writable before it can be renamed.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <ivan@.microsoft.com> wrote in message
news:eg3ONfrdGHA.3484@.TK2MSFTNGP04.phx.gbl...
> The database is in Single User mode and I have restart the SQL Server 2000
> to ensure no one is connect to the database.
> However, it still shows that error message.
> Ivan
> "amish" <shahamishm@.gmail.com>
> '?:1147525165.494414.185770@.j33g2000cwa.googlegroups.com...
>> In SQL2k with single user I can rename database easily.
>> Please check other settings also.
>> Regards
>> Amish Shah
>|||Hi
If you make the database the current database and then use
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
you should not need to reboot the server.
If you reboot the server and someone connects to the database before you
then you will have a problem.
John
"Ivan" wrote:
> The database is in Single User mode and I have restart the SQL Server 2000
> to ensure no one is connect to the database.
> However, it still shows that error message.
> Ivan
> "amish" <shahamishm@.gmail.com>
> '?:1147525165.494414.185770@.j33g2000cwa.googlegroups.com...
> > In SQL2k with single user I can rename database easily.
> > Please check other settings also.
> >
> > Regards
> > Amish Shah
> >
>
>sql

rename column with check constraints

I want to change the name of a column. I tried to use sp_rename but i get the message:
"Object 'table2.TelNoMob' cannot be renamed because the object participates in enforced dependencies."

I think this is because the specific column has check constraint.

Is there a way of changing the column's name?You will have to drop the constraint and add it it again afterwards.

This will give you the constraint name to drop

select object_name(constid) from sysconstraints where id = object_id('tblname')
and colid = (select colid from syscolumns where id = object_id('tblname') and name = 'colname')

removing old logins

Using SS2000. I'm removing old logins. When I tried to remove one, I receive
d this message "removing tis login will remove all associated database users
(if any)."
What does this mean?
Thanks,
--
Dan D.Hi,
This error will come when the user has been allocated permissions to some
other databases. So you have to
drop the user associated to that login from all the databases and then drop
the login.
Command to get the databases user is mapped
sp_helplogins <logn_name>
Then use have to go to each database and drop the user
use <dbname>
go
sp_dropuser <user_name>
Once you drop the user from all the databases you could drop the login..
sp_droplogin <login_name>
Thanks
Hari
MCDBA
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:DC4A9B5E-2EB5-40E6-9BD1-F4A9AC51E63D@.microsoft.com...
> Using SS2000. I'm removing old logins. When I tried to remove one, I
received this message "removing tis login will remove all associated
database users (if any)."
> What does this mean?
> Thanks,
> --
> Dan D.|||I understand. Makes perfect sense.
Thanks,
--
Dan D.
"Hari Prasad" wrote:

> Hi,
> This error will come when the user has been allocated permissions to some
> other databases. So you have to
> drop the user associated to that login from all the databases and then dro
p
> the login.
> Command to get the databases user is mapped
> sp_helplogins <logn_name>
> Then use have to go to each database and drop the user
> use <dbname>
> go
> sp_dropuser <user_name>
> Once you drop the user from all the databases you could drop the login..
> sp_droplogin <login_name>
> Thanks
> Hari
> MCDBA
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:DC4A9B5E-2EB5-40E6-9BD1-F4A9AC51E63D@.microsoft.com...
> received this message "removing tis login will remove all associated
> database users (if any)."
>
>

removing old logins

Using SS2000. I'm removing old logins. When I tried to remove one, I received this message "removing tis login will remove all associated database users (if any)."
What does this mean?
Thanks,
Dan D.
Hi,
This error will come when the user has been allocated permissions to some
other databases. So you have to
drop the user associated to that login from all the databases and then drop
the login.
Command to get the databases user is mapped
sp_helplogins <logn_name>
Then use have to go to each database and drop the user
use <dbname>
go
sp_dropuser <user_name>
Once you drop the user from all the databases you could drop the login..
sp_droplogin <login_name>
Thanks
Hari
MCDBA
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:DC4A9B5E-2EB5-40E6-9BD1-F4A9AC51E63D@.microsoft.com...
> Using SS2000. I'm removing old logins. When I tried to remove one, I
received this message "removing tis login will remove all associated
database users (if any)."
> What does this mean?
> Thanks,
> --
> Dan D.
|||I understand. Makes perfect sense.
Thanks,
Dan D.
"Hari Prasad" wrote:

> Hi,
> This error will come when the user has been allocated permissions to some
> other databases. So you have to
> drop the user associated to that login from all the databases and then drop
> the login.
> Command to get the databases user is mapped
> sp_helplogins <logn_name>
> Then use have to go to each database and drop the user
> use <dbname>
> go
> sp_dropuser <user_name>
> Once you drop the user from all the databases you could drop the login..
> sp_droplogin <login_name>
> Thanks
> Hari
> MCDBA
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:DC4A9B5E-2EB5-40E6-9BD1-F4A9AC51E63D@.microsoft.com...
> received this message "removing tis login will remove all associated
> database users (if any)."
>
>

removing old logins

Using SS2000. I'm removing old logins. When I tried to remove one, I received this message "removing tis login will remove all associated database users (if any)."
What does this mean?
Thanks,
--
Dan D.Hi,
This error will come when the user has been allocated permissions to some
other databases. So you have to
drop the user associated to that login from all the databases and then drop
the login.
Command to get the databases user is mapped
sp_helplogins <logn_name>
Then use have to go to each database and drop the user
use <dbname>
go
sp_dropuser <user_name>
Once you drop the user from all the databases you could drop the login..
sp_droplogin <login_name>
Thanks
Hari
MCDBA
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:DC4A9B5E-2EB5-40E6-9BD1-F4A9AC51E63D@.microsoft.com...
> Using SS2000. I'm removing old logins. When I tried to remove one, I
received this message "removing tis login will remove all associated
database users (if any)."
> What does this mean?
> Thanks,
> --
> Dan D.