Dropping table Column in SQL server 6.5

I'm trying to drop a table column in SQL Server 6.5. I used the following
command and got error:
ALTER TABLE tablename
DROP COLUMN columnname
GO
It works in SQL Server 2000 version
Please I need help.
Thanks.
Ebon.Hi,
You cant delete a column in sql 6.5
Only way is :-
1. put the data into a new table (select * into table_backup from
real_table)
2. script the table and dependant objetcs
3. change the table script with out the unwanted column
4. Insert into table from table_backup
5. create dependant objects , indexes..
Thanks
Hari
MCDBA
"Egbon" <vnjowusi@.gosps.com> wrote in message
news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> I'm trying to drop a table column in SQL Server 6.5. I used the following
> command and got error:
> ALTER TABLE tablename
> DROP COLUMN columnname
> GO
> It works in SQL Server 2000 version
> Please I need help.
> Thanks.
> Ebon.
>|||Many thanks! Hari.
Egbon.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OtjNXHFoEHA.2588@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You cant delete a column in sql 6.5
> Only way is :-
> 1. put the data into a new table (select * into table_backup from
> real_table)
> 2. script the table and dependant objetcs
> 3. change the table script with out the unwanted column
> 4. Insert into table from table_backup
> 5. create dependant objects , indexes..
> Thanks
> Hari
> MCDBA
> "Egbon" <vnjowusi@.gosps.com> wrote in message
> news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> > I'm trying to drop a table column in SQL Server 6.5. I used the
following
> > command and got error:
> >
> > ALTER TABLE tablename
> > DROP COLUMN columnname
> > GO
> >
> > It works in SQL Server 2000 version
> > Please I need help.
> >
> > Thanks.
> >
> > Ebon.
> >
> >
>

Dropping Permissions

Does anyone know what is the statement to drop the
following permissions in QA (Not DENY).
There are too many of them to drop from the E.M.
GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE
ON [dbo].[mytable] TO [public]
GO
Thanks.look at the REVOKE statement.
Example: REVOKE SELECT ON Budget_Data TO Mary
"Eric" wrote:
> Does anyone know what is the statement to drop the
> following permissions in QA (Not DENY).
> There are too many of them to drop from the E.M.
>
> GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE
> ON [dbo].[mytable] TO [public]
> GO
> Thanks.
>|||Thanks...........
>--Original Message--
>look at the REVOKE statement.
>Example: REVOKE SELECT ON Budget_Data TO Mary
>
>"Eric" wrote:
>> Does anyone know what is the statement to drop the
>> following permissions in QA (Not DENY).
>> There are too many of them to drop from the E.M.
>>
>> GRANT REFERENCES , SELECT , UPDATE , INSERT ,
DELETE
>> ON [dbo].[mytable] TO [public]
>> GO
>> Thanks.
>.
>

Dropping article on transaction publications.

In 2005 transactional replication, The following procedure worked (without dropping the subscription) when I dropped an article from a replicated database:

Drop article: On Publication Properties, uncheck the article (table, stored procedure or function).

Create a new snapshot.

Synchronize the push subscription.

DROP the article on the Publication and Subscriber databases.

Replication still works!

However, the following article says the subscription needs to be dropped and re-created when an article is dropped from publication: http://msdn2.microsoft.com/en-us/library/ms152493.aspx (Adding Articles to and Dropping Articles from Existing Publications ). For transactional publications, articles can be dropped with no special considerations prior to subscriptions being created. If an article is dropped after one or more subscriptions is created, the subscriptions must be dropped, recreated, and synchronized.

Under what conditions is dropping the subscription and recreating it absolutely necessary? I do not want to include this extra step.

Linda

Hi Linda, the documentation is correct. If you try to drop the artice via TSQL stored proc sp_droparticle, it will raise an error saying you you cannot drop the article because you have existing subscription. What I think BOL should say is that "the subscriptions to the article must be dropped before you can drop the article".

This is what the UI is doing - it will first drop the article from all existing subscriptions (via sp_dropsubscription), then drop the article from the publication and then invalidate the snapshot. So for existing subscriptions, there isn't anything else you need to do, but if you add a new subscription afterwards, you have to regenerate a new snapshot.

I hope this is a little more clear.

Droppin rule in SQL Server 2000

I am trying to drop a rule but I am getting the following error message:
"The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one or
more column".
I tried to run sp_unbindrule but it gave me this error message: The data
type "RULE_NAME" does not exist.
How can I resolve this? Thank you in advanceA rule can be bound to either a particular column or to a user defined
datatype. What is the name of the rule? Also what is the name the table and
the column?
Can you post the syntax you were using?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
>I am trying to drop a rule but I am getting the following error message:
> "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one or
> more column".
> I tried to run sp_unbindrule but it gave me this error message: The data
> type "RULE_NAME" does not exist.
> How can I resolve this? Thank you in advance|||Thanks for your response Kalen.
I could only get some info using sp_help stored procedure. It says owner is
dbo and type = rule.
It is using the table tblEmpType. I am using the syntax as
DROP RULE [dbo].[Rule_Sim_App]
I forgot to mention this in my previous post. When I check in EM -->
Database --> Rules tab, I do not see any rule created by this name
Rule_Sim_App.
Please advise.
"Kalen Delaney" wrote:

> A rule can be bound to either a particular column or to a user defined
> datatype. What is the name of the rule? Also what is the name the table an
d
> the column?
> Can you post the syntax you were using?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
>
>|||What syntax are you using to try to unbind the rule
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...[vbcol=seagreen]
> Thanks for your response Kalen.
> I could only get some info using sp_help stored procedure. It says owner
> is
> dbo and type = rule.
> It is using the table tblEmpType. I am using the syntax as
> DROP RULE [dbo].[Rule_Sim_App]
> I forgot to mention this in my previous post. When I check in EM -->
> Database --> Rules tab, I do not see any rule created by this name
> Rule_Sim_App.
> Please advise.
> "Kalen Delaney" wrote:
>|||sp_unbindrule 'Rule_Sim_App' and it gives me this error message: The
data type "RULE_NAME" does not exist.
Thanks again
----
"Kalen Delaney" wrote:

> What syntax are you using to try to unbind the rule
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...
>
>|||Hi David
Please check the syntax in BOL for sp_unbindrule. The parameter is what you
are unbinding from, either a user defined type or a 'tablename.columnname'.
Since there can only be one rule bound to anything, there is no ambiguity.
The message is indicating that it thinks Rule_Sim_App is a type name, since
it is not in the format of a table and column.
You need to find the name of the table and the column the rule is bound to,
and then use them in the sp_unbindrule statement.
This query might give you a start:
select name as col_name, object_name(id) as table_name from syscolumns
where domain = object_id ('Rule_Sim_App')
Once you have the table and column, you can run sp_unbindrule:
exec sp_unbindrule 'tablename.columnname'
Again, you do not use the name of the rule when calling sp_unbindrule.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:3A5A21B4-D7BD-48C2-A695-61A70BD3D04E@.microsoft.com...[vbcol=seagreen]
> sp_unbindrule 'Rule_Sim_App' and it gives me this error message: The
> data type "RULE_NAME" does not exist.
> Thanks again
> ----
> "Kalen Delaney" wrote:
>|||Bull's eye Kalen. I really appreciate it.
I have asked the developer to use CHECK constraint instead of using bind
rule objects. Is check constraint easy to administer than bind rule objects?
Thanks again ...
"Kalen Delaney" wrote:

