Removing a server: object

Showing posts with label object. Show all posts

Wednesday, March 21, 2012

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')

Rename a table sql 2k

HI,
I am trying to rename a table, it gives me this error,
Object 'Test' cannot be renamed because the object participates in enforced
dependencies.
How do I rename a table then create a new table with the original name.
thanks,"mecn" <mecn2002@.yahoo.com> wrote in message
news:uC6yio6OGHA.3100@.TK2MSFTNGP11.phx.gbl...
> HI,
> I am trying to rename a table, it gives me this error,
> Object 'Test' cannot be renamed because the object participates in
> enforced dependencies.
> How do I rename a table then create a new table with the original name.
> thanks,
>
It looks like you have some FK constraints that reference that table, or
some SCHEMABINDING constraints somewhere. You will have to drop those first
before you can rename the table.
You could use ALTER TABLE commands to add new columns to your table...
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks, Rick.
How do I drop these dependencies(I just want to rename it --lots of
records) and readd depedencies on the new table.
Thanks again
"Rick Sawtell" <Quickening@.msn.com> wrote in message
news:ezvV716OGHA.1696@.TK2MSFTNGP14.phx.gbl...
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uC6yio6OGHA.3100@.TK2MSFTNGP11.phx.gbl...
>> HI,
>> I am trying to rename a table, it gives me this error,
>> Object 'Test' cannot be renamed because the object participates in
>> enforced dependencies.
>> How do I rename a table then create a new table with the original name.
>> thanks,
> It looks like you have some FK constraints that reference that table, or
> some SCHEMABINDING constraints somewhere. You will have to drop those
> first before you can rename the table.
> You could use ALTER TABLE commands to add new columns to your table...
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||You can do ALTER of the views and functions that has schemabinding against your table to recreate
the objects without schemabinding, make your table changes and then re-create your functions and
views again, with schemabinding.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:uurS1%236OGHA.344@.TK2MSFTNGP11.phx.gbl...
> Thanks, Rick.
> How do I drop these dependencies(I just want to rename it --lots of records) and readd
> depedencies on the new table.
> Thanks again
> "Rick Sawtell" <Quickening@.msn.com> wrote in message news:ezvV716OGHA.1696@.TK2MSFTNGP14.phx.gbl...
>> "mecn" <mecn2002@.yahoo.com> wrote in message news:uC6yio6OGHA.3100@.TK2MSFTNGP11.phx.gbl...
>> HI,
>> I am trying to rename a table, it gives me this error,
>> Object 'Test' cannot be renamed because the object participates in enforced dependencies.
>> How do I rename a table then create a new table with the original name.
>> thanks,
>>
>> It looks like you have some FK constraints that reference that table, or some SCHEMABINDING
>> constraints somewhere. You will have to drop those first before you can rename the table.
>> You could use ALTER TABLE commands to add new columns to your table...
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>|||Thanks,
There is no quick way to drop the dependencies? That table has about 15 - 20
dependecies. I need to alter all 15-20 objects before I could rename and
alter them again to a new table?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O2cd0G7OGHA.2624@.TK2MSFTNGP12.phx.gbl...
> You can do ALTER of the views and functions that has schemabinding against
> your table to recreate the objects without schemabinding, make your table
> changes and then re-create your functions and views again, with
> schemabinding.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uurS1%236OGHA.344@.TK2MSFTNGP11.phx.gbl...
>> Thanks, Rick.
>> How do I drop these dependencies(I just want to rename it --lots of
>> records) and readd depedencies on the new table.
>> Thanks again
>> "Rick Sawtell" <Quickening@.msn.com> wrote in message
>> news:ezvV716OGHA.1696@.TK2MSFTNGP14.phx.gbl...
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:uC6yio6OGHA.3100@.TK2MSFTNGP11.phx.gbl...
>> HI,
>> I am trying to rename a table, it gives me this error,
>> Object 'Test' cannot be renamed because the object participates in
>> enforced dependencies.
>> How do I rename a table then create a new table with the original name.
>> thanks,
>>
>> It looks like you have some FK constraints that reference that table, or
>> some SCHEMABINDING constraints somewhere. You will have to drop those
>> first before you can rename the table.
>> You could use ALTER TABLE commands to add new columns to your table...
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
>|||I guess you could write some code which does this, using the DMO or SMO API (depending on SQL Server
version). http://www.karaszi.com/SQLServer/info_generate_script.asp
But you still have to modify these objects as you are about to do changes in the table (name), and
these objects need to be adjusted properly. If it is a one off, manual would be the fastest thing
(IMO). If you are about to do this regularly, I'd go with some DMO or SMO coding.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:eNMM0O7OGHA.3944@.tk2msftngp13.phx.gbl...
> Thanks,
> There is no quick way to drop the dependencies? That table has about 15 - 20 dependecies. I need
> to alter all 15-20 objects before I could rename and alter them again to a new table?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:O2cd0G7OGHA.2624@.TK2MSFTNGP12.phx.gbl...
>> You can do ALTER of the views and functions that has schemabinding against your table to recreate
>> the objects without schemabinding, make your table changes and then re-create your functions and
>> views again, with schemabinding.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "mecn" <mecn2002@.yahoo.com> wrote in message news:uurS1%236OGHA.344@.TK2MSFTNGP11.phx.gbl...
>> Thanks, Rick.
>> How do I drop these dependencies(I just want to rename it --lots of records) and readd
>> depedencies on the new table.
>> Thanks again
>> "Rick Sawtell" <Quickening@.msn.com> wrote in message
>> news:ezvV716OGHA.1696@.TK2MSFTNGP14.phx.gbl...
>> "mecn" <mecn2002@.yahoo.com> wrote in message news:uC6yio6OGHA.3100@.TK2MSFTNGP11.phx.gbl...
>> HI,
>> I am trying to rename a table, it gives me this error,
>> Object 'Test' cannot be renamed because the object participates in enforced dependencies.
>> How do I rename a table then create a new table with the original name.
>> thanks,
>>
>> It looks like you have some FK constraints that reference that table, or some SCHEMABINDING
>> constraints somewhere. You will have to drop those first before you can rename the table.
>> You could use ALTER TABLE commands to add new columns to your table...
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
>>
>|||Tibor,
Thanks a lot.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u8T68l7OGHA.1460@.TK2MSFTNGP10.phx.gbl...
>I guess you could write some code which does this, using the DMO or SMO API
>(depending on SQL Server version).
>http://www.karaszi.com/SQLServer/info_generate_script.asp
> But you still have to modify these objects as you are about to do changes
> in the table (name), and these objects need to be adjusted properly. If it
> is a one off, manual would be the fastest thing (IMO). If you are about to
> do this regularly, I'd go with some DMO or SMO coding.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:eNMM0O7OGHA.3944@.tk2msftngp13.phx.gbl...
>> Thanks,
>> There is no quick way to drop the dependencies? That table has about 15 -
>> 20 dependecies. I need to alter all 15-20 objects before I could rename
>> and alter them again to a new table?
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:O2cd0G7OGHA.2624@.TK2MSFTNGP12.phx.gbl...
>> You can do ALTER of the views and functions that has schemabinding
>> against your table to recreate the objects without schemabinding, make
>> your table changes and then re-create your functions and views again,
>> with schemabinding.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:uurS1%236OGHA.344@.TK2MSFTNGP11.phx.gbl...
>> Thanks, Rick.
>> How do I drop these dependencies(I just want to rename it --lots of
>> records) and readd depedencies on the new table.
>> Thanks again
>> "Rick Sawtell" <Quickening@.msn.com> wrote in message
>> news:ezvV716OGHA.1696@.TK2MSFTNGP14.phx.gbl...
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:uC6yio6OGHA.3100@.TK2MSFTNGP11.phx.gbl...
>> HI,
>> I am trying to rename a table, it gives me this error,
>> Object 'Test' cannot be renamed because the object participates in
>> enforced dependencies.
>> How do I rename a table then create a new table with the original
>> name.
>> thanks,
>>
>> It looks like you have some FK constraints that reference that table,
>> or some SCHEMABINDING constraints somewhere. You will have to drop
>> those first before you can rename the table.
>> You could use ALTER TABLE commands to add new columns to your table...
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
>>
>>
>

