Change Identify/Primary Key Column Data Type?

Hi ...

I've

taken over a project where the tables were created with

identity/primary key columns of type DECIMAL(12,0). The latest addition

to the project is to replicate data down to Pocket PC applications.

Replication requires that identify/primary key columns be of type

INT/BIGINT.

I've attempted to ALTER TABLE xxx ALTER COLUMN yyy

BIGINT; and it fails. Failed due to all the foreign key constraints that have been created. While it didn't give any error messages associated with the the field being the PRIMARY KEY - I'm assuming I may get that error as well.

I then did an ALTER TABLE xxx NOCHECK CONSTRAINT

ALL; for every table in the system to disable checking of foreign keys

and then attempted to alter the column to a bigint and it still failed.

How

can I change the column from Decimal to BIGINT - or do I have to create

new tables, import all the data, get rid of the original tables? Please

tell me I don't have to do the latter.

Thanks ...Unfortunately the only way is to recreate the table or drop & recreate the column. This would require removing the existing constraints (PKs, FKs) and recreating them. Alternatively, you can create a view on the table(s) that does the conversion and replicate those. But this may or may not work depending on your replication scheme and you will have to ask in the Replication newsgroup.

Change Clustered Index to Non-Clustered Index

When I create a table in Enterprise Manager, the Primary
Key is created as a Clustered Index (As there is no other
Clustered Index exists in that table).
However, when I attempt to change the Clustered Index to a
non-Clustered Index, it says that "Cannot convert a
Clustered Index to an nonclustered index using the
DROP_EXISTING Option".
I would like to know
1) Is it possible to change the Clustered Index to Non-
clustered Index in Enterprise Manager OR we have to change
it in Query Analyzer ?
2) When we create a table in Enterprise Manager, can we
specify a column as a Clustered Index (Instead of creating
Clustered Index in the Primary Key)?
ThanksRoger
DROP TABLE TEST
CREATE TABLE TEST
(
COL INT NOT NULL PRIMARY KEY
)
--Run this sp to make sure you have clustered unique index
SP_HELPINDEX TEST
--other way to create clustered index
CREATE TABLE TEST (COL INT)
GO
CREATE UNIQUE CLUSTERED INDEX Idx1 ON TEST(COL)
--change clustered index to non_clustered
CREATE UNIQUE CLUSTERED INDEX Idx1 ON TEST(COL)
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'Idx1')
DROP INDEX TEST.Idx1
CREATE UNIQUE NONCLUSTERED INDEX Idx1 ON TEST(COL)
SP_HELPINDEX TEST
"Roger Lee" <rogerlee@.nospam.com> wrote in message
news:04e701c35a5b$1667a1c0$a101280a@.phx.gbl...
> When I create a table in Enterprise Manager, the Primary
> Key is created as a Clustered Index (As there is no other
> Clustered Index exists in that table).
> However, when I attempt to change the Clustered Index to a
> non-Clustered Index, it says that "Cannot convert a
> Clustered Index to an nonclustered index using the
> DROP_EXISTING Option".
> I would like to know
> 1) Is it possible to change the Clustered Index to Non-
> clustered Index in Enterprise Manager OR we have to change
> it in Query Analyzer ?
> 2) When we create a table in Enterprise Manager, can we
> specify a column as a Clustered Index (Instead of creating
> Clustered Index in the Primary Key)?
> Thanks|||Roger
You can change a clustered index to non-clustered in EM.
How did you try to do it? Use the properties window in the
design table pane. (it generates a drop index and create
index stement for you)
You can create a column or indeed multi-column clustered
index on any data you like. Again using EM, the design
table is the easiest way to do it.
Hope this helps.
John|||Dear John,
Does "Design Table Pane" mean the Database Diagram ?
I create a new database diagram with that table and I am
able to chagne the Clustered Index to Non-Clustered Index.
Thanks|||Roger
No not the database diagram.
In EM open up databases on your server. Then open the
database you want. Click on tables. In the pane on the
right, right click on the table you are interested in and
choose 'design table'.
One in the design table view you can open index properties
and there you can do a variety of thing with your indexes
including creating new ones, moving filegroups, make an
index clustered (as long as there is not already one) or
make it unclustered if it already is clustered.
When you exit the design table view it will ask if you
want to save the changes, say yes if you want the changes
you have made to take effect.
Regards
John