> Hi David
> Please check the syntax in BOL for sp_unbindrule. The parameter is what yo
u
> are unbinding from, either a user defined type or a 'tablename.columnname'
.
> Since there can only be one rule bound to anything, there is no ambiguity.
> The message is indicating that it thinks Rule_Sim_App is a type name, sinc
e
> it is not in the format of a table and column.
> You need to find the name of the table and the column the rule is bound to
,
> and then use them in the sp_unbindrule statement.
> This query might give you a start:
> select name as col_name, object_name(id) as table_name from syscolumns
> where domain = object_id ('Rule_Sim_App')
> Once you have the table and column, you can run sp_unbindrule:
> exec sp_unbindrule 'tablename.columnname'
> Again, you do not use the name of the rule when calling sp_unbindrule.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:3A5A21B4-D7BD-48C2-A695-61A70BD3D04E@.microsoft.com...
>
>|||Good idea. Bound rules will be going away in a future version.
Check constraints may be easier to keep track of since they are attached to
a single table.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:65F55A05-C4DC-4E08-803A-FE527C4506A4@.microsoft.com...[vbcol=seagreen]
> Bull's eye Kalen. I really appreciate it.
> I have asked the developer to use CHECK constraint instead of using bind
> rule objects. Is check constraint easy to administer than bind rule
> objects?
> Thanks again ...
>
> "Kalen Delaney" wrote:
>

Droppin rule in SQL Server 2000