Monday, March 12, 2012

Removing SQL Parameters from Command Object after use.

Hi all,

is it good practice to remove SQL Parameters from the Command Object's parameters collection after the CommandObject has executed its SQL containing the SQL Parameters?

The reason I ask is because I have encountered some issues (errors) stating that the SQL Parameter cant be used because it is still contained in another Parameter collection - ( I created the Parameters, and then use these same parameter (through looping) to execute new commands).

Thanks

Tryst, if you are in a loop reusing the same command object, clear your parameters either before you begin adding them again, or after you execute the command using:

cmd.Parameters.Clear

That should be all you need to reuse your parameter objects again.

|||OK - thanks.

Removing Replication Triggers.

Does anyone know how to remove 'Replication Triggers'?

I have a database created by export of data object. This process does not export any system tables to do with replication but there still seems to be something in the database which references them. There is no poblem if I backup and restore the dataabse.

Any advice?

Thanks Edno such monkey as a replication trigger. My guess here is that you replicating a table with triggers associated with it and when the publisher was defined the table in question had a trigger that did not have the trigger defined with the NOT FOR REPLICATION option and now you are getting conflicts.

If this is the case, ALTER TRIGGER should set you on the right path. Look up it's arguments in Books Online.

If this is not the case, why do you think triggers are the problem?

