Rename a primary key column

hi i am trying to rename a primary using sp_rename. but it keeps failing with
message that the column is involved in dependencies. but i have already
dropped all constraints on this table! any help please
On Tue, 31 May 2005 18:04:02 -0700, "sql guy" <sql
guy@.discussions.microsoft.com> wrote:

>hi i am trying to rename a primary using sp_rename. but it keeps failing with
>message that the column is involved in dependencies. but i have already
>dropped all constraints on this table! any help please
Hi sql guy,
Are there any foreign key constraints on other table that reference the
primary key column you're trying to rename?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||hi hugo, yes there are loads of them. however, i finally resolved it all
yesterday. thanks for the reply.
cheers
"Hugo Kornelis" wrote:

> On Tue, 31 May 2005 18:04:02 -0700, "sql guy" <sql
> guy@.discussions.microsoft.com> wrote:
>
> Hi sql guy,
> Are there any foreign key constraints on other table that reference the
> primary key column you're trying to rename?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Removing Log Shipping after disaster

I am trying to get our database back to a "clean" state after a disaster at the weekend. The primary server died - completely - and so the secondary server was promoted to primary. Now, I'd like to remove all traces of log shipping on the server so I can start fresh when our new server arrives imminently.

However, when I try to remove log shipping, SQL tries to connect to the old primary server which of course no longer exists. I can't find anyway to remove the log shipping. What can I do?