I am trying to drop a rule but I am getting the following error message:
"The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one or
more column".
I tried to run sp_unbindrule but it gave me this error message: The data
type "RULE_NAME" does not exist.
How can I resolve this? Thank you in advanceA rule can be bound to either a particular column or to a user defined
datatype. What is the name of the rule? Also what is the name the table and
the column?
Can you post the syntax you were using?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
>I am trying to drop a rule but I am getting the following error message:
> "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one or
> more column".
> I tried to run sp_unbindrule but it gave me this error message: The data
> type "RULE_NAME" does not exist.
> How can I resolve this? Thank you in advance|||Thanks for your response Kalen.
I could only get some info using sp_help stored procedure. It says owner is
dbo and type = rule.
It is using the table tblEmpType. I am using the syntax as
DROP RULE [dbo].[Rule_Sim_App]
I forgot to mention this in my previous post. When I check in EM -->
Database --> Rules tab, I do not see any rule created by this name
Rule_Sim_App.
Please advise.
"Kalen Delaney" wrote:
> A rule can be bound to either a particular column or to a user defined
> datatype. What is the name of the rule? Also what is the name the table and
> the column?
> Can you post the syntax you were using?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
> >I am trying to drop a rule but I am getting the following error message:
> >
> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one or
> > more column".
> >
> > I tried to run sp_unbindrule but it gave me this error message: The data
> > type "RULE_NAME" does not exist.
> >
> > How can I resolve this? Thank you in advance
>
>|||What syntax are you using to try to unbind the rule
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...
> Thanks for your response Kalen.
> I could only get some info using sp_help stored procedure. It says owner
> is
> dbo and type = rule.
> It is using the table tblEmpType. I am using the syntax as
> DROP RULE [dbo].[Rule_Sim_App]
> I forgot to mention this in my previous post. When I check in EM -->
> Database --> Rules tab, I do not see any rule created by this name
> Rule_Sim_App.
> Please advise.
> "Kalen Delaney" wrote:
>> A rule can be bound to either a particular column or to a user defined
>> datatype. What is the name of the rule? Also what is the name the table
>> and
>> the column?
>> Can you post the syntax you were using?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
>> >I am trying to drop a rule but I am getting the following error message:
>> >
>> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one
>> > or
>> > more column".
>> >
>> > I tried to run sp_unbindrule but it gave me this error message: The
>> > data
>> > type "RULE_NAME" does not exist.
>> >
>> > How can I resolve this? Thank you in advance
>>|||sp_unbindrule 'Rule_Sim_App' and it gives me this error message: The
data type "RULE_NAME" does not exist.
Thanks again
----
"Kalen Delaney" wrote:
> What syntax are you using to try to unbind the rule
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...
> > Thanks for your response Kalen.
> >
> > I could only get some info using sp_help stored procedure. It says owner
> > is
> > dbo and type = rule.
> >
> > It is using the table tblEmpType. I am using the syntax as
> > DROP RULE [dbo].[Rule_Sim_App]
> >
> > I forgot to mention this in my previous post. When I check in EM -->
> > Database --> Rules tab, I do not see any rule created by this name
> > Rule_Sim_App.
> >
> > Please advise.
> >
> > "Kalen Delaney" wrote:
> >
> >> A rule can be bound to either a particular column or to a user defined
> >> datatype. What is the name of the rule? Also what is the name the table
> >> and
> >> the column?
> >> Can you post the syntax you were using?
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.solidqualitylearning.com
> >>
> >>
> >> "David" <David@.discussions.microsoft.com> wrote in message
> >> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
> >> >I am trying to drop a rule but I am getting the following error message:
> >> >
> >> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one
> >> > or
> >> > more column".
> >> >
> >> > I tried to run sp_unbindrule but it gave me this error message: The
> >> > data
> >> > type "RULE_NAME" does not exist.
> >> >
> >> > How can I resolve this? Thank you in advance
> >>
> >>
> >>
>
>|||Hi David
Please check the syntax in BOL for sp_unbindrule. The parameter is what you
are unbinding from, either a user defined type or a 'tablename.columnname'.
Since there can only be one rule bound to anything, there is no ambiguity.
The message is indicating that it thinks Rule_Sim_App is a type name, since
it is not in the format of a table and column.
You need to find the name of the table and the column the rule is bound to,
and then use them in the sp_unbindrule statement.
This query might give you a start:
select name as col_name, object_name(id) as table_name from syscolumns
where domain = object_id ('Rule_Sim_App')
Once you have the table and column, you can run sp_unbindrule:
exec sp_unbindrule 'tablename.columnname'
Again, you do not use the name of the rule when calling sp_unbindrule.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:3A5A21B4-D7BD-48C2-A695-61A70BD3D04E@.microsoft.com...
> sp_unbindrule 'Rule_Sim_App' and it gives me this error message: The
> data type "RULE_NAME" does not exist.
> Thanks again
> ----
> "Kalen Delaney" wrote:
>> What syntax are you using to try to unbind the rule
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...
>> > Thanks for your response Kalen.
>> >
>> > I could only get some info using sp_help stored procedure. It says
>> > owner
>> > is
>> > dbo and type = rule.
>> >
>> > It is using the table tblEmpType. I am using the syntax as
>> > DROP RULE [dbo].[Rule_Sim_App]
>> >
>> > I forgot to mention this in my previous post. When I check in EM -->
>> > Database --> Rules tab, I do not see any rule created by this name
>> > Rule_Sim_App.
>> >
>> > Please advise.
>> >
>> > "Kalen Delaney" wrote:
>> >
>> >> A rule can be bound to either a particular column or to a user defined
>> >> datatype. What is the name of the rule? Also what is the name the
>> >> table
>> >> and
>> >> the column?
>> >> Can you post the syntax you were using?
>> >>
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >> www.solidqualitylearning.com
>> >>
>> >>
>> >> "David" <David@.discussions.microsoft.com> wrote in message
>> >> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
>> >> >I am trying to drop a rule but I am getting the following error
>> >> >message:
>> >> >
>> >> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to
>> >> > one
>> >> > or
>> >> > more column".
>> >> >
>> >> > I tried to run sp_unbindrule but it gave me this error message: The
>> >> > data
>> >> > type "RULE_NAME" does not exist.
>> >> >
>> >> > How can I resolve this? Thank you in advance
>> >>
>> >>
>> >>
>>|||Bull's eye Kalen. I really appreciate it.
I have asked the developer to use CHECK constraint instead of using bind
rule objects. Is check constraint easy to administer than bind rule objects?
Thanks again ...
"Kalen Delaney" wrote:
> Hi David
> Please check the syntax in BOL for sp_unbindrule. The parameter is what you
> are unbinding from, either a user defined type or a 'tablename.columnname'.
> Since there can only be one rule bound to anything, there is no ambiguity.
> The message is indicating that it thinks Rule_Sim_App is a type name, since
> it is not in the format of a table and column.
> You need to find the name of the table and the column the rule is bound to,
> and then use them in the sp_unbindrule statement.
> This query might give you a start:
> select name as col_name, object_name(id) as table_name from syscolumns
> where domain = object_id ('Rule_Sim_App')
> Once you have the table and column, you can run sp_unbindrule:
> exec sp_unbindrule 'tablename.columnname'
> Again, you do not use the name of the rule when calling sp_unbindrule.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:3A5A21B4-D7BD-48C2-A695-61A70BD3D04E@.microsoft.com...
> > sp_unbindrule 'Rule_Sim_App' and it gives me this error message: The
> > data type "RULE_NAME" does not exist.
> >
> > Thanks again
> > ----
> >
> > "Kalen Delaney" wrote:
> >
> >> What syntax are you using to try to unbind the rule
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.solidqualitylearning.com
> >>
> >>
> >> "David" <David@.discussions.microsoft.com> wrote in message
> >> news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...
> >> > Thanks for your response Kalen.
> >> >
> >> > I could only get some info using sp_help stored procedure. It says
> >> > owner
> >> > is
> >> > dbo and type = rule.
> >> >
> >> > It is using the table tblEmpType. I am using the syntax as
> >> > DROP RULE [dbo].[Rule_Sim_App]
> >> >
> >> > I forgot to mention this in my previous post. When I check in EM -->
> >> > Database --> Rules tab, I do not see any rule created by this name
> >> > Rule_Sim_App.
> >> >
> >> > Please advise.
> >> >
> >> > "Kalen Delaney" wrote:
> >> >
> >> >> A rule can be bound to either a particular column or to a user defined
> >> >> datatype. What is the name of the rule? Also what is the name the
> >> >> table
> >> >> and
> >> >> the column?
> >> >> Can you post the syntax you were using?
> >> >>
> >> >> --
> >> >> HTH
> >> >> Kalen Delaney, SQL Server MVP
> >> >> www.solidqualitylearning.com
> >> >>
> >> >>
> >> >> "David" <David@.discussions.microsoft.com> wrote in message
> >> >> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
> >> >> >I am trying to drop a rule but I am getting the following error
> >> >> >message:
> >> >> >
> >> >> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to
> >> >> > one
> >> >> > or
> >> >> > more column".
> >> >> >
> >> >> > I tried to run sp_unbindrule but it gave me this error message: The
> >> >> > data
> >> >> > type "RULE_NAME" does not exist.
> >> >> >
> >> >> > How can I resolve this? Thank you in advance
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Good idea. Bound rules will be going away in a future version.
Check constraints may be easier to keep track of since they are attached to
a single table.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:65F55A05-C4DC-4E08-803A-FE527C4506A4@.microsoft.com...
> Bull's eye Kalen. I really appreciate it.
> I have asked the developer to use CHECK constraint instead of using bind
> rule objects. Is check constraint easy to administer than bind rule
> objects?
> Thanks again ...
>
> "Kalen Delaney" wrote:
>> Hi David
>> Please check the syntax in BOL for sp_unbindrule. The parameter is what
>> you
>> are unbinding from, either a user defined type or a
>> 'tablename.columnname'.
>> Since there can only be one rule bound to anything, there is no
>> ambiguity.
>> The message is indicating that it thinks Rule_Sim_App is a type name,
>> since
>> it is not in the format of a table and column.
>> You need to find the name of the table and the column the rule is bound
>> to,
>> and then use them in the sp_unbindrule statement.
>> This query might give you a start:
>> select name as col_name, object_name(id) as table_name from syscolumns
>> where domain = object_id ('Rule_Sim_App')
>> Once you have the table and column, you can run sp_unbindrule:
>> exec sp_unbindrule 'tablename.columnname'
>> Again, you do not use the name of the rule when calling sp_unbindrule.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:3A5A21B4-D7BD-48C2-A695-61A70BD3D04E@.microsoft.com...
>> > sp_unbindrule 'Rule_Sim_App' and it gives me this error message: The
>> > data type "RULE_NAME" does not exist.
>> >
>> > Thanks again
>> > ----
>> >
>> > "Kalen Delaney" wrote:
>> >
>> >> What syntax are you using to try to unbind the rule
>> >>
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >> www.solidqualitylearning.com
>> >>
>> >>
>> >> "David" <David@.discussions.microsoft.com> wrote in message
>> >> news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...
>> >> > Thanks for your response Kalen.
>> >> >
>> >> > I could only get some info using sp_help stored procedure. It says
>> >> > owner
>> >> > is
>> >> > dbo and type = rule.
>> >> >
>> >> > It is using the table tblEmpType. I am using the syntax as
>> >> > DROP RULE [dbo].[Rule_Sim_App]
>> >> >
>> >> > I forgot to mention this in my previous post. When I check in EM -->
>> >> > Database --> Rules tab, I do not see any rule created by this name
>> >> > Rule_Sim_App.
>> >> >
>> >> > Please advise.
>> >> >
>> >> > "Kalen Delaney" wrote:
>> >> >
>> >> >> A rule can be bound to either a particular column or to a user
>> >> >> defined
>> >> >> datatype. What is the name of the rule? Also what is the name the
>> >> >> table
>> >> >> and
>> >> >> the column?
>> >> >> Can you post the syntax you were using?
>> >> >>
>> >> >> --
>> >> >> HTH
>> >> >> Kalen Delaney, SQL Server MVP
>> >> >> www.solidqualitylearning.com
>> >> >>
>> >> >>
>> >> >> "David" <David@.discussions.microsoft.com> wrote in message
>> >> >> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
>> >> >> >I am trying to drop a rule but I am getting the following error
>> >> >> >message:
>> >> >> >
>> >> >> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound
>> >> >> > to
>> >> >> > one
>> >> >> > or
>> >> >> > more column".
>> >> >> >
>> >> >> > I tried to run sp_unbindrule but it gave me this error message:
>> >> >> > The
>> >> >> > data
>> >> >> > type "RULE_NAME" does not exist.
>> >> >> >
>> >> >> > How can I resolve this? Thank you in advance
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>