change clustered index

Hi,
I have a table with 1.5 million records. It has the primary key as the clustered index now, I would like to change the create_dt as clustered index. When I do reindex, will it block the table? How long will it take? Can I do it in EM from the Design able? ThanksYes you can do it from EM and yes it will block all access to the table for
the entire duration of the process. How long this takes depends mainly on
your hardware configuration.
--
Andrew J. Kelly
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:A0F7FA2F-77F0-486B-99C2-31B2DD2E1409@.microsoft.com...
> Hi,
> I have a table with 1.5 million records. It has the primary key as the
clustered index now, I would like to change the create_dt as clustered
index. When I do reindex, will it block the table? How long will it take?
Can I do it in EM from the Design able? Thanks|||Hi,
This operation has to be done when there is no activity in this table.
Execute the below statements from Query Analyzer, Replace the table name ,
constraint name and column name with yours.
ALTER TABLE dbo.tablename
DROP CONSTRAINT Pkey_constraintname
go
ALTER TABLE dbo.tablename ADD CONSTRAINT
Pkey_constraintname PRIMARY KEY NONCLUSTERED
(
columnname
)
go
Create Clustered index IDX_name on tablename(newcolumn)
Thanks
Hari
MCDBA
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:A0F7FA2F-77F0-486B-99C2-31B2DD2E1409@.microsoft.com...
> Hi,
> I have a table with 1.5 million records. It has the primary key as the
clustered index now, I would like to change the create_dt as clustered
index. When I do reindex, will it block the table? How long will it take?
Can I do it in EM from the Design able? Thanks

change clustered index

Hi,
I have a table with 1.5 million records. It has the primary key as the clust
ered index now, I would like to change the create_dt as clustered index. Whe
n I do reindex, will it block the table? How long will it take? Can I do it
in EM from the Design able?
ThanksYes you can do it from EM and yes it will block all access to the table for
the entire duration of the process. How long this takes depends mainly on
your hardware configuration.
Andrew J. Kelly
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:A0F7FA2F-77F0-486B-99C2-31B2DD2E1409@.microsoft.com...
> Hi,
> I have a table with 1.5 million records. It has the primary key as the
clustered index now, I would like to change the create_dt as clustered
index. When I do reindex, will it block the table? How long will it take?
Can I do it in EM from the Design able? Thanks|||Hi,
This operation has to be done when there is no activity in this table.
Execute the below statements from Query Analyzer, Replace the table name ,
constraint name and column name with yours.
ALTER TABLE dbo.tablename
DROP CONSTRAINT Pkey_constraintname
go
ALTER TABLE dbo.tablename ADD CONSTRAINT
Pkey_constraintname PRIMARY KEY NONCLUSTERED
(
columnname
)
go
Create Clustered index IDX_name on tablename(newcolumn)
Thanks
Hari
MCDBA
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:A0F7FA2F-77F0-486B-99C2-31B2DD2E1409@.microsoft.com...
> Hi,
> I have a table with 1.5 million records. It has the primary key as the
clustered index now, I would like to change the create_dt as clustered
index. When I do reindex, will it block the table? How long will it take?
Can I do it in EM from the Design able? Thanks

Catching Primary Key Violation on insert error

I've read a few different articticles wrt how the handle this error gracefully.

I am thinking of wrapping my sql insert statement in a try catch and have the catch be something like

IF ( e.ToString() LIKE '%System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_PS_HR_Hrs'. Cannot insert duplicate key in object %')

{
lable1.text = "Sorry, you already have reported hours for that day, please select anothe rdate"

}

Is there a better way?

TIA

Dan

Find out the error number that your Exception is throwing, then trap for that particular error number. Your error might be number 2627 or 2601.

|||

Bummer-

I'm away from my asp.net enviormentt.

Um...

how do I do it?

int errorcode = ex.ToInt

if (errorcode = 123) then...

TIA

Dan

(Can't stop thinknkng about this stuff, I think I need professional help!)

|||

Catch the SqlException first, then display your message based on the returned number.

try{}catch (SqlException ex){if (ex.Number.Equals(2627)){// Display your error here}}

|||

Thanks Ed!

(Cute Kid!)