Delete the jobs that are doing the backup, copy, and restore. Remove the fileshares. No more log shipping. If this is SQL Server 2000, then just delete the maintenance plan that you used to setup log shipping.|||I've deleted the jobs on what was the secondary server (now the primary). I'm not able to delete the maintenance plans as SQL says I must remove log shipping before deleting the plan. If I try to remove the log shipping, SQL tries to connect to the old machine and fails (as it doesn't exist anymore) .

Removing IDENTITY from existing column

I have been testing some code that has been inserting large amounts of data
into some tables. All of the tables have a Primary Key that is also an
Identity. After a bad run, I go through and DELETE all of the entries in
each table (I would TRUNCATE, but they all have at least three other tables
referencing them and you can't TRUNCATE a linked table). Only problem is
that the IDs are getting extremely large and make it harder for me to
determine if the code is working correctly. In the TSQL that I use to DELET
E
the contents of the tables, I would like to set the IDENTITY to NO, then bac
k
to YES so that the IDs increment from 1 again instead of some very large
number. The only way that I can see is to drop the column and then add it
again. I am not sure how well this would work since I have lots of foreign
keys referencing these PKs.
Thanks in advance.
--
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsDrop the IDENTITY and not the column, then put the IDENTITY back on the
column -- one way.
Owen
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:27E1D52C-BE0C-425D-BCA2-04BB2A89B367@.microsoft.com...
>I have been testing some code that has been inserting large amounts of data
> into some tables. All of the tables have a Primary Key that is also an
> Identity. After a bad run, I go through and DELETE all of the entries in
> each table (I would TRUNCATE, but they all have at least three other
> tables
> referencing them and you can't TRUNCATE a linked table). Only problem is
> that the IDs are getting extremely large and make it harder for me to
> determine if the code is working correctly. In the TSQL that I use to
> DELETE
> the contents of the tables, I would like to set the IDENTITY to NO, then
> back
> to YES so that the IDs increment from 1 again instead of some very large
> number. The only way that I can see is to drop the column and then add it
> again. I am not sure how well this would work since I have lots of
> foreign
> keys referencing these PKs.
> Thanks in advance.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||See "DBCC CHECKIDENT" in BOL.
AMB
"Chris Lieb" wrote:

> I have been testing some code that has been inserting large amounts of dat
a
> into some tables. All of the tables have a Primary Key that is also an
> Identity. After a bad run, I go through and DELETE all of the entries in
> each table (I would TRUNCATE, but they all have at least three other table
s
> referencing them and you can't TRUNCATE a linked table). Only problem is
> that the IDs are getting extremely large and make it harder for me to
> determine if the code is working correctly. In the TSQL that I use to DEL
ETE
> the contents of the tables, I would like to set the IDENTITY to NO, then b
ack
> to YES so that the IDs increment from 1 again instead of some very large
> number. The only way that I can see is to drop the column and then add it
> again. I am not sure how well this would work since I have lots of foreig
n
> keys referencing these PKs.
> Thanks in advance.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Try: DBCC CHECKIDENT (tableName)
Look it ub in BOL.
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:27E1D52C-BE0C-425D-BCA2-04BB2A89B367@.microsoft.com...
>I have been testing some code that has been inserting large amounts of data
> into some tables. All of the tables have a Primary Key that is also an
> Identity. After a bad run, I go through and DELETE all of the entries in
> each table (I would TRUNCATE, but they all have at least three other
> tables
> referencing them and you can't TRUNCATE a linked table). Only problem is
> that the IDs are getting extremely large and make it harder for me to
> determine if the code is working correctly. In the TSQL that I use to
> DELETE
> the contents of the tables, I would like to set the IDENTITY to NO, then
> back
> to YES so that the IDs increment from 1 again instead of some very large
> number. The only way that I can see is to drop the column and then add it
> again. I am not sure how well this would work since I have lots of
> foreign
> keys referencing these PKs.
> Thanks in advance.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Use
DBCC CHECKIDENT ('table_name',RESEED,0)
to reset the IDENTITY value.
David Portas
SQL Server MVP
--|||I tried to add IDENTITY to an existing column, and it didn't work. I was
told on this forum that it can't be done.
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:O%23vzDYhYFHA.980@.TK2MSFTNGP12.phx.gbl...
> Drop the IDENTITY and not the column, then put the IDENTITY back on the
> column -- one way.
> Owen
> "Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
> news:27E1D52C-BE0C-425D-BCA2-04BB2A89B367@.microsoft.com...
>|||Strange. I do that all the time (using Enterprise Manager)...
Owen
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:eI6kqdhYFHA.3960@.TK2MSFTNGP10.phx.gbl...
>I tried to add IDENTITY to an existing column, and it didn't work. I was
>told on this forum that it can't be done.
>
> "Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
> news:O%23vzDYhYFHA.980@.TK2MSFTNGP12.phx.gbl...
>|||Strange that you use Enterprise Manager to make changes to table
structures :-)
Paul is in fact correct that you cannot do this with a single statement
in TSQL. One of the (many) problems with using Enerprise Manager is
that to achieve this it will drop your table, constraints and indexes,
recreate and then copy all your data over. Definitely not recommended
on a system that's in use!
David Portas
SQL Server MVP
--|||That's because behind the scenes EM will jump through the hoops
of creating a temp table copy of the original table, creating a
new table with identity column assigned, inserting data from the
the temp table, and then dropping the temp table. There's no
easy way of doing it through Query Analyzer other than
what I've outlined.
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:e$HrUihYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Strange. I do that all the time (using Enterprise Manager)...
> Owen
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:eI6kqdhYFHA.3960@.TK2MSFTNGP10.phx.gbl...
an
then
large
add
>|||EM does a lot of work behind the scenes.
A SIMPLE example on a table with no foreign key references where Identity is
removed:
It creates a new table, copies the data, drops the old table and renames the
new one.
If there where foreign key references, EM has to handle this too.
In EM, you have an icon in the table design view to save the script when you
make a change and before committing it.
Try it (on a test table of course).
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:e$HrUihYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Strange. I do that all the time (using Enterprise Manager)...
> Owen
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:eI6kqdhYFHA.3960@.TK2MSFTNGP10.phx.gbl...

Removing Extra Transaction Logs

I created some extra transaction logs for a database because of a lack of space however I want to now get rid of those and only have one primary transaction log. I cannot move data into other files like you can with a filegroup, how do I get rid of the files ?Originally posted by yorkie
Q1 I created some extra transaction logs for a database because of a lack of space however I want to now get rid of those and only have one primary transaction log. I cannot move data into other files like you can with a filegroup, how do I get rid of the files ?

-- A1 First empty the TL file(s) then remove it (them) from the DB. For example:

dbcc ShrinkFile('TL_FileName', EmptyFile)
Go

-- Then alter the DB to remove it

Alter Database DBName
Remove File TL_FileName
Go|||Thanks, worked a treat.

Removing clustered index on Primary key.

Hi,
I want to remove clustering of the Primary Key and make a foreign key in the
table the clustered index. Can I do this with a replicated database? I
suppose I hav to do this then on the Publisher and Subscriber database. Does
anybody has any experience with this?
TIA,
Stefan
You have to:
1. exclude this table from publication
2. do the required manipulations of table schema on publisher and
subscribers
3. add table back to publication.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message
news:uA3bA6LZEHA.728@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to remove clustering of the Primary Key and make a foreign key in
the
> table the clustered index. Can I do this with a replicated database? I
> suppose I hav to do this then on the Publisher and Subscriber database.
Does
> anybody has any experience with this?
> TIA,
> Stefan
>
|||Stefan,
when I tested this, I found it was possible in merge but not in
transactional replication. Using merge, you should be able to do it directly
on the publisher and using sp_addscriptexec on the subscribers. For
transactional, you'll need to drop then recreate the publication after
making the changes.
HTH,
Paul Ibison
|||I was able to do it by dropping the publication modifying the table and then
replicating it.
Not sure if this is what you want or not.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OBMjuFQZEHA.3112@.tk2msftngp13.phx.gbl...
> Stefan,
> when I tested this, I found it was possible in merge but not in
> transactional replication. Using merge, you should be able to do it
directly
> on the publisher and using sp_addscriptexec on the subscribers. For
> transactional, you'll need to drop then recreate the publication after
> making the changes.
> HTH,
> Paul Ibison
>