Friday, March 9, 2012

Removing owner from objects listed in the object explorer?

Is there a way to remove the owner from objects listed in object explorer? This is cumbersome because you cannot jump to an object using the keyboard when the ower is the same for all of your objects.Hello there

Thanks for asking this question.
Unfortunately the ability to remove the 'owner' part of objects listed in the object explorer is not available in the final release. [It was earlier available on the beta releases, but it was cut since the feature was not ready to support all scenarios]

You might be able to use the 'filter' functionality to minimize the list of objects displayed - which might help in dealing with the cumbersome task of walking thru all the available objects.

Thanks,
[Venkat]
This posting is provided "AS IS" with no warranties, and confers no rights.

Monday, February 20, 2012

removing a user from a database problem

I have followed the procedure in http://support.microsoft.com/kb/275312
to change object ownership so that I can delete a user.
This appears to have worked ( subsequent runs tell me that the
original user owns no objects and a query on the sysobjects table
filtered on their uid confirms this)
When I go to delete the user in enterprise manager it tells me that
the user still owns objects ( i have refreshed the view and even
exited EM to no effect)
I am using SQL server 2000 sp4
Any help much appreciated...
TIAHi
Have you tried sp_dropuser stored procedure in QA?
<pryan@.meticulus.com> wrote in message
news:1185880756.933653.204030@.19g2000hsx.googlegroups.com...
>I have followed the procedure in http://support.microsoft.com/kb/275312
> to change object ownership so that I can delete a user.
> This appears to have worked ( subsequent runs tell me that the
> original user owns no objects and a query on the sysobjects table
> filtered on their uid confirms this)
> When I go to delete the user in enterprise manager it tells me that
> the user still owns objects ( i have refreshed the view and even
> exited EM to no effect)
> I am using SQL server 2000 sp4
> Any help much appreciated...
> TIA
>

removing a user from a database problem

I have followed the procedure in http://support.microsoft.com/kb/275312
to change object ownership so that I can delete a user.
This appears to have worked ( subsequent runs tell me that the
original user owns no objects and a query on the sysobjects table
filtered on their uid confirms this)
When I go to delete the user in enterprise manager it tells me that
the user still owns objects ( i have refreshed the view and even
exited EM to no effect)
I am using SQL server 2000 sp4
Any help much appreciated...
TIA
Hi
Have you tried sp_dropuser stored procedure in QA?
<pryan@.meticulus.com> wrote in message
news:1185880756.933653.204030@.19g2000hsx.googlegro ups.com...
>I have followed the procedure in http://support.microsoft.com/kb/275312
> to change object ownership so that I can delete a user.
> This appears to have worked ( subsequent runs tell me that the
> original user owns no objects and a query on the sysobjects table
> filtered on their uid confirms this)
> When I go to delete the user in enterprise manager it tells me that
> the user still owns objects ( i have refreshed the view and even
> exited EM to no effect)
> I am using SQL server 2000 sp4
> Any help much appreciated...
> TIA
>

Removing a SQL server backup

In SQL Enterprise Manager, an employee right-clicked the 'Database' object
and created a scheduled backup. Then they expanded the 'Database' object,
found the target database 'XYZdatabase', right-clicked that too and
scheduled a second backup to a different drive.
How do I delete the backup job(s) because I have two running right now?
If I remove the file location where it backs up to, it tells me I must
select a file name / location. There is no way to remove the databse in the
SQL Server backup window, only an option from the dropdown to select
different databases.
Thanks in advance.r042wal schrieb:
> In SQL Enterprise Manager, an employee right-clicked the 'Database'
> object and created a scheduled backup. Then they expanded the
> 'Database' object, found the target database 'XYZdatabase',
> right-clicked that too and scheduled a second backup to a different drive.
> How do I delete the backup job(s) because I have two running right now?
look at the SQL Agent Jobs. There you find the scheduled jobs and can
delete one.
> If I remove the file location where it backs up to, it tells me I must
> select a file name / location. There is no way to remove the databse in
> the SQL Server backup window, only an option from the dropdown to select
> different databases.
> Thanks in advance.
hth
Gregor Stefka

Wednesday, March 21, 2012

rename column with check constraints

Rename a table sql 2k

Rename a table sql 2k

Rename a table sql 2k

Monday, March 12, 2012

Removing SQL Parameters from Command Object after use.

Removing Replication Triggers.

Friday, March 9, 2012

Removing owner from objects listed in the object explorer?

Monday, February 20, 2012

removing a user from a database problem

removing a user from a database problem

removing a user from a database problem

Removing a SQL server backup

Removing a server

Blog Archive

About Me