Drop User and schema

How do I determine the schema own by a user, remove them and drop the user?
I am trying to drop a user and I get the following error message:
The database principal owns a schema in the database, and cannot be dropped.
Thanks.
Please disregard. I changed the schema owner under the schema object of the
database.
"Emma" wrote:

> How do I determine the schema own by a user, remove them and drop the user?
> I am trying to drop a user and I get the following error message:
> The database principal owns a schema in the database, and cannot be dropped.
> Thanks.

Drop User and schema

How do I determine the schema own by a user, remove them and drop the user?
I am trying to drop a user and I get the following error message:
The database principal owns a schema in the database, and cannot be dropped.
Thanks.Please disregard. I changed the schema owner under the schema object of the
database.
"Emma" wrote:
> How do I determine the schema own by a user, remove them and drop the user?
> I am trying to drop a user and I get the following error message:
> The database principal owns a schema in the database, and cannot be dropped.
> Thanks.sql

Drop User and schema

How do I determine the schema own by a user, remove them and drop the user?
I am trying to drop a user and I get the following error message:
The database principal owns a schema in the database, and cannot be dropped.
Thanks.Please disregard. I changed the schema owner under the schema object of the
database.
"Emma" wrote:

> How do I determine the schema own by a user, remove them and drop the user
?
> I am trying to drop a user and I get the following error message:
> The database principal owns a schema in the database, and cannot be droppe
d.
> Thanks.

drop user

Hi,

I have a user in my SQL server 2005 database sys.sysusers table with following values.

I am unable to delete this user and unable to create a user with this same user name.

Please tell some one what is status=16 and issqluser=0

status 16

ame \CMSXXCMSTESTER

roles NULL

altuid 5

hasdbaccess 0

islogin 1

isntname 0

isntgroup 0

isntuser 0

issqluser 0

isaliased 1

issqlrole 0

isapprole 0

When I tried delete the user using sp_dropuser it says the user doesnt exist or u do not have permissions. later is not correct as i have all permissions as I am admin.

And i also tried sp_change_users_login 'report' but I can't see the user in question.

Please tell me what is status=16 and how a record like this present in table which doesnt allow to delete nor allow to create with same name.

I want to drop this user some how..

Thanks

Hello,

It seems that the account is aliased. execute sp_dropalias.

Hope that helps.

Cheers

Rob

|||I am having the same problem as the OP. sp_dropalias does not work either. Is there any was to remove these records from sys.sysusers. I would like to be able to use the user name that is being held hostage by the status 16.|||Is the user name with status = 16 a windows user or group ?|||I am having the same problem. I have a user '\import' in the database with a status of 16. I cannot drop it as a either user or an alias. I have no idea how the user got on the database (it was there before I took the job) - so I have no idea if it was a user or a group.

|||

Hi

COuld you please post the results of the sp_helpuser command.

regards

Jag

drop user

Hi,

I have a user in my SQL server 2005 database sys.sysusers table with following values.

I am unable to delete this user and unable to create a user with this same user name.

Please tell some one what is status=16 and issqluser=0

status 16

ame \CMSXXCMSTESTER

roles NULL

altuid 5

hasdbaccess 0

islogin 1

isntname 0

isntgroup 0

isntuser 0

issqluser 0

isaliased 1

issqlrole 0

isapprole 0

When I tried delete the user using sp_dropuser it says the user doesnt exist or u do not have permissions. later is not correct as i have all permissions as I am admin.

And i also tried sp_change_users_login 'report' but I can't see the user in question.

Please tell me what is status=16 and how a record like this present in table which doesnt allow to delete nor allow to create with same name.

I want to drop this user some how..

Thanks

Hello,

It seems that the account is aliased. execute sp_dropalias.

Hope that helps.

Cheers

Rob

|||I am having the same problem as the OP. sp_dropalias does not work either. Is there any was to remove these records from sys.sysusers. I would like to be able to use the user name that is being held hostage by the status 16.|||Is the user name with status = 16 a windows user or group ?|||I am having the same problem. I have a user '\import' in the database with a status of 16. I cannot drop it as a either user or an alias. I have no idea how the user got on the database (it was there before I took the job) - so I have no idea if it was a user or a group.

|||

Hi

COuld you please post the results of the sp_helpuser command.

regards

Jag

drop user

Hi,

I have a user in my SQL server 2005 database sys.sysusers table with following values.

I am unable to delete this user and unable to create a user with this same user name.

Please tell some one what is status=16 and issqluser=0

status 16

ame \CMSXXCMSTESTER

roles NULL

altuid 5

hasdbaccess 0

islogin 1

isntname 0

isntgroup 0

isntuser 0

issqluser 0

isaliased 1

issqlrole 0

isapprole 0

When I tried delete the user using sp_dropuser it says the user doesnt exist or u do not have permissions. later is not correct as i have all permissions as I am admin.

And i also tried sp_change_users_login 'report' but I can't see the user in question.

Please tell me what is status=16 and how a record like this present in table which doesnt allow to delete nor allow to create with same name.

I want to drop this user some how..

Thanks

Hello,

It seems that the account is aliased. execute sp_dropalias.

Hope that helps.

Cheers

Rob

|||I am having the same problem as the OP. sp_dropalias does not work either. Is there any was to remove these records from sys.sysusers. I would like to be able to use the user name that is being held hostage by the status 16.|||Is the user name with status = 16 a windows user or group ?|||I am having the same problem. I have a user '\import' in the database with a status of 16. I cannot drop it as a either user or an alias. I have no idea how the user got on the database (it was there before I took the job) - so I have no idea if it was a user or a group.|||

Hi

COuld you please post the results of the sp_helpuser command.

regards

Jag

sql

drop table

I upgraded my system from Sql2000 to sql2005.

And now i have a problem.

I have job wich runs every minute and perform the following

select * into tabler_1 from .....

begin tran

if exists(select * from sysobjects where xtype ='u' and name = 'tabler')

drop table tabler

exec sp_rename tabler_1, tabler

commit

And on client side i have IIS which runs asp pages which connect to database with

ado connction using ODBC and runs procedures wich performs select * from tabler

and there is a problem they some times failes with error tabler does not exists.

It worked fine in sql2000

all connections opened with default setting.

(transaction isolation level read commited)

What abou this here:

if not exists

(

SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'tabler_1'

AND TABLE_TYPE = 'BASE_TABLE'

)

select * into tabler_1 from SomeTable

if exists

(

SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'tabler'

AND TABLE_TYPE = 'BASE_TABLE'

)

BEGIN

DROP TABLE tabler

EXEC sp_rename tabler_1, tabler

END

Make sure that you check the existence of the table before doing a SELECT INTO.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

It is not a problem.

it make drop very nice.

the problem is on the client which runs store procedure like this

create procedure test_sp

as

select * from tabler

go

OR

create procedure test_sp_d

as

exec ('select * from tabler')

go

when i droping table i do it in transaction so schema is locked

the procedures have to wait for commit and then select

it worked fine sql2000

sql

Drop Primary Transaction Log File

Hi All,
I attemping to move a transaction log file online to another location. I performed the following steps;
- Created a second(log_name2) log file.
- ran DBCC Shrinkfile(log_name1,emptyfile)
- alter database mydb
remove file log_name1
* However, I receive the following error 5020 "The primary data or log file cannot be removed from a database".
Is it possible to remove the original log file?
Mike,
You might have to take the database offline by doing:
1.sp_detach_db
2.Then attach the database back using sp_attach_db but this time mentioning
the new path for ldf file.Refer BooksOnLine for syntax of the two commands.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?
|||Hi,
In your case , I feel that the only solution is detach and attach the
databases.
Steps:
1. detach the database using sp_detach_db
2. copy the LDF to new location
3. Attach it back using sp_attach_db
Have a look into the below link:
http://msdn.microsoft.com/library/de...us/createdb/cm
_8_des_03_9dbn.asp
Thanks
Hari
MCDBA
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?

Drop Primary Transaction Log File

Hi All,
I attemping to move a transaction log file online to another location. I per
formed the following steps;
- Created a second(log_name2) log file.
- ran DBCC Shrinkfile(log_name1,emptyfile)
- alter database mydb
remove file log_name1
* However, I receive the following error 5020 "The primary data or log file
cannot be removed from a database".
Is it possible to remove the original log file?Mike,
You might have to take the database offline by doing:
1.sp_detach_db
2.Then attach the database back using sp_attach_db but this time mentioning
the new path for ldf file.Refer BooksOnLine for syntax of the two commands.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?|||Hi,
In your case , I feel that the only solution is detach and attach the
databases.
Steps:
1. detach the database using sp_detach_db
2. copy the LDF to new location
3. Attach it back using sp_attach_db
Have a look into the below link:
http://msdn.microsoft.com/library/d...-us/createdb/cm
_8_des_03_9dbn.asp
Thanks
Hari
MCDBA
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?

drop not null

hi

i'm using mssql server 2000

i want to remove a not null column constraint from the column
answertext in table answertext.

i tried the following line
ALTER TABLE AnswerText ALTER COLUMN AnswerText DROP NOT NULL;

it didn't work and this error message apeared (sorry about the german)
Server: Nachr.-Nr. 156, Schweregrad 15, Status 1, Zeile 6
Falsche Syntax in der Nhe des NOT-Schlsselwortes.

is there a way to drop not null column constraint or do i have to save
the data, drop the table and recreate it?

i created the table with the following ddl code
CREATE TABLE AnswerText(
...
,AnswerText VARCHAR(512) NOT NULL
...
)

tanks for your helpcreate table foo (blah varchar(10) not null)

alter table foo alter column blah varchar(10)null

I found the best way to learn stuff like this is either find it in BOL
or run prfiler on myself and do it in enterprise manager and then look
at the syntax.

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Drop database

I cannot drop a user defined database in the following situation:
In enterprise manager, I select that database, select tables.
In query analyser, run the statement: use master, go, drop that user
database, an error said that database is currently in use.
Then I back to enterprise manager, collapse the database folder. Run the
statement in query analyser again but got the same error.
The way I get around was go back to enterprise manager, select root level or
upper level database, press refresh.
Back to query analyser and run that SQL again and it worked.
Why do I need to refresh enterprise manager after select the root level
folder or collapse the database folder ?Because EM doesn't want to do unecessary database work unless it has to. So, the connection is still
in the old database until you refresh in EM.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alan" <alanpltse@.yahoo.com.au> wrote in message news:OnzQiTEaDHA.440@.tk2msftngp13.phx.gbl...
> I cannot drop a user defined database in the following situation:
> In enterprise manager, I select that database, select tables.
> In query analyser, run the statement: use master, go, drop that user
> database, an error said that database is currently in use.
> Then I back to enterprise manager, collapse the database folder. Run the
> statement in query analyser again but got the same error.
> The way I get around was go back to enterprise manager, select root level or
> upper level database, press refresh.
> Back to query analyser and run that SQL again and it worked.
> Why do I need to refresh enterprise manager after select the root level
> folder or collapse the database folder ?
>

drop clustered index - fails on duplicate key

When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem. Is
there a way to drop the clustered index without creating new indexes? Or have
the new indexes ignore dulicates?
Thanks
Have you got a complete repro? I'm wondering if there is a foreign key from
another table.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem.
Is
there a way to drop the clustered index without creating new indexes? Or
have
the new indexes ignore dulicates?
Thanks
|||My guess is that you have a corruption problem. Seem you have a unique nc index in which you have
managed to get duplicates. So the dropping of the clustered index will re-create the nc index and it
fails because of this. I'd do DBCC CHECKDB and see what it says.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
> When I try to drop my unique clustered index with the following code:
> if exists (select * from dbo.sysindexes
> where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
> drop index [dbo].[my_Table].[my_Index]
> GO
>
> it fails with the error:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 2. Most significant primary key is '23497698'.
> The statement has been terminated.
>
> I understand that when you drop a clustered index unclustered indexes are
> atomatically created and I am guessing one of them is causing the problem. Is
> there a way to drop the clustered index without creating new indexes? Or have
> the new indexes ignore dulicates?
> Thanks

drop clustered index - fails on duplicate key

When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem. I
s
there a way to drop the clustered index without creating new indexes? Or hav
e
the new indexes ignore dulicates?
ThanksHave you got a complete repro? I'm wondering if there is a foreign key from
another table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem.
Is
there a way to drop the clustered index without creating new indexes? Or
have
the new indexes ignore dulicates?
Thanks|||My guess is that you have a corruption problem. Seem you have a unique nc in
dex in which you have
managed to get duplicates. So the dropping of the clustered index will re-cr
eate the nc index and it
fails because of this. I'd do DBCC CHECKDB and see what it says.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
> When I try to drop my unique clustered index with the following code:
> if exists (select * from dbo.sysindexes
> where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
> drop index [dbo].[my_Table].[my_Index]
> GO
>
> it fails with the error:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 2. Most significant primary key is '23497698'.
> The statement has been terminated.
>
> I understand that when you drop a clustered index unclustered indexes are
> atomatically created and I am guessing one of them is causing the problem.
Is
> there a way to drop the clustered index without creating new indexes? Or h
ave
> the new indexes ignore dulicates?
> Thanks

drop clustered index - fails on duplicate key

When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem. Is
there a way to drop the clustered index without creating new indexes? Or have
the new indexes ignore dulicates?
ThanksHave you got a complete repro? I'm wondering if there is a foreign key from
another table.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem.
Is
there a way to drop the clustered index without creating new indexes? Or
have
the new indexes ignore dulicates?
Thanks|||My guess is that you have a corruption problem. Seem you have a unique nc index in which you have
managed to get duplicates. So the dropping of the clustered index will re-create the nc index and it
fails because of this. I'd do DBCC CHECKDB and see what it says.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
> When I try to drop my unique clustered index with the following code:
> if exists (select * from dbo.sysindexes
> where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
> drop index [dbo].[my_Table].[my_Index]
> GO
>
> it fails with the error:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 2. Most significant primary key is '23497698'.
> The statement has been terminated.
>
> I understand that when you drop a clustered index unclustered indexes are
> atomatically created and I am guessing one of them is causing the problem. Is
> there a way to drop the clustered index without creating new indexes? Or have
> the new indexes ignore dulicates?
> Thanks

Drop all the connections to the DataBase

Hi ,
I am using the following code in a batch file to take the back up of a database. but some times it fails with the message
"Cannot access the database becuase it is being used by another process." Is there any way i can force all the connections
to the database to be dropped using code. Any help will be greatly appreciated.
isql -b -S localhost -E -U xyzuser -P xyz -Q "sp_detach_db 'JMS', 'true'"
copy C:\JJMSdb\JMS_Data.MDF C:\JJMSdb\JMS_Data_2004-03-29_14-54-5933.MDF
copy C:\JJMSdb\JMS_Log.LDF C:\JJMSdb\JMS_Log_2004-03-29_14-54-5933.LDF
isql -b -S localhost -E -U xyzuser -P xyzpwd -Q "sp_attach_db @.dbname = 'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 = 'C:\JJMSdb\JMS_Log.LDF'"
Thanks,
Ram
Ram
This procedure written by Narayana Vyas Kondreddi
CREATE PROC sp_dboption2
(
@.dbname sysname = NULL, --Database name
@.optname varchar(35) = NULL, --Option name
@.optvalue varchar(5) = NULL, --Option value, either 'true' or 'false'
@.wait int = NULL --Seconds to wait, before killing the existing
connections
)
AS
BEGIN
/************************************************** *************************
********************************
Copyright 2001 Narayana Vyas Kondreddi. All rights reserved.
Purpose: The system stored procedure sp_dboption fails to set databases in
'read only'/'single user'/'offline'
modes if the database is in use. This procedure works as a wrapper around
sp_dboption and overcomes that
limitation by killing all the active connections. You can configure it to
kill the connections immediately,
or after waiting for a specified interval. This procedure simulates the
new ALTER TABLE syntax of SQL Server
2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options along with
OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
SINGLE_USER, RESTRICTED_USER, MULTI_USER).
Written by: Narayana Vyas Kondreddi
http://vyaskn.tripod.com
Tested on: SQL Server 7.0, Service Pack 3
Date created: October-29-2001 1:30 AM Indian Standard Time
Date modified: October-29-2001 1:30 AM Indian Standard Time
Email: vyaskn@.hotmail.com
Usage: Just run this complete script in the master database to create this
stored procedure. As far as syntax is
concerned, this procedure works very similar to the system stored
procedure sp_dboption. It has an additional
parameter @.wait, which can be used, to wait for a specified number of
seconds, before killing the connections.
The settable database option names need to be specified in full. For
example, the option name 'single' is
considered invalid and 'single user' is considered valid.
To bring pubs database into single user mode:
EXEC sp_dboption2 'pubs', 'single user', 'true'
To bring pubs database into single user mode. Wait for 30 seconds, for
current connections to leave and
start killing the connections after 30 seconds:
EXEC sp_dboption2 'pubs', 'single user', 'true', 30
To bring pubs database into read/write mode:
EXEC sp_dboption2 'pubs', 'read only', 'false'
To bring pubs database into read/write mode. Wait for 30 seconds, for
current connections to leave and
start killing the connections after 30 seconds:
EXEC sp_dboption2 'pubs', 'read only', 'false', 30
************************************************** **************************
*******************************/
DECLARE @.dbid int, @.spid int, @.execstr varchar(15), @.waittime varchar(15),
@.final_chk int
--Only the following options require that, no other connections should
access the database
IF (LOWER(@.optname) IN ('offline', 'read only', 'single user')) AND
(LOWER(@.optvalue) IN('true', 'false'))
BEGIN
--Determining whether to wait, before killing the existing connections
IF @.wait > 0
BEGIN
SET @.waittime = (SELECT CONVERT(varchar, DATEADD(s, @.wait, GETDATE()),
14))
WAITFOR TIME @.waittime --Wait the specified number of seconds
END
SET @.dbid = DB_ID(@.dbname) --Getting the database_id for the specified
database
--Get the lowest spid
TryAgain:
SET @.spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid =
@.dbid)
WHILE @.spid IS NOT NULL
BEGIN
IF @.spid <> @.@.SPID --To avoid the KILL attempt on own connection
BEGIN
SET @.execstr = 'KILL ' + LTRIM(STR(@.spid))
EXEC(@.execstr) --Killing the connection
END
--Get the spid higher than the last spid
SET @.spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid =
@.dbid AND spid > @.spid)
END
END
SET @.final_chk = (SELECT COUNT(spid) FROM master..sysprocesses WHERE dbid =
@.dbid)
IF (@.final_chk = 0) OR (@.final_chk = 1 AND DB_NAME() = @.dbname)
BEGIN
EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling sp_dboption to
complete the job
END
ELSE
BEGIN
GOTO TryAgain --New connections popped up, or killed connections aren't
cleaned up yet, so try killing them again
END
END
"Ram" <anonymous@.discussions.microsoft.com> wrote in message
news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
> Hi ,
> I am using the following code in a batch file to take the back up of a
database. but some times it fails with the message
> "Cannot access the database becuase it is being used by another process."
Is there any way i can force all the connections
> to the database to be dropped using code. Any help will be greatly
appreciated.
> isql -b -S localhost -E -U xyzuser -P xyz -Q "sp_detach_db 'JMS', 'true'"
> copy C:\JJMSdb\JMS_Data.MDF C:\JJMSdb\JMS_Data_2004-03-29_14-54-5933.MDF
> copy C:\JJMSdb\JMS_Log.LDF C:\JJMSdb\JMS_Log_2004-03-29_14-54-5933.LDF
> isql -b -S localhost -E -U xyzuser -P xyzpwd -Q "sp_attach_db @.dbname =
'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =
'C:\JJMSdb\JMS_Log.LDF'"
> Thanks,
> Ram
>
>
|||Why would you KILL connections instead of using the "ALTER=20
DATABASE dbname set SINGLE_USER with rollback immediate"=20
syntax that you mention? It is much more clean than=20
issuing a bunch of KILL commands. What if you KILLed a=20
process that was going to take a long time to die or just=20
hung? You could potentially corrupt your database using=20
KILL commands. KILL should be used very carefully. =20
Van

>--Original Message--
>Ram
>This procedure written by Narayana Vyas Kondreddi
>CREATE PROC sp_dboption2
>(
> @.dbname sysname =3D NULL, --Database name
> @.optname varchar(35) =3D NULL, --Option name
> @.optvalue varchar(5) =3D NULL, --Option value,=20
either 'true' or 'false'
> @.wait int =3D NULL --Seconds to wait, before killing=20
the existing
>connections
>)
>AS
>BEGIN
>/************************************************** *******
******************
>********************************
> Copyright =A9 2001 Narayana Vyas Kondreddi. All rights=20
reserved.
>Purpose: The system stored procedure sp_dboption fails to=20
set databases in
>'read only'/'single user'/'offline'
> modes if the database is in use. This procedure works=20
as a wrapper around
>sp_dboption and overcomes that
> limitation by killing all the active connections. You=20
can configure it to
>kill the connections immediately,
> or after waiting for a specified interval. This=20
procedure simulates the
>new ALTER TABLE syntax of SQL Server
> 2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options=20
along with
>OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
> SINGLE_USER, RESTRICTED_USER, MULTI_USER).
>Written by: Narayana Vyas Kondreddi
> http://vyaskn.tripod.com
>Tested on: SQL Server 7.0, Service Pack 3
>Date created: October-29-2001 1:30 AM Indian Standard Time
>Date modified: October-29-2001 1:30 AM Indian Standard=20
Time
>Email: vyaskn@.hotmail.com
>Usage: Just run this complete script in the master=20
database to create this
>stored procedure. As far as syntax is
> concerned, this procedure works very similar to the=20
system stored
>procedure sp_dboption. It has an additional
> parameter @.wait, which can be used, to wait for a=20
specified number of
>seconds, before killing the connections.
> The settable database option names need to be specified=20
in full. For
>example, the option name 'single' is
> considered invalid and 'single user' is considered=20
valid.
> To bring pubs database into single user mode:
> EXEC sp_dboption2 'pubs', 'single user', 'true'
> To bring pubs database into single user mode. Wait for=20
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'single user', 'true', 30
> To bring pubs database into read/write mode:
> EXEC sp_dboption2 'pubs', 'read only', 'false'
> To bring pubs database into read/write mode. Wait for=20
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'read only', 'false', 30
>
>************************************************* *********
******************
>*******************************/
> DECLARE @.dbid int, @.spid int, @.execstr varchar(15),=20
@.waittime varchar(15),
>@.final_chk int
> --Only the following options require that, no other=20
connections should
>access the database
> IF (LOWER(@.optname) IN ('offline', 'read only', 'single=20
user')) AND
>(LOWER(@.optvalue) IN('true', 'false'))
> BEGIN
> --Determining whether to wait, before killing the=20
existing connections
> IF @.wait > 0
> BEGIN
> SET @.waittime =3D (SELECT CONVERT(varchar, DATEADD(s,=20
@.wait, GETDATE()),
>14))
> WAITFOR TIME @.waittime --Wait the specified number of=20
seconds
> END
> SET @.dbid =3D DB_ID(@.dbname) --Getting the database_id=20
for the specified
>database
> --Get the lowest spid
> TryAgain:
> SET @.spid =3D (SELECT MIN(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid)
> WHILE @.spid IS NOT NULL
> BEGIN
> IF @.spid <> @.@.SPID --To avoid the KILL attempt on own=20
connection
> BEGIN
> SET @.execstr =3D 'KILL ' + LTRIM(STR(@.spid))
> EXEC(@.execstr) --Killing the connection
> END
> --Get the spid higher than the last spid
> SET @.spid =3D (SELECT MIN(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid AND spid > @.spid)
> END
> END
> SET @.final_chk =3D (SELECT COUNT(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid)
> IF (@.final_chk =3D 0) OR (@.final_chk =3D 1 AND DB_NAME() =3D=20
@.dbname)
> BEGIN
> EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling=20
sp_dboption to
>complete the job
> END
> ELSE
> BEGIN
> GOTO TryAgain --New connections popped up, or killed=20
connections aren't
>cleaned up yet, so try killing them again
> END
>END
>
>
>"Ram" <anonymous@.discussions.microsoft.com> wrote in=20
message
>news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
the back up of a
>database. but some times it fails with the message
another process."
>Is there any way i can force all the connections
will be greatly
>appreciated.
Q "sp_detach_db 'JMS', 'true'"
29_14-54-5933.MDF
29_14-54-5933.LDF
Q "sp_attach_db @.dbname =3D
>'JMS', @.filename1 =3D 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =3D
>'C:\JJMSdb\JMS_Log.LDF'"
>
>.
>
|||Van
SQL Server 7.0
If you have an active users in your database you will not be able use 'SET
SINGLE USER' mode
Server: Msg 15089, Level 11, State 1, Procedure sp_dboption, Line 400
Cannot change the 'single user' option of a database while another user is
in the database.
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:15e7101c416a4$9ad58a70$a501280a@.phx.gbl...
Why would you KILL connections instead of using the "ALTER
DATABASE dbname set SINGLE_USER with rollback immediate"
syntax that you mention? It is much more clean than
issuing a bunch of KILL commands. What if you KILLed a
process that was going to take a long time to die or just
hung? You could potentially corrupt your database using
KILL commands. KILL should be used very carefully.
Van

>--Original Message--
>Ram
>This procedure written by Narayana Vyas Kondreddi
>CREATE PROC sp_dboption2
>(
> @.dbname sysname = NULL, --Database name
> @.optname varchar(35) = NULL, --Option name
> @.optvalue varchar(5) = NULL, --Option value,
either 'true' or 'false'
> @.wait int = NULL --Seconds to wait, before killing
the existing
>connections
>)
>AS
>BEGIN
>/************************************************** *******
******************
>********************************
> Copyright 2001 Narayana Vyas Kondreddi. All rights
reserved.
>Purpose: The system stored procedure sp_dboption fails to
set databases in
>'read only'/'single user'/'offline'
> modes if the database is in use. This procedure works
as a wrapper around
>sp_dboption and overcomes that
> limitation by killing all the active connections. You
can configure it to
>kill the connections immediately,
> or after waiting for a specified interval. This
procedure simulates the
>new ALTER TABLE syntax of SQL Server
> 2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options
along with
>OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
> SINGLE_USER, RESTRICTED_USER, MULTI_USER).
>Written by: Narayana Vyas Kondreddi
> http://vyaskn.tripod.com
>Tested on: SQL Server 7.0, Service Pack 3
>Date created: October-29-2001 1:30 AM Indian Standard Time
>Date modified: October-29-2001 1:30 AM Indian Standard
Time
>Email: vyaskn@.hotmail.com
>Usage: Just run this complete script in the master
database to create this
>stored procedure. As far as syntax is
> concerned, this procedure works very similar to the
system stored
>procedure sp_dboption. It has an additional
> parameter @.wait, which can be used, to wait for a
specified number of
>seconds, before killing the connections.
> The settable database option names need to be specified
in full. For
>example, the option name 'single' is
> considered invalid and 'single user' is considered
valid.
> To bring pubs database into single user mode:
> EXEC sp_dboption2 'pubs', 'single user', 'true'
> To bring pubs database into single user mode. Wait for
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'single user', 'true', 30
> To bring pubs database into read/write mode:
> EXEC sp_dboption2 'pubs', 'read only', 'false'
> To bring pubs database into read/write mode. Wait for
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'read only', 'false', 30
>
>************************************************* *********
******************
>*******************************/
> DECLARE @.dbid int, @.spid int, @.execstr varchar(15),
@.waittime varchar(15),
>@.final_chk int
> --Only the following options require that, no other
connections should
>access the database
> IF (LOWER(@.optname) IN ('offline', 'read only', 'single
user')) AND
>(LOWER(@.optvalue) IN('true', 'false'))
> BEGIN
> --Determining whether to wait, before killing the
existing connections
> IF @.wait > 0
> BEGIN
> SET @.waittime = (SELECT CONVERT(varchar, DATEADD(s,
@.wait, GETDATE()),
>14))
> WAITFOR TIME @.waittime --Wait the specified number of
seconds
> END
> SET @.dbid = DB_ID(@.dbname) --Getting the database_id
for the specified
>database
> --Get the lowest spid
> TryAgain:
> SET @.spid = (SELECT MIN(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid)
> WHILE @.spid IS NOT NULL
> BEGIN
> IF @.spid <> @.@.SPID --To avoid the KILL attempt on own
connection
> BEGIN
> SET @.execstr = 'KILL ' + LTRIM(STR(@.spid))
> EXEC(@.execstr) --Killing the connection
> END
> --Get the spid higher than the last spid
> SET @.spid = (SELECT MIN(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid AND spid > @.spid)
> END
> END
> SET @.final_chk = (SELECT COUNT(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid)
> IF (@.final_chk = 0) OR (@.final_chk = 1 AND DB_NAME() =
@.dbname)
> BEGIN
> EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling
sp_dboption to
>complete the job
> END
> ELSE
> BEGIN
> GOTO TryAgain --New connections popped up, or killed
connections aren't
>cleaned up yet, so try killing them again
> END
>END
>
>
>"Ram" <anonymous@.discussions.microsoft.com> wrote in
message
>news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
the back up of a
>database. but some times it fails with the message
another process."
>Is there any way i can force all the connections
will be greatly
>appreciated.
Q "sp_detach_db 'JMS', 'true'"
29_14-54-5933.MDF
29_14-54-5933.LDF
Q "sp_attach_db @.dbname =
>'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =
>'C:\JJMSdb\JMS_Log.LDF'"
>
>.
>
|||True, SQL 7.0 doesn't support the "with rollback=20
immediate" syntax (or at least I don't think it does). =20
The person who posted this question didn't state what=20
version of SQL they are using. But even if it is version=20
7.0 and my suggestion that uses the "with rollback=20
immediate" doesn't work, it's still not a good idea to=20
have an SP with KILL commands that runs as a scheduled=20
process. KILL commands should be monitored and used=20
carefully to ensure data and database corruption do not=20
occur. It would be better to stop SQL server, start it=20
back up and then put it into single user mode for the=20
detach. This could be done with NET STOP and NET START=20
from the batch file.
A better solution to the whole thing may be to just use=20
the BACKUP DATABASE syntax and not worry about putting it=20
in single user mode or detaching.

>--Original Message--
>Van
>SQL Server 7.0
>If you have an active users in your database you will not=20
be able use 'SET
>SINGLE USER' mode
>Server: Msg 15089, Level 11, State 1, Procedure=20
sp_dboption, Line 400
>Cannot change the 'single user' option of a database=20
while another user is
>in the database.
>
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote=20
in message
>news:15e7101c416a4$9ad58a70$a501280a@.phx.gbl...
>Why would you KILL connections instead of using the "ALTER
>DATABASE dbname set SINGLE_USER with rollback immediate"
>syntax that you mention? It is much more clean than
>issuing a bunch of KILL commands. What if you KILLed a
>process that was going to take a long time to die or just
>hung? You could potentially corrupt your database using
>KILL commands. KILL should be used very carefully.
>Van
>
>either 'true' or 'false'
>the existing
*
>******************
>reserved.
>set databases in
>as a wrapper around
>can configure it to
>procedure simulates the
>along with
Time
>Time
>database to create this
>system stored
>specified number of
>in full. For
>valid.
>30 seconds, for
>30 seconds, for
*
>******************
>@.waittime varchar(15),
>connections should
>user')) AND
>existing connections
>@.wait, GETDATE()),
>seconds
>for the specified
>master..sysprocesses WHERE dbid =3D
>connection
>master..sysprocesses WHERE dbid =3D
>master..sysprocesses WHERE dbid =3D
>@.dbname)
>sp_dboption to
>connections aren't
>message
CEB5FC97F532@.microsoft.com...
>the back up of a
>another process."
>will be greatly
>Q "sp_detach_db 'JMS', 'true'"
>29_14-54-5933.MDF
>29_14-54-5933.LDF
>Q "sp_attach_db @.dbname =3D
=3D
>
>.
>