Transfering SQL database into Acess.
Problmes:
1. Identity column is not getting converted automitaclly into AutoNumber.
2. Default Value not retained.
is there any way out to get this.What version of SQL Server and Access are you using? Also what process of converting the data are you using??|||hi
i am using SQL server 2000, Access 2000.
nilesh
Showing posts with label identity. Show all posts
Showing posts with label identity. Show all posts
Saturday, February 25, 2012
Sunday, February 19, 2012
Data Integrity Question
I have the following table:
CREATE TABLE [transactions] (
[Transaction_ID] [int] IDENTITY (1, 1) NOT NULL ,
[CID] [int] NULL ,
[Effec_date] [smalldatetime] NULL ,
[Amount] [money] NULL ,
[Transaction_Type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Trans_code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Accuracy_disclaimer] [bit] NULL
CONSTRAINT [PK_transactions] PRIMARY KEY CLUSTERED
(
[Transaction_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
---
I want to prevent a record from being inserted where a record already exists
based on the following columns:
--CID
--Effec_Date
--Amount
In other words, if I have a record where CID = 1, Effec_Date = '04/01/2006'
and
Amount = 1.50, I want to prevent another record with these values from being
inserted. Would I use a muti column check constraint? What is the syntax?In a similar fashion as you have the PRIMARY KEY constraint, add a UNIQUE co
nstraint over those
three columns.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:0AFE3D9C-6934-44A8-9160-CEDE47D2C76A@.microsoft.com...
>I have the following table:
> CREATE TABLE [transactions] (
> [Transaction_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [CID] [int] NULL ,
> [Effec_date] [smalldatetime] NULL ,
> [Amount] [money] NULL ,
> [Transaction_Type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Trans_code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Accuracy_disclaimer] [bit] NULL
> CONSTRAINT [PK_transactions] PRIMARY KEY CLUSTERED
> (
> [Transaction_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ---
> I want to prevent a record from being inserted where a record already exis
ts
> based on the following columns:
> --CID
> --Effec_Date
> --Amount
> In other words, if I have a record where CID = 1, Effec_Date = '04/01/2006
'
> and
> Amount = 1.50, I want to prevent another record with these values from bei
ng
> inserted. Would I use a muti column check constraint? What is the syntax
?
>|||create a unique constraint on these 3 columns
create table t_1
(
col1 int,
col2 int,
col3 int
)
alter table t_1 add constraint UQ_col1_col2_col3 unique (col1,col2,col3)
go
"Eric" wrote:
> I have the following table:
> CREATE TABLE [transactions] (
> [Transaction_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [CID] [int] NULL ,
> [Effec_date] [smalldatetime] NULL ,
> [Amount] [money] NULL ,
> [Transaction_Type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Trans_code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Accuracy_disclaimer] [bit] NULL
> CONSTRAINT [PK_transactions] PRIMARY KEY CLUSTERED
> (
> [Transaction_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ---
> I want to prevent a record from being inserted where a record already exis
ts
> based on the following columns:
> --CID
> --Effec_Date
> --Amount
> In other words, if I have a record where CID = 1, Effec_Date = '04/01/2006
'
> and
> Amount = 1.50, I want to prevent another record with these values from bei
ng
> inserted. Would I use a muti column check constraint? What is the syntax
?
>|||"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:0AFE3D9C-6934-44A8-9160-CEDE47D2C76A@.microsoft.com...
>I have the following table:
> CREATE TABLE [transactions] (
> [Transaction_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [CID] [int] NULL ,
> [Effec_date] [smalldatetime] NULL ,
> [Amount] [money] NULL ,
> [Transaction_Type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Trans_code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Accuracy_disclaimer] [bit] NULL
> CONSTRAINT [PK_transactions] PRIMARY KEY CLUSTERED
> (
> [Transaction_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ---
> I want to prevent a record from being inserted where a record already
> exists
> based on the following columns:
> --CID
> --Effec_Date
> --Amount
> In other words, if I have a record where CID = 1, Effec_Date =
> '04/01/2006'
> and
> Amount = 1.50, I want to prevent another record with these values from
> being
> inserted. Would I use a muti column check constraint? What is the
> syntax?
>
This is a UNIQUE constraint. I suggest you make those columns non-nullable
first though:
ALTER TABLE transactions
ALTER COLUMN cid INTEGER NOT NULL;
ALTER TABLE transactions
ALTER COLUMN effec_date SMALLDATETIME NOT NULL;
ALTER TABLE transactions
ALTER COLUMN amount MONEY NOT NULL;
/* Add the unique constraint */
ALTER TABLE transactions
ADD CONSTRAINT ak1_transactions
UNIQUE (cid, effec_date, amount);
BTW. The MONEY datatype is typically a poor choice for amounts that will be
used in calculations. Using MONEY can lead to rounding errors. Use DECIMAL
instead.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Yes, noticed the rounding issue w/money. Thanks for the tip(s).
"David Portas" wrote:
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:0AFE3D9C-6934-44A8-9160-CEDE47D2C76A@.microsoft.com...
> This is a UNIQUE constraint. I suggest you make those columns non-nullable
> first though:
> ALTER TABLE transactions
> ALTER COLUMN cid INTEGER NOT NULL;
> ALTER TABLE transactions
> ALTER COLUMN effec_date SMALLDATETIME NOT NULL;
> ALTER TABLE transactions
> ALTER COLUMN amount MONEY NOT NULL;
> /* Add the unique constraint */
> ALTER TABLE transactions
> ADD CONSTRAINT ak1_transactions
> UNIQUE (cid, effec_date, amount);
> BTW. The MONEY datatype is typically a poor choice for amounts that will b
e
> used in calculations. Using MONEY can lead to rounding errors. Use DECIMAL
> instead.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||You actually do not have a table at all, so you can NEVER have data
integrity. IDENTITY cannot be a key by definition and alll the other
columns are NULL-able and have no constraints.
Literally every row is wrong according to the basic rules of RDBMS, ISO
rules and data modeling.
CREATE TABLE Transactions - vague name!!
(transaction_id INTEGER IDENTITY (1, 1) NULL - non-relatioanl ,
non-key!! Total crap
cid INTEGER NULL, nulls in an identifier? Duh!
effect_date DATETIME NULL, -- no default?
amount MONEY NULL, -- proprietary data type and bad name!!
transaction_type VARCHAR (25) NULL, -- variable length & ISO-11179
violation
trans_code NVARCHAR (50) NULL, --really? codes in Chinese?
accuracy_disclaimer BIT NULL --bits in SQL?
);
Please give me an example of a transaction_type that is CHAR(50); if
you can find one, we need to re-design this encoding, unless it is an
industry standard.
Why did you use MONEY? Do you spit on ANSI./ISO Standards or do you
want the math problems?
Why did you think that BIT is a SQL data type or valid way to program
once you get above the assembly language level?
I have to use this in a book. When I try to make up a bad example, I
keep correcting gross errors; you do not!!
.
based on the following columns:
--CID
--Effec_Date
--Amount <<
Then put them into a key or a UNIQUE constraint!! Duh! And learn that
a row is not anything like a record.
CREATE TABLE [transactions] (
[Transaction_ID] [int] IDENTITY (1, 1) NOT NULL ,
[CID] [int] NULL ,
[Effec_date] [smalldatetime] NULL ,
[Amount] [money] NULL ,
[Transaction_Type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Trans_code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Accuracy_disclaimer] [bit] NULL
CONSTRAINT [PK_transactions] PRIMARY KEY CLUSTERED
(
[Transaction_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
---
I want to prevent a record from being inserted where a record already exists
based on the following columns:
--CID
--Effec_Date
--Amount
In other words, if I have a record where CID = 1, Effec_Date = '04/01/2006'
and
Amount = 1.50, I want to prevent another record with these values from being
inserted. Would I use a muti column check constraint? What is the syntax?In a similar fashion as you have the PRIMARY KEY constraint, add a UNIQUE co
nstraint over those
three columns.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:0AFE3D9C-6934-44A8-9160-CEDE47D2C76A@.microsoft.com...
>I have the following table:
> CREATE TABLE [transactions] (
> [Transaction_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [CID] [int] NULL ,
> [Effec_date] [smalldatetime] NULL ,
> [Amount] [money] NULL ,
> [Transaction_Type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Trans_code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Accuracy_disclaimer] [bit] NULL
> CONSTRAINT [PK_transactions] PRIMARY KEY CLUSTERED
> (
> [Transaction_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ---
> I want to prevent a record from being inserted where a record already exis
ts
> based on the following columns:
> --CID
> --Effec_Date
> --Amount
> In other words, if I have a record where CID = 1, Effec_Date = '04/01/2006
'
> and
> Amount = 1.50, I want to prevent another record with these values from bei
ng
> inserted. Would I use a muti column check constraint? What is the syntax
?
>|||create a unique constraint on these 3 columns
create table t_1
(
col1 int,
col2 int,
col3 int
)
alter table t_1 add constraint UQ_col1_col2_col3 unique (col1,col2,col3)
go
"Eric" wrote:
> I have the following table:
> CREATE TABLE [transactions] (
> [Transaction_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [CID] [int] NULL ,
> [Effec_date] [smalldatetime] NULL ,
> [Amount] [money] NULL ,
> [Transaction_Type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Trans_code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Accuracy_disclaimer] [bit] NULL
> CONSTRAINT [PK_transactions] PRIMARY KEY CLUSTERED
> (
> [Transaction_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ---
> I want to prevent a record from being inserted where a record already exis
ts
> based on the following columns:
> --CID
> --Effec_Date
> --Amount
> In other words, if I have a record where CID = 1, Effec_Date = '04/01/2006
'
> and
> Amount = 1.50, I want to prevent another record with these values from bei
ng
> inserted. Would I use a muti column check constraint? What is the syntax
?
>|||"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:0AFE3D9C-6934-44A8-9160-CEDE47D2C76A@.microsoft.com...
>I have the following table:
> CREATE TABLE [transactions] (
> [Transaction_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [CID] [int] NULL ,
> [Effec_date] [smalldatetime] NULL ,
> [Amount] [money] NULL ,
> [Transaction_Type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Trans_code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Accuracy_disclaimer] [bit] NULL
> CONSTRAINT [PK_transactions] PRIMARY KEY CLUSTERED
> (
> [Transaction_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ---
> I want to prevent a record from being inserted where a record already
> exists
> based on the following columns:
> --CID
> --Effec_Date
> --Amount
> In other words, if I have a record where CID = 1, Effec_Date =
> '04/01/2006'
> and
> Amount = 1.50, I want to prevent another record with these values from
> being
> inserted. Would I use a muti column check constraint? What is the
> syntax?
>
This is a UNIQUE constraint. I suggest you make those columns non-nullable
first though:
ALTER TABLE transactions
ALTER COLUMN cid INTEGER NOT NULL;
ALTER TABLE transactions
ALTER COLUMN effec_date SMALLDATETIME NOT NULL;
ALTER TABLE transactions
ALTER COLUMN amount MONEY NOT NULL;
/* Add the unique constraint */
ALTER TABLE transactions
ADD CONSTRAINT ak1_transactions
UNIQUE (cid, effec_date, amount);
BTW. The MONEY datatype is typically a poor choice for amounts that will be
used in calculations. Using MONEY can lead to rounding errors. Use DECIMAL
instead.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Yes, noticed the rounding issue w/money. Thanks for the tip(s).
"David Portas" wrote:
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:0AFE3D9C-6934-44A8-9160-CEDE47D2C76A@.microsoft.com...
> This is a UNIQUE constraint. I suggest you make those columns non-nullable
> first though:
> ALTER TABLE transactions
> ALTER COLUMN cid INTEGER NOT NULL;
> ALTER TABLE transactions
> ALTER COLUMN effec_date SMALLDATETIME NOT NULL;
> ALTER TABLE transactions
> ALTER COLUMN amount MONEY NOT NULL;
> /* Add the unique constraint */
> ALTER TABLE transactions
> ADD CONSTRAINT ak1_transactions
> UNIQUE (cid, effec_date, amount);
> BTW. The MONEY datatype is typically a poor choice for amounts that will b
e
> used in calculations. Using MONEY can lead to rounding errors. Use DECIMAL
> instead.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||You actually do not have a table at all, so you can NEVER have data
integrity. IDENTITY cannot be a key by definition and alll the other
columns are NULL-able and have no constraints.
Literally every row is wrong according to the basic rules of RDBMS, ISO
rules and data modeling.
CREATE TABLE Transactions - vague name!!
(transaction_id INTEGER IDENTITY (1, 1) NULL - non-relatioanl ,
non-key!! Total crap
cid INTEGER NULL, nulls in an identifier? Duh!
effect_date DATETIME NULL, -- no default?
amount MONEY NULL, -- proprietary data type and bad name!!
transaction_type VARCHAR (25) NULL, -- variable length & ISO-11179
violation
trans_code NVARCHAR (50) NULL, --really? codes in Chinese?
accuracy_disclaimer BIT NULL --bits in SQL?
);
Please give me an example of a transaction_type that is CHAR(50); if
you can find one, we need to re-design this encoding, unless it is an
industry standard.
Why did you use MONEY? Do you spit on ANSI./ISO Standards or do you
want the math problems?
Why did you think that BIT is a SQL data type or valid way to program
once you get above the assembly language level?
I have to use this in a book. When I try to make up a bad example, I
keep correcting gross errors; you do not!!
.
based on the following columns:
--CID
--Effec_Date
--Amount <<
Then put them into a key or a UNIQUE constraint!! Duh! And learn that
a row is not anything like a record.
Data integrity error
Hi All,
This is a wierd one... I have a table for which I wish to remove the
IDENTITY attribute for one of the columns. To do this I intended to
create a new column, copy the data from the identity column to the new
column, delete the identity column and then rename the new column...
All pretty straight forward stuff.
However, when trying to do step 2 (up, I get a recursive "Data
integrity error" message appearing in my results window (there are only
15 rows in the table and I get approximately 20K "DIE" messages a
minute)
The script I am using is as follows;
ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
UPDATE tToBeAltered SET [new_id] = [id]
ALTER TABLE tToBeAltered DROP COLUMN [id]
EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
Column names have been changed to protect the innocent ;)
>From where I am sitting this all looks pretty generic, and I can't see
why my script is kicking up such a fuss...
Merry christmas to all,
Best, Marki would do it otherwise:
why dont you try to create a new table with all needed indexes, identity, etc'
and then use the select into old table to new table. if your table consist
only 15 rows - may be you should consider rebuid the index after you do the
select into. it worth a try...
tomer
"Cuperman" wrote:
> Hi All,
> This is a wierd one... I have a table for which I wish to remove the
> IDENTITY attribute for one of the columns. To do this I intended to
> create a new column, copy the data from the identity column to the new
> column, delete the identity column and then rename the new column...
> All pretty straight forward stuff.
> However, when trying to do step 2 (up, I get a recursive "Data
> integrity error" message appearing in my results window (there are only
> 15 rows in the table and I get approximately 20K "DIE" messages a
> minute)
> The script I am using is as follows;
> ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
> UPDATE tToBeAltered SET [new_id] = [id]
> ALTER TABLE tToBeAltered DROP COLUMN [id]
> EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
> Column names have been changed to protect the innocent ;)
> >From where I am sitting this all looks pretty generic, and I can't see
> why my script is kicking up such a fuss...
> Merry christmas to all,
> Best, Mark
>|||... and also do DBCC CHECKDB on the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tomer" <tomer@.discussions.microsoft.com> wrote in message
news:9CDA2C47-ADF2-4AF9-AAA0-2730F5D61DAB@.microsoft.com...
>i would do it otherwise:
> why dont you try to create a new table with all needed indexes, identity, etc'
> and then use the select into old table to new table. if your table consist
> only 15 rows - may be you should consider rebuid the index after you do the
> select into. it worth a try...
> tomer
> "Cuperman" wrote:
>> Hi All,
>> This is a wierd one... I have a table for which I wish to remove the
>> IDENTITY attribute for one of the columns. To do this I intended to
>> create a new column, copy the data from the identity column to the new
>> column, delete the identity column and then rename the new column...
>> All pretty straight forward stuff.
>> However, when trying to do step 2 (up, I get a recursive "Data
>> integrity error" message appearing in my results window (there are only
>> 15 rows in the table and I get approximately 20K "DIE" messages a
>> minute)
>> The script I am using is as follows;
>> ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
>> UPDATE tToBeAltered SET [new_id] = [id]
>> ALTER TABLE tToBeAltered DROP COLUMN [id]
>> EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
>> Column names have been changed to protect the innocent ;)
>> >From where I am sitting this all looks pretty generic, and I can't see
>> why my script is kicking up such a fuss...
>> Merry christmas to all,
>> Best, Mark
>>|||Hi Tomer,
Thanks for taking the time to respond.
I actually have more than one table that will be modified, so was
scripting a solution that would do all tables. Most are working OK
(where Identity is Numeric or Int), but this one table is causing a
real problem.
At the moment I am working on a test DB, hence 15 rows, but when we get
to production environment the table will contain millions of rows.
Thanks for suggesting the option, but I think a full table recreate
would be too dangerous (missing constraints here or there) and more
importantly too slow for my requirements here.
I guess I was hoping that there is a known configuration issue / bug
that I could simply tweak to fix this. Something seems fundamentally
wrong here.
Best, Mark|||** Update: There is even a problem with:
ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
UPDATE tToBeAltered SET [new_id] = 1
This throws the same D.I.E. error... is this a corrupt DB?
Thanks, Mark|||Thanks for taking the time to respond Tibor.
results from DBCC CHECKDB
...
DBCC results for 'tToBeAltered'.
There are 13 rows in 2 pages for object 'tToBeAltered'.
...
CHECKDB found 0 allocation errors and 0 consistency errors in database
'TestDB'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
...
This seems OK to me. Doesn't suggest anything to worry about...
Best, Mark|||Hi Mark,
It sounds pretty weird. but may be you should:
1. to run dbcc indexdefrag. it will not cause any locks - you can run it
online.
2. if you work on a dev enviorment may be you should take the last backup
of this db and restore it and then try to do what you are doing with the id
column.
thx,
Tomer
"Cuperman" wrote:
> ** Update: There is even a problem with:
> ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
> UPDATE tToBeAltered SET [new_id] = 1
> This throws the same D.I.E. error... is this a corrupt DB?
> Thanks, Mark
>|||I hate it when a thread just runs out of steam and no solution is ever
given ...luckily I have found the cause of my problem... there was an
update trigger that was causing problems...
To fix this all I needed was:
/*************************************************************/
ALTER TABLE tToBeAltered DISABLE TRIGGER ALL
GO
ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
UPDATE tToBeAltered SET [new_id] = [id]
ALTER TABLE tToBeAltered DROP COLUMN [id]
EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
GO
ALTER TABLE tToBeAltered ENABLE TRIGGER ALL
GO
/*************************************************************/
Thanks to all who offered assistance.
Best Regards,
Mark
This is a wierd one... I have a table for which I wish to remove the
IDENTITY attribute for one of the columns. To do this I intended to
create a new column, copy the data from the identity column to the new
column, delete the identity column and then rename the new column...
All pretty straight forward stuff.
However, when trying to do step 2 (up, I get a recursive "Data
integrity error" message appearing in my results window (there are only
15 rows in the table and I get approximately 20K "DIE" messages a
minute)
The script I am using is as follows;
ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
UPDATE tToBeAltered SET [new_id] = [id]
ALTER TABLE tToBeAltered DROP COLUMN [id]
EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
Column names have been changed to protect the innocent ;)
>From where I am sitting this all looks pretty generic, and I can't see
why my script is kicking up such a fuss...
Merry christmas to all,
Best, Marki would do it otherwise:
why dont you try to create a new table with all needed indexes, identity, etc'
and then use the select into old table to new table. if your table consist
only 15 rows - may be you should consider rebuid the index after you do the
select into. it worth a try...
tomer
"Cuperman" wrote:
> Hi All,
> This is a wierd one... I have a table for which I wish to remove the
> IDENTITY attribute for one of the columns. To do this I intended to
> create a new column, copy the data from the identity column to the new
> column, delete the identity column and then rename the new column...
> All pretty straight forward stuff.
> However, when trying to do step 2 (up, I get a recursive "Data
> integrity error" message appearing in my results window (there are only
> 15 rows in the table and I get approximately 20K "DIE" messages a
> minute)
> The script I am using is as follows;
> ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
> UPDATE tToBeAltered SET [new_id] = [id]
> ALTER TABLE tToBeAltered DROP COLUMN [id]
> EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
> Column names have been changed to protect the innocent ;)
> >From where I am sitting this all looks pretty generic, and I can't see
> why my script is kicking up such a fuss...
> Merry christmas to all,
> Best, Mark
>|||... and also do DBCC CHECKDB on the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tomer" <tomer@.discussions.microsoft.com> wrote in message
news:9CDA2C47-ADF2-4AF9-AAA0-2730F5D61DAB@.microsoft.com...
>i would do it otherwise:
> why dont you try to create a new table with all needed indexes, identity, etc'
> and then use the select into old table to new table. if your table consist
> only 15 rows - may be you should consider rebuid the index after you do the
> select into. it worth a try...
> tomer
> "Cuperman" wrote:
>> Hi All,
>> This is a wierd one... I have a table for which I wish to remove the
>> IDENTITY attribute for one of the columns. To do this I intended to
>> create a new column, copy the data from the identity column to the new
>> column, delete the identity column and then rename the new column...
>> All pretty straight forward stuff.
>> However, when trying to do step 2 (up, I get a recursive "Data
>> integrity error" message appearing in my results window (there are only
>> 15 rows in the table and I get approximately 20K "DIE" messages a
>> minute)
>> The script I am using is as follows;
>> ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
>> UPDATE tToBeAltered SET [new_id] = [id]
>> ALTER TABLE tToBeAltered DROP COLUMN [id]
>> EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
>> Column names have been changed to protect the innocent ;)
>> >From where I am sitting this all looks pretty generic, and I can't see
>> why my script is kicking up such a fuss...
>> Merry christmas to all,
>> Best, Mark
>>|||Hi Tomer,
Thanks for taking the time to respond.
I actually have more than one table that will be modified, so was
scripting a solution that would do all tables. Most are working OK
(where Identity is Numeric or Int), but this one table is causing a
real problem.
At the moment I am working on a test DB, hence 15 rows, but when we get
to production environment the table will contain millions of rows.
Thanks for suggesting the option, but I think a full table recreate
would be too dangerous (missing constraints here or there) and more
importantly too slow for my requirements here.
I guess I was hoping that there is a known configuration issue / bug
that I could simply tweak to fix this. Something seems fundamentally
wrong here.
Best, Mark|||** Update: There is even a problem with:
ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
UPDATE tToBeAltered SET [new_id] = 1
This throws the same D.I.E. error... is this a corrupt DB?
Thanks, Mark|||Thanks for taking the time to respond Tibor.
results from DBCC CHECKDB
...
DBCC results for 'tToBeAltered'.
There are 13 rows in 2 pages for object 'tToBeAltered'.
...
CHECKDB found 0 allocation errors and 0 consistency errors in database
'TestDB'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
...
This seems OK to me. Doesn't suggest anything to worry about...
Best, Mark|||Hi Mark,
It sounds pretty weird. but may be you should:
1. to run dbcc indexdefrag. it will not cause any locks - you can run it
online.
2. if you work on a dev enviorment may be you should take the last backup
of this db and restore it and then try to do what you are doing with the id
column.
thx,
Tomer
"Cuperman" wrote:
> ** Update: There is even a problem with:
> ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
> UPDATE tToBeAltered SET [new_id] = 1
> This throws the same D.I.E. error... is this a corrupt DB?
> Thanks, Mark
>|||I hate it when a thread just runs out of steam and no solution is ever
given ...luckily I have found the cause of my problem... there was an
update trigger that was causing problems...
To fix this all I needed was:
/*************************************************************/
ALTER TABLE tToBeAltered DISABLE TRIGGER ALL
GO
ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
UPDATE tToBeAltered SET [new_id] = [id]
ALTER TABLE tToBeAltered DROP COLUMN [id]
EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
GO
ALTER TABLE tToBeAltered ENABLE TRIGGER ALL
GO
/*************************************************************/
Thanks to all who offered assistance.
Best Regards,
Mark
Subscribe to:
Posts (Atom)