Sql Server Alignment problem: following

Showing posts with label following. Show all posts

Thursday, March 29, 2012

Alternate Rows in Matrix

Hi All,
I need the alternate bg colors of the rows in the matrix to be
white smoke and grey.
when i used the following expression it throws a error.
=iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
The background color expression for the textbox â'ProfCountâ' has a scope
parameter that is not valid for RunningValue, RowNumber or Previous. The
scope parameter must be set to a string constant that is equal to the name of
a containing group within the matrix â'matrix1â'.
What i need to do get the alternate coloring in matrix,
Thanks in advance....I beleive I have posted an example called Matrix.Greenbar on
www.msbicentral.com
Look under Downloads, reporting services, RDL... There are several Matrix
examples there which will probably help you get started.
Hope this helps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:B95FDE7C-7DAA-4327-AA61-4C1045DDEABA@.microsoft.com...
> Hi All,
> I need the alternate bg colors of the rows in the matrix to be
> white smoke and grey.
> when i used the following expression it throws a error.
> =iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
> The background color expression for the textbox 'ProfCount' has a scope
> parameter that is not valid for RunningValue, RowNumber or Previous. The
> scope parameter must be set to a string constant that is equal to the name
of
> a containing group within the matrix 'matrix1'.
> What i need to do get the alternate coloring in matrix,
> Thanks in advance....sql

Sunday, March 25, 2012

Altering a column which has an index defined on it

Hello,

I'm trying the following test (which works like a charm on Oracle)

create table x1(c1 numeric(10), c2 numeric(5,1))
create index x1_c2_idx on x1(c2)
alter table x1 alter column c2 numeric(9,1)

I get the following error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'x1_c2_idx' is dependent on column 'c2'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN c2 failed because one or more objects access this column.

Is there a way to alter the column WITHOUT dropping the index ?

Regards,

Tal Olier
otal@.mercury.co.ilNo, you must drop the index first.|||Originally posted by Paul Young
No, you must drop the index first.

Thanks.

Alter View permission?

MSDN says the following at
http://msdn.microsoft.com/library/d...rl=/library/en-
us/tsqlref/ts_aa-az_2gtz.asp:
"ALTER VIEW permissions default to members of the db_owner and
db_ddladmin fixed database roles, and to the view owner. These
permissions are not transferable.
To alter a view, the user must have ALTER VIEW permission along with
SELECT permission on the tables, views, and table-valued functions being
referenced in the view, ..."
The BOL documentation for GRANT doesn't show ALTER VIEW as a permissible
statement type to grant permissions on. Trying to GRANT ALTER VIEW
doesn't work. How can I give a user ALTER VIEW permission? Does "not
transferable" mean I can't?
Thanks.
David WalkerYou can add the user to the db_owner or db_ddladmin fixed database roles.
AMB
"DWalker" wrote:

> MSDN says the following at
> http://msdn.microsoft.com/library/d...rl=/library/en-
> us/tsqlref/ts_aa-az_2gtz.asp:
> "ALTER VIEW permissions default to members of the db_owner and
> db_ddladmin fixed database roles, and to the view owner. These
> permissions are not transferable.
> To alter a view, the user must have ALTER VIEW permission along with
> SELECT permission on the tables, views, and table-valued functions being
> referenced in the view, ..."
> The BOL documentation for GRANT doesn't show ALTER VIEW as a permissible
> statement type to grant permissions on. Trying to GRANT ALTER VIEW
> doesn't work. How can I give a user ALTER VIEW permission? Does "not
> transferable" mean I can't?
> Thanks.
> David Walker
>|||I ran across the DDLAdmin role, but BOL makes it sound like you can
grant ALTER VIEW permissions. Maybe I'm misreading it -- I suppose
those permissions are only given to members of db_owner and db_ddladmin,
and they can't be granted to anyone else.
The phrase "ALTER VIEW permissions default to members of the db_owner
and db_ddladmin fixed database roles" should have "default to" replaced
by "are restricted to" if that's the case.
It would be nice to be able to grant ALTER VIEW permission on one view
to one user or role.
Thanks, AlejandroMesa.
David Walker
"examnotes"
<AlejandroMesa@.discussions.microsoft.com> wrote in
news:668F79B3-3335-4BC3-860F-67F467C12CEF@.microsoft.com:

> You can add the user to the db_owner or db_ddladmin fixed database
> roles.
>
> AMB
> "DWalker" wrote:
>
>|||You cannot give a user (that is not member of db_owner and db_ddladmin
fixed dabase roles) the permission to alter a specific view (that he
does not own). This is exactly what "not transferable" means (at least,
this is what it means to me).
As I see it, you have the following alternatives:
a) make the user a member of db_ddladmin role: this will enable him to
make any modification to the objects in the database, including
creating and deleting other objects (tables, views, procedures, etc)
b) grant the user the "CREATE VIEW" permission; the views that are
created by him will be will be owned by him, and so he will be able to
modify them
c) create the views in his name, by prefixing them with his user name
instead of "dbo" (without granting him the "CREATE VIEW" permission).
He will be able to modify those views (and to delete them), but he
won't be able to create new views (or other objects).
The problem with the b) and c) alternatives is that only that user will
be able to access those views by specifying only the view's name (the
other users must prefix the view's name with the user name).
Razvan|||It would be NICE if I could grant a user permission to alter a specific
view, but we'll take what we can get...
I'll probably add the user to the ddladmin role, although that's more
power than I would like to give. But the other choices aren't great
either. Thanks.
David
"Razvan Socol" <rsocol@.gmail.com> wrote in
news:1112639463.986674.297650@.f14g2000cwb.googlegroups.com:

> You cannot give a user (that is not member of db_owner and db_ddladmin
> fixed dabase roles) the permission to alter a specific view (that he
> does not own). This is exactly what "not transferable" means (at
> least, this is what it means to me).
> As I see it, you have the following alternatives:
> a) make the user a member of db_ddladmin role: this will enable him to
> make any modification to the objects in the database, including
> creating and deleting other objects (tables, views, procedures, etc)
> b) grant the user the "CREATE VIEW" permission; the views that are
> created by him will be will be owned by him, and so he will be able to
> modify them
> c) create the views in his name, by prefixing them with his user name
> instead of "dbo" (without granting him the "CREATE VIEW" permission).
> He will be able to modify those views (and to delete them), but he
> won't be able to create new views (or other objects).
> The problem with the b) and c) alternatives is that only that user
> will be able to access those views by specifying only the view's name
> (the other users must prefix the view's name with the user name).
> Razvan
>

ALTER TABLE/COLUMN syntax

Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
AndrisSimply add the default with an ALTER TABLE:
ALTER TABLE firmNoliktava_test
ADD CONSTRAINT DF1_firmNoliktava_test
DEFAULT 1 FOR valstsID
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Andris" <spameris@.gmail.com> wrote in message
news:eY4DCdHiGHA.3956@.TK2MSFTNGP02.phx.gbl...
Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
Andris|||On Mon, 05 Jun 2006 11:08:02 +0300, Andris wrote:
>Hi!
>I want a add default value to existing column with int type with
>following syntax:
>ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
>but got error
>Msg 156, Level 15, State 1, Line 2
>Incorrect syntax near the keyword 'SET'.
>Server SQL 2005 x64, in server Help Contents i see example
>ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
>Corporation'
>
>What i do wrong ?
Hi Andris,
The example you have seen is not for SQL Server, but for SQL Server
Mobile edition. There are many syntax difference between "normal" SQL
Server and the mobile version. I've been tricked by this myself quite a
few times already - just remember to always check the heading of the
subject in Books Online to check if you're looking at a Mobile or a
T-SQL subject.
--
Hugo Kornelis, SQL Server MVP

Thursday, March 22, 2012

Alter table weird bug?

I have created the following test SQL code to illustrate a real
problem I have with some SQL code.

CREATE TABLE JCTable ( CustomerName varchar(50) )
ALTER TABLE JCTable ADD CustomerNo int
INSERT INTO JCTable ( CustomerName , CustomerNo ) VALUES ( 'Jon Combe'
, 1 )
INSERT INTO JCTable ( CustomerName , CustomerNo ) VALUES ( 'Bill
Gates' , 1 )
UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
SELECT * FROM JCTable

When I run this SQL via the query analyser I get the errors:-

Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'CustomerNo'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'CustomerNo'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'CustomerNo'.

It appears the SQL Server is trying to "pre-parse" the query and
hasn't picked up on the ALTER TABLE line that adds this column and so
complains that it doesn't exist. However it doesn't end there.

If I then run this query a line at a time by highlighting each line in
the query analyser and running it all works (not unexpected). However
then dropping the table and then running the full SQL code once more
(I.E. not highlighting each line), it then works as expected. I assume
it was somehow remembering "state" in my session so closed and
re-started the Query Analyser with the same result that the code does
now work.

However changing the table name to something new brings back the
errors once more. Can anyone explain what is going on here? I am
dropping the table before re-running the code each time.

I'm using SQL Server 2000 if that makes a difference.

Thanks.
Jon.Not a bug. As you rightly suspected, SQL Server tries to validate your
code first by attempting to resolve any column or object references to
existing objects and columns. If the table doesn't exist at compile time
then the resolution of that table's columns is deferred until the
statement executes. However, if the table exists then you will receive
an error if the referenced columns don't also exist. A workaround is to
put a GO batch separator after the ALTER TABLE statement so that the
second batch will be compiled independently.

For this reason among others it is good practice always to separate DDL
(Data Definition Language, such as CREATE and ALTER table statements)
and DML (Data Manipulation Language, such as SELECT, UPDATE, INSERT,
DELETE statements). I would create separate scripts for your DDL and DML
statements.

--
David Portas
SQL Server MVP
--

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

> Not a bug. As you rightly suspected, SQL Server tries to validate your
> code first by attempting to resolve any column or object references to
> existing objects and columns. If the table doesn't exist at compile time
> then the resolution of that table's columns is deferred until the
> statement executes.

David,

Thanks, this does make sense however the table in my statement did not exist
at compile time (otherwise the create table line would fail), so that is
why I cannot understand why I get that message.

Thanks.
Jon.|||Jon Combe (jcombe@.acxiom.co.uk) writes:
> Thanks, this does make sense however the table in my statement did not
> exist at compile time (otherwise the create table line would fail), so
> that is why I cannot understand why I get that message.

Your batch gets compiled several times. First you have:

CREATE TABLE JCTable ( CustomerName varchar(50) )
ALTER TABLE JCTable ADD CustomerNo int
INSERT INTO JCTable ( CustomerName , CustomerNo )
VALUES ( 'Jon Combe' , 1 )
INSERT INTO JCTable ( CustomerName , CustomerNo )
VALUES ( 'Bill Gates' , 1 )
UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
SELECT * FROM JCTable

On the first compile, all but the first statement is deferred. Once the
table has been created, SQL Server hits the ALTER TABLE, finds that the
statement is deferred, and recompiles the batch. This time, all statements
are scrutinized, since once all tables in a query exist, SQL Server per-
form full checks on the query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:

> Your batch gets compiled several times. First you have:
> CREATE TABLE JCTable ( CustomerName varchar(50) )
> ALTER TABLE JCTable ADD CustomerNo int
> INSERT INTO JCTable ( CustomerName , CustomerNo )
> VALUES ( 'Jon Combe' , 1 )
> INSERT INTO JCTable ( CustomerName , CustomerNo )
> VALUES ( 'Bill Gates' , 1 )
> UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
> SELECT * FROM JCTable
> On the first compile, all but the first statement is deferred. Once the
> table has been created, SQL Server hits the ALTER TABLE, finds that the
> statement is deferred, and recompiles the batch. This time, all statements
> are scrutinized, since once all tables in a query exist, SQL Server per-
> form full checks on the query.

Thanks Erland,

Why doesn't it spot that I've added a column and then either defer the last
three statements again, or recognise that the column I'm using does now
exist? I'd expect that sort of behaviour from it although I take the point
made earlier that it's best to split the statements with a GO between them.

Also given the way it is compiling the code it doesn't explain why if I run
these statements one line at a time, then drop the table, reload Query
Analyser and re-run this full batch of code it works yet running the full
batch before the table has ever existed generates an error. That just seems
weird, so if anyone can explain why I'd be interested to hear it! The
database should be in the same state in both cases, but as the behaviour is
different it can't be.

Jon.|||Jon Combe wrote:
> Erland Sommarskog wrote:
>
>>Your batch gets compiled several times. First you have:
>>
>> CREATE TABLE JCTable ( CustomerName varchar(50) )
>> ALTER TABLE JCTable ADD CustomerNo int
>> INSERT INTO JCTable ( CustomerName , CustomerNo )
>> VALUES ( 'Jon Combe' , 1 )
>> INSERT INTO JCTable ( CustomerName , CustomerNo )
>> VALUES ( 'Bill Gates' , 1 )
>> UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
>> SELECT * FROM JCTable
>>
>>On the first compile, all but the first statement is deferred. Once the
>>table has been created, SQL Server hits the ALTER TABLE, finds that the
>>statement is deferred, and recompiles the batch. This time, all statements
>>are scrutinized, since once all tables in a query exist, SQL Server per-
>>form full checks on the query.
>
> Thanks Erland,
> Why doesn't it spot that I've added a column and then either defer the last
> three statements again, or recognise that the column I'm using does now
> exist? I'd expect that sort of behaviour from it although I take the point
> made earlier that it's best to split the statements with a GO between them.
> Also given the way it is compiling the code it doesn't explain why if I run
> these statements one line at a time, then drop the table, reload Query
> Analyser and re-run this full batch of code it works yet running the full
> batch before the table has ever existed generates an error. That just seems
> weird, so if anyone can explain why I'd be interested to hear it! The
> database should be in the same state in both cases, but as the behaviour is
> different it can't be.
> Jon.

I have to confess that this behaviour, if it is "normal", is surprising
to me. Is this the way the product is expected to behave?

Thanks.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)|||Jon Combe (jcombe@.acxiom.co.uk) writes:
> Why doesn't it spot that I've added a column

No, you haven't added a column. You get the error the table has been
created, but the ALTER TABLE statement has not been executed. Since the
ALTER TABLE statement was deferred, SQL Server recompiles the batch,
and it recompiles the batch, because that the lowest granularity for
compilation in SQL 2000. And since at this point the columns does not
exist, the compilation fails.

> and then either defer the last three statements again,

SQL Server could defer compilation because of unknown columns too, but it
has quite some ramifications, and I am very happy that unknown columns is
reason for deferral. It is bad as it is. To wit, when you create a stored
procedure, you want to be alerted if you have misspelled a table name of a
column name. Due to deferred name resolution, you don't get alerts for
misspelled table names, but since SQL Server checks the query once all
tables are there, you do at least sometimes get alerts about misspelling
column names. (And in our in-house load tool, I scan the code for table
references to find the missing tables, and also perform some tricks to
get SQL Server check queries with temp tables too.

But, there is light at the end of the tunnel. Your script runs as you
expected in SQL 2005. This is because SQL 2005 is able to recompile a
single statement in a batch, so the entire batch is not recompiled at
once.

> Also given the way it is compiling the code it doesn't explain why if I
> run these statements one line at a time, then drop the table, reload
> Query Analyser and re-run this full batch of code it works yet running
> the full batch before the table has ever existed generates an error.
> That just seems weird, so if anyone can explain why I'd be interested to
> hear it! The database should be in the same state in both cases, but as
> the behaviour is different it can't be.

This has to do with cached plans. The plans are in the cache, even if
the table is dropped. (This makes sense with temp tables.) Yes, it's
certainly a bit confusing, but for the situations for which the behaviour
is designed, it gives the best result.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:

>> Also given the way it is compiling the code it doesn't explain why if I
>> run these statements one line at a time, then drop the table, reload
>> Query Analyser and re-run this full batch of code it works yet running
>> the full batch before the table has ever existed generates an error.
>> That just seems weird, so if anyone can explain why I'd be interested to
>> hear it! The database should be in the same state in both cases, but as
>> the behaviour is different it can't be.
> This has to do with cached plans. The plans are in the cache, even if
> the table is dropped. (This makes sense with temp tables.) Yes, it's
> certainly a bit confusing, but for the situations for which the behaviour
> is designed, it gives the best result.

So basically with the query I have, whether I get an error or not (when the
database is in the same state) depends entirely on what has been run
previously? That doesn't seem very satisfactory!

Jon.

ALTER TABLE to add NOT NULL fields

I'm using the following statement to create two new fields in a table:
ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME] NOT
NULL
When I run it in QA, I get this error message:
ALTER TABLE only allows columns to be added that can contain nulls or have a
DEFAULT definition specified. Column 'LOGUserID' cannot be added to table
'tblActivity' because it does not allow nulls and does not specify a DEFAULT
definition.
How can I get those two fields to be created via the ALTER TABLE statement,
I can not use Enterprise Manager, since the statement that I'm using is bein
g
generated by another script to add those fields to all the tables in the
database...You need to specify a default value...
ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME] NOT
NULL
DEFAULT( 0 )
Make sure whatever default value you specify is meaningful to your
application; also, you could drop the DEFAULT constraint afterward...
ALTER TABLE ... DROP CONSTRAINT ...
Example...
create table t (
id int null )
insert t values( 1 )
insert t values( null )
go
alter table t add mycol int not null default( 0 )
go
sp_help t
go
alter table t drop constraint DF__t__mycol__781FBE44
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:EB8AF4A5-1217-4ADD-8E41-291A1E812B46@.microsoft.com...
> I'm using the following statement to create two new fields in a table:
> ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME]
> NOT
> NULL
> When I run it in QA, I get this error message:
> ALTER TABLE only allows columns to be added that can contain nulls or have
> a
> DEFAULT definition specified. Column 'LOGUserID' cannot be added to table
> 'tblActivity' because it does not allow nulls and does not specify a
> DEFAULT
> definition.
> How can I get those two fields to be created via the ALTER TABLE
> statement,
> I can not use Enterprise Manager, since the statement that I'm using is
> being
> generated by another script to add those fields to all the tables in the
> database...|||To add on to Tony's response, you can specify an explicit constraint name to
make subsequent table maintenance easier:
ALTER TABLE tblActivity
ADD
LOGUserID [INT] NOT NULL
CONSTRAINT DF_tblActivity_LOGUserID DEFAULT( 0 ),
LOGDATE [DATETIME] NOT NULL
CONSTRAINT DF_tblActivity_LOGDATE DEFAULT( GETDATE() )
Hope this helps.
Dan Guzman
SQL Server MVP
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:EB8AF4A5-1217-4ADD-8E41-291A1E812B46@.microsoft.com...
> I'm using the following statement to create two new fields in a table:
> ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME]
> NOT
> NULL
> When I run it in QA, I get this error message:
> ALTER TABLE only allows columns to be added that can contain nulls or have
> a
> DEFAULT definition specified. Column 'LOGUserID' cannot be added to table
> 'tblActivity' because it does not allow nulls and does not specify a
> DEFAULT
> definition.
> How can I get those two fields to be created via the ALTER TABLE
> statement,
> I can not use Enterprise Manager, since the statement that I'm using is
> being
> generated by another script to add those fields to all the tables in the
> database...sql

ALTER TABLE statement conflicted with COLUMN FOREIGN KEY

I got the following Error
"ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
constraint 'FK_ABC_DEF'. The conflict occurred in
database 'Test', table 'DEF', column 'ID'."
when I ran the following scripts:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_ABC_DEF]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ABC] DROP CONSTRAINT FK_ABC_DEF
GO
ALTER TABLE [dbo].[ABC] ADD
CONSTRAINT [FK_ABC_DEF] FOREIGN KEY
(
[ID]
) REFERENCES [dbo].[DEF] (
[ID]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
My goal was to delete the constraint and recreate it but
the above error indicates that the FK constraint is still
active even when I verified on both tables and there were
not available.
Is this a problem with sqlserver 2000 or the problem is me.
Please help.Post your DDL for table dbo.DEF.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"stoko" <anonymous@.discussions.microsoft.com> wrote in message
news:0ba901c48936$784343d0$3501280a@.phx.gbl...
I got the following Error
"ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
constraint 'FK_ABC_DEF'. The conflict occurred in
database 'Test', table 'DEF', column 'ID'."
when I ran the following scripts:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_ABC_DEF]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ABC] DROP CONSTRAINT FK_ABC_DEF
GO
ALTER TABLE [dbo].[ABC] ADD
CONSTRAINT [FK_ABC_DEF] FOREIGN KEY
(
[ID]
) REFERENCES [dbo].[DEF] (
[ID]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
My goal was to delete the constraint and recreate it but
the above error indicates that the FK constraint is still
active even when I verified on both tables and there were
not available.
Is this a problem with sqlserver 2000 or the problem is me.
Please help.|||Perhaps you have existing data that prevents the new constraint from being
created. You can identify this data with the query below:
SELECT *
FROM [dbo].[ABC] AS a
WHERE NOT EXISTS
(
SELECT *
FROM [dbo].[DEF] AS b
WHERE a.[ID] = b.[ID]
)
Hope this helps.
Dan Guzman
SQL Server MVP
"stoko" <anonymous@.discussions.microsoft.com> wrote in message
news:0ba901c48936$784343d0$3501280a@.phx.gbl...
> I got the following Error
> "ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
> constraint 'FK_ABC_DEF'. The conflict occurred in
> database 'Test', table 'DEF', column 'ID'."
> when I ran the following scripts:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_ABC_DEF]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[ABC] DROP CONSTRAINT FK_ABC_DEF
> GO
>
> ALTER TABLE [dbo].[ABC] ADD
> CONSTRAINT [FK_ABC_DEF] FOREIGN KEY
> (
> [ID]
> ) REFERENCES [dbo].[DEF] (
> [ID]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
>
> My goal was to delete the constraint and recreate it but
> the above error indicates that the FK constraint is still
> active even when I verified on both tables and there were
> not available.
> Is this a problem with sqlserver 2000 or the problem is me.
> Please help.
>|||Below is the info you requested. Each time I drop the constraints via sql
analyzer and try recreating them, I have the FK error. I check via EM and
the constraints are not there. What must be going on is beyond my
comprehension. Initially, the first 4 attempts works fine but there-after,
nothing works. Remember that the tables have data.
Let me know...
Thanks in advance.
Stoko.
"Tom Moreau" wrote:

> Post your DDL for table dbo.DEF.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "stoko" <anonymous@.discussions.microsoft.com> wrote in message
> news:0ba901c48936$784343d0$3501280a@.phx.gbl...
> I got the following Error
> "ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
> constraint 'FK_ABC_DEF'. The conflict occurred in
> database 'Test', table 'DEF', column 'ID'."
> when I ran the following scripts:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_ABC_DEF]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[ABC] DROP CONSTRAINT FK_ABC_DEF
> GO
>
> ALTER TABLE [dbo].[ABC] ADD
> CONSTRAINT [FK_ABC_DEF] FOREIGN KEY
> (
> [ID]
> ) REFERENCES [dbo].[DEF] (
> [ID]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
>
> My goal was to delete the constraint and recreate it but
> the above error indicates that the FK constraint is still
> active even when I verified on both tables and there were
> not available.
> Is this a problem with sqlserver 2000 or the problem is me.
> Please help.
>
>|||Below is the info you requested. Each time I drop the constraints via sql
analyzer and try recreating them, I have the FK error. I check via EM and
the constraints are not there. What must be going on is beyond my
comprehension. Initially, the first 4 attempts works fine but there-after,
nothing works. Remember that the tables have data.
Let me know...
Thanks in advance.
Stoko.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_AliasIDtb_RecipDemotb]') and OBJECTPROPERTY(id
,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[AliasIDtb] DROP CONSTRAINT FK_AliasIDtb_RecipDemo
tb
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_CMStb_RecipDemotb]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CMStb] DROP CONSTRAINT FK_CMStb_RecipDemotb
GO
CREATE TABLE [dbo].[RecipDemotb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[RecipSSN] [numeric](18, 0) NULL ,
[RecipLastNM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[RecipFirstNM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[RecipMiddleNM] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[RecipSuffix] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipPhone] [numeric](10, 0) NULL ,
[RecipDOB] [datetime] NULL ,
[RecipDOD] [datetime] NULL ,
[RecipAddress] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[RecipAddress2] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[RecipCounty] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipState] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipZip] [numeric](11, 0) NULL ,
[RecipRace] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MedIDNM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EPSDTIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipSex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TPLIND] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipNMCD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipDOE] [datetime] NULL ,
[RecipIDNUM] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Buy_In_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dup_Card_Code] [tinyint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AliasIDtb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[MAID] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IdBeginDate] [datetime] NULL ,
[IdEndDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CMStb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[CMS_PART_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CMS_Beg_Date] [datetime] NULL ,
[CMS_End_Date] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RecipDemotb] WITH NOCHECK ADD
CONSTRAINT [PK_RecipDemotb] PRIMARY KEY CLUSTERED
(
[OriginalRecipid]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[AliasIDtb] ADD
CONSTRAINT [FK_AliasIDtb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[CMStb] ADD
CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
"Tom Moreau" wrote:

> Post your DDL for table dbo.DEF.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "stoko" <anonymous@.discussions.microsoft.com> wrote in message
> news:0ba901c48936$784343d0$3501280a@.phx.gbl...
> I got the following Error
> "ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
> constraint 'FK_ABC_DEF'. The conflict occurred in
> database 'Test', table 'DEF', column 'ID'."
> when I ran the following scripts:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_ABC_DEF]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[ABC] DROP CONSTRAINT FK_ABC_DEF
> GO
>
> ALTER TABLE [dbo].[ABC] ADD
> CONSTRAINT [FK_ABC_DEF] FOREIGN KEY
> (
> [ID]
> ) REFERENCES [dbo].[DEF] (
> [ID]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
>
> My goal was to delete the constraint and recreate it but
> the above error indicates that the FK constraint is still
> active even when I verified on both tables and there were
> not available.
> Is this a problem with sqlserver 2000 or the problem is me.
> Please help.
>
>|||Below is the info you requested. Each time I drop the constraints via sql
analyzer and try recreating them, I have the FK error. I check via EM and
the constraints are not there. What must be going on is beyond my
comprehension. Initially, the first 4 attempts works fine but there-after,
nothing works. Remember that the tables have data.
Let me know...
Thanks in advance.
Stoko.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_AliasIDtb_RecipDemotb]') and OBJECTPROPERTY(id
,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[AliasIDtb] DROP CONSTRAINT FK_AliasIDtb_RecipDemo
tb
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_CMStb_RecipDemotb]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CMStb] DROP CONSTRAINT FK_CMStb_RecipDemotb
GO
CREATE TABLE [dbo].[RecipDemotb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[RecipSSN] [numeric](18, 0) NULL ,
[RecipLastNM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[RecipFirstNM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[RecipMiddleNM] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[RecipSuffix] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipPhone] [numeric](10, 0) NULL ,
[RecipDOB] [datetime] NULL ,
[RecipDOD] [datetime] NULL ,
[RecipAddress] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[RecipAddress2] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[RecipCounty] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipState] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipZip] [numeric](11, 0) NULL ,
[RecipRace] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MedIDNM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EPSDTIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipSex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TPLIND] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipNMCD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipDOE] [datetime] NULL ,
[RecipIDNUM] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Buy_In_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dup_Card_Code] [tinyint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AliasIDtb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[MAID] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IdBeginDate] [datetime] NULL ,
[IdEndDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CMStb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[CMS_PART_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CMS_Beg_Date] [datetime] NULL ,
[CMS_End_Date] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RecipDemotb] WITH NOCHECK ADD
CONSTRAINT [PK_RecipDemotb] PRIMARY KEY CLUSTERED
(
[OriginalRecipid]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[AliasIDtb] ADD
CONSTRAINT [FK_AliasIDtb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[CMStb] ADD
CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
"Dan Guzman" wrote:

> Perhaps you have existing data that prevents the new constraint from being
> created. You can identify this data with the query below:
> SELECT *
> FROM [dbo].[ABC] AS a
> WHERE NOT EXISTS
> (
> SELECT *
> FROM [dbo].[DEF] AS b
> WHERE a.[ID] = b.[ID]
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "stoko" <anonymous@.discussions.microsoft.com> wrote in message
> news:0ba901c48936$784343d0$3501280a@.phx.gbl...
>
>|||Actually, we really need the DDL for table dbo.DEF.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"stoko" <stoko@.discussions.microsoft.com> wrote in message
news:72208196-B2EE-4A61-B46C-0F9C7DA0AAE6@.microsoft.com...
Below is the info you requested. Each time I drop the constraints via sql
analyzer and try recreating them, I have the FK error. I check via EM and
the constraints are not there. What must be going on is beyond my
comprehension. Initially, the first 4 attempts works fine but there-after,
nothing works. Remember that the tables have data.
Let me know...
Thanks in advance.
Stoko.
"Tom Moreau" wrote:

> Post your DDL for table dbo.DEF.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "stoko" <anonymous@.discussions.microsoft.com> wrote in message
> news:0ba901c48936$784343d0$3501280a@.phx.gbl...
> I got the following Error
> "ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
> constraint 'FK_ABC_DEF'. The conflict occurred in
> database 'Test', table 'DEF', column 'ID'."
> when I ran the following scripts:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_ABC_DEF]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[ABC] DROP CONSTRAINT FK_ABC_DEF
> GO
>
> ALTER TABLE [dbo].[ABC] ADD
> CONSTRAINT [FK_ABC_DEF] FOREIGN KEY
> (
> [ID]
> ) REFERENCES [dbo].[DEF] (
> [ID]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
>
> My goal was to delete the constraint and recreate it but
> the above error indicates that the FK constraint is still
> active even when I verified on both tables and there were
> not available.
> Is this a problem with sqlserver 2000 or the problem is me.
> Please help.
>
>|||On Wed, 8 Sep 2004 12:15:05 -0700, stoko wrote:

>Below is the info you requested. Each time I drop the constraints via sql
>analyzer and try recreating them, I have the FK error. I check via EM and
>the constraints are not there. What must be going on is beyond my
>comprehension. Initially, the first 4 attempts works fine but there-after,
>nothing works. Remember that the tables have data.
>Let me know...
>Thanks in advance.
>Stoko.
(snip code)
Hi Stoko,
The code you supplied works fine for me. And when I append the code from
your original post, I get the following error:
Server: Msg 4902, Level 16, State 1, Line 3
Cannot alter table 'dbo.ABC' because this table does not exist in database
'TestDB80'.
Somehow, you seem to have posted the wrong tables here.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Tom,
It would have saved us some time if you renamed the tables to DEF. The DDL
I sent are production tables. I was trying to change the names, etc, but
decided to send you the live information. Thus far, no one has been able to
help explain why I cannot delete and recreate constraints in a sp or script
or dts on tables that have records. The irony is that this thing worked the
first few times and just fails thereafter -- requiring me to recreate the
constraints manually.
I am still waiting for your support.
Thanks.
Stoko.
"Tom Moreau" wrote:

> Actually, we really need the DDL for table dbo.DEF.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "stoko" <stoko@.discussions.microsoft.com> wrote in message
> news:72208196-B2EE-4A61-B46C-0F9C7DA0AAE6@.microsoft.com...
> Below is the info you requested. Each time I drop the constraints via sql
> analyzer and try recreating them, I have the FK error. I check via EM and
> the constraints are not there. What must be going on is beyond my
> comprehension. Initially, the first 4 attempts works fine but there-after
,
> nothing works. Remember that the tables have data.
> Let me know...
> Thanks in advance.
> Stoko.
> "Tom Moreau" wrote:
>
>|||It will save you even more time if you simply provide the DDL for BOTH
tables. I cannot help you until you do that.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"stoko" <stoko@.discussions.microsoft.com> wrote in message
news:F2F57F9B-D3E5-4B47-A53E-4A2F2750E2FB@.microsoft.com...
Tom,
It would have saved us some time if you renamed the tables to DEF. The DDL
I sent are production tables. I was trying to change the names, etc, but
decided to send you the live information. Thus far, no one has been able to
help explain why I cannot delete and recreate constraints in a sp or script
or dts on tables that have records. The irony is that this thing worked the
first few times and just fails thereafter -- requiring me to recreate the
constraints manually.
I am still waiting for your support.
Thanks.
Stoko.
"Tom Moreau" wrote:

> Actually, we really need the DDL for table dbo.DEF.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "stoko" <stoko@.discussions.microsoft.com> wrote in message
> news:72208196-B2EE-4A61-B46C-0F9C7DA0AAE6@.microsoft.com...
> Below is the info you requested. Each time I drop the constraints via sql
> analyzer and try recreating them, I have the FK error. I check via EM and
> the constraints are not there. What must be going on is beyond my
> comprehension. Initially, the first 4 attempts works fine but
there-after,
> nothing works. Remember that the tables have data.
> Let me know...
> Thanks in advance.
> Stoko.
> "Tom Moreau" wrote:
>
>sql

ALTER TABLE statement conflicted with COLUMN FOREIGN KEY

I got the following Error
"ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
constraint 'FK_ABC_DEF'. The conflict occurred in
database 'Test', table 'DEF', column 'ID'."
when I ran the following scripts:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_ABC_DEF]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ABC] DROP CONSTRAINT FK_ABC_DEF
GO
ALTER TABLE [dbo].[ABC] ADD
CONSTRAINT [FK_ABC_DEF] FOREIGN KEY
(
[ID]
) REFERENCES [dbo].[DEF] (
[ID]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
My goal was to delete the constraint and recreate it but
the above error indicates that the FK constraint is still
active even when I verified on both tables and there were
not available.
Is this a problem with sqlserver 2000 or the problem is me.
Please help.
Post your DDL for table dbo.DEF.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"stoko" <anonymous@.discussions.microsoft.com> wrote in message
news:0ba901c48936$784343d0$3501280a@.phx.gbl...
I got the following Error
"ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
constraint 'FK_ABC_DEF'. The conflict occurred in
database 'Test', table 'DEF', column 'ID'."
when I ran the following scripts:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_ABC_DEF]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ABC] DROP CONSTRAINT FK_ABC_DEF
GO
ALTER TABLE [dbo].[ABC] ADD
CONSTRAINT [FK_ABC_DEF] FOREIGN KEY
(
[ID]
) REFERENCES [dbo].[DEF] (
[ID]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
My goal was to delete the constraint and recreate it but
the above error indicates that the FK constraint is still
active even when I verified on both tables and there were
not available.
Is this a problem with sqlserver 2000 or the problem is me.
Please help.
|||Perhaps you have existing data that prevents the new constraint from being
created. You can identify this data with the query below:
SELECT *
FROM [dbo].[ABC] AS a
WHERE NOT EXISTS
(
SELECT *
FROM [dbo].[DEF] AS b
WHERE a.[ID] = b.[ID]
)
Hope this helps.
Dan Guzman
SQL Server MVP
"stoko" <anonymous@.discussions.microsoft.com> wrote in message
news:0ba901c48936$784343d0$3501280a@.phx.gbl...
> I got the following Error
> "ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
> constraint 'FK_ABC_DEF'. The conflict occurred in
> database 'Test', table 'DEF', column 'ID'."
> when I ran the following scripts:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_ABC_DEF]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[ABC] DROP CONSTRAINT FK_ABC_DEF
> GO
>
> ALTER TABLE [dbo].[ABC] ADD
> CONSTRAINT [FK_ABC_DEF] FOREIGN KEY
> (
> [ID]
> ) REFERENCES [dbo].[DEF] (
> [ID]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
>
> My goal was to delete the constraint and recreate it but
> the above error indicates that the FK constraint is still
> active even when I verified on both tables and there were
> not available.
> Is this a problem with sqlserver 2000 or the problem is me.
> Please help.
>
|||Below is the info you requested. Each time I drop the constraints via sql
analyzer and try recreating them, I have the FK error. I check via EM and
the constraints are not there. What must be going on is beyond my
comprehension. Initially, the first 4 attempts works fine but there-after,
nothing works. Remember that the tables have data.
Let me know...
Thanks in advance.
Stoko.
"Tom Moreau" wrote:

> Post your DDL for table dbo.DEF.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "stoko" <anonymous@.discussions.microsoft.com> wrote in message
> news:0ba901c48936$784343d0$3501280a@.phx.gbl...
> I got the following Error
> "ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
> constraint 'FK_ABC_DEF'. The conflict occurred in
> database 'Test', table 'DEF', column 'ID'."
> when I ran the following scripts:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_ABC_DEF]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[ABC] DROP CONSTRAINT FK_ABC_DEF
> GO
>
> ALTER TABLE [dbo].[ABC] ADD
> CONSTRAINT [FK_ABC_DEF] FOREIGN KEY
> (
> [ID]
> ) REFERENCES [dbo].[DEF] (
> [ID]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
>
> My goal was to delete the constraint and recreate it but
> the above error indicates that the FK constraint is still
> active even when I verified on both tables and there were
> not available.
> Is this a problem with sqlserver 2000 or the problem is me.
> Please help.
>
>
|||Below is the info you requested. Each time I drop the constraints via sql
analyzer and try recreating them, I have the FK error. I check via EM and
the constraints are not there. What must be going on is beyond my
comprehension. Initially, the first 4 attempts works fine but there-after,
nothing works. Remember that the tables have data.
Let me know...
Thanks in advance.
Stoko.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_AliasIDtb_RecipDemotb]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[AliasIDtb] DROP CONSTRAINT FK_AliasIDtb_RecipDemotb
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_CMStb_RecipDemotb]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CMStb] DROP CONSTRAINT FK_CMStb_RecipDemotb
GO
CREATE TABLE [dbo].[RecipDemotb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RecipSSN] [numeric](18, 0) NULL ,
[RecipLastNM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipFirstNM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipMiddleNM] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipSuffix] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipPhone] [numeric](10, 0) NULL ,
[RecipDOB] [datetime] NULL ,
[RecipDOD] [datetime] NULL ,
[RecipAddress] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipAddress2] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipCounty] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipState] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipZip] [numeric](11, 0) NULL ,
[RecipRace] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MedIDNM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EPSDTIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipSex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TPLIND] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipNMCD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipDOE] [datetime] NULL ,
[RecipIDNUM] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Buy_In_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dup_Card_Code] [tinyint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AliasIDtb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MAID] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IdBeginDate] [datetime] NULL ,
[IdEndDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CMStb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CMS_PART_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CMS_Beg_Date] [datetime] NULL ,
[CMS_End_Date] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RecipDemotb] WITH NOCHECK ADD
CONSTRAINT [PK_RecipDemotb] PRIMARY KEY CLUSTERED
(
[OriginalRecipid]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[AliasIDtb] ADD
CONSTRAINT [FK_AliasIDtb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[CMStb] ADD
CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
"Tom Moreau" wrote:

> Post your DDL for table dbo.DEF.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "stoko" <anonymous@.discussions.microsoft.com> wrote in message
> news:0ba901c48936$784343d0$3501280a@.phx.gbl...
> I got the following Error
> "ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
> constraint 'FK_ABC_DEF'. The conflict occurred in
> database 'Test', table 'DEF', column 'ID'."
> when I ran the following scripts:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_ABC_DEF]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[ABC] DROP CONSTRAINT FK_ABC_DEF
> GO
>
> ALTER TABLE [dbo].[ABC] ADD
> CONSTRAINT [FK_ABC_DEF] FOREIGN KEY
> (
> [ID]
> ) REFERENCES [dbo].[DEF] (
> [ID]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
>
> My goal was to delete the constraint and recreate it but
> the above error indicates that the FK constraint is still
> active even when I verified on both tables and there were
> not available.
> Is this a problem with sqlserver 2000 or the problem is me.
> Please help.
>
>
|||Below is the info you requested. Each time I drop the constraints via sql
analyzer and try recreating them, I have the FK error. I check via EM and
the constraints are not there. What must be going on is beyond my
comprehension. Initially, the first 4 attempts works fine but there-after,
nothing works. Remember that the tables have data.
Let me know...
Thanks in advance.
Stoko.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_AliasIDtb_RecipDemotb]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[AliasIDtb] DROP CONSTRAINT FK_AliasIDtb_RecipDemotb
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_CMStb_RecipDemotb]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CMStb] DROP CONSTRAINT FK_CMStb_RecipDemotb
GO
CREATE TABLE [dbo].[RecipDemotb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RecipSSN] [numeric](18, 0) NULL ,
[RecipLastNM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipFirstNM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipMiddleNM] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipSuffix] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipPhone] [numeric](10, 0) NULL ,
[RecipDOB] [datetime] NULL ,
[RecipDOD] [datetime] NULL ,
[RecipAddress] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipAddress2] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipCounty] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipState] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipZip] [numeric](11, 0) NULL ,
[RecipRace] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MedIDNM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EPSDTIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipSex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TPLIND] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipNMCD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipDOE] [datetime] NULL ,
[RecipIDNUM] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Buy_In_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dup_Card_Code] [tinyint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AliasIDtb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MAID] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IdBeginDate] [datetime] NULL ,
[IdEndDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CMStb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CMS_PART_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CMS_Beg_Date] [datetime] NULL ,
[CMS_End_Date] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RecipDemotb] WITH NOCHECK ADD
CONSTRAINT [PK_RecipDemotb] PRIMARY KEY CLUSTERED
(
[OriginalRecipid]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[AliasIDtb] ADD
CONSTRAINT [FK_AliasIDtb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[CMStb] ADD
CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
"Dan Guzman" wrote:

> Perhaps you have existing data that prevents the new constraint from being
> created. You can identify this data with the query below:
> SELECT *
> FROM [dbo].[ABC] AS a
> WHERE NOT EXISTS
> (
> SELECT *
> FROM [dbo].[DEF] AS b
> WHERE a.[ID] = b.[ID]
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "stoko" <anonymous@.discussions.microsoft.com> wrote in message
> news:0ba901c48936$784343d0$3501280a@.phx.gbl...
>
>
|||Actually, we really need the DDL for table dbo.DEF.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"stoko" <stoko@.discussions.microsoft.com> wrote in message
news:72208196-B2EE-4A61-B46C-0F9C7DA0AAE6@.microsoft.com...
Below is the info you requested. Each time I drop the constraints via sql
analyzer and try recreating them, I have the FK error. I check via EM and
the constraints are not there. What must be going on is beyond my
comprehension. Initially, the first 4 attempts works fine but there-after,
nothing works. Remember that the tables have data.
Let me know...
Thanks in advance.
Stoko.
"Tom Moreau" wrote:

> Post your DDL for table dbo.DEF.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "stoko" <anonymous@.discussions.microsoft.com> wrote in message
> news:0ba901c48936$784343d0$3501280a@.phx.gbl...
> I got the following Error
> "ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
> constraint 'FK_ABC_DEF'. The conflict occurred in
> database 'Test', table 'DEF', column 'ID'."
> when I ran the following scripts:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_ABC_DEF]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[ABC] DROP CONSTRAINT FK_ABC_DEF
> GO
>
> ALTER TABLE [dbo].[ABC] ADD
> CONSTRAINT [FK_ABC_DEF] FOREIGN KEY
> (
> [ID]
> ) REFERENCES [dbo].[DEF] (
> [ID]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
>
> My goal was to delete the constraint and recreate it but
> the above error indicates that the FK constraint is still
> active even when I verified on both tables and there were
> not available.
> Is this a problem with sqlserver 2000 or the problem is me.
> Please help.
>
>
|||On Wed, 8 Sep 2004 12:15:05 -0700, stoko wrote:

>Below is the info you requested. Each time I drop the constraints via sql
>analyzer and try recreating them, I have the FK error. I check via EM and
>the constraints are not there. What must be going on is beyond my
>comprehension. Initially, the first 4 attempts works fine but there-after,
>nothing works. Remember that the tables have data.
>Let me know...
>Thanks in advance.
>Stoko.
(snip code)
Hi Stoko,
The code you supplied works fine for me. And when I append the code from
your original post, I get the following error:
Server: Msg 4902, Level 16, State 1, Line 3
Cannot alter table 'dbo.ABC' because this table does not exist in database
'TestDB80'.
Somehow, you seem to have posted the wrong tables here.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Tom,
It would have saved us some time if you renamed the tables to DEF. The DDL
I sent are production tables. I was trying to change the names, etc, but
decided to send you the live information. Thus far, no one has been able to
help explain why I cannot delete and recreate constraints in a sp or script
or dts on tables that have records. The irony is that this thing worked the
first few times and just fails thereafter -- requiring me to recreate the
constraints manually.
I am still waiting for your support.
Thanks.
Stoko.
"Tom Moreau" wrote:

> Actually, we really need the DDL for table dbo.DEF.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "stoko" <stoko@.discussions.microsoft.com> wrote in message
> news:72208196-B2EE-4A61-B46C-0F9C7DA0AAE6@.microsoft.com...
> Below is the info you requested. Each time I drop the constraints via sql
> analyzer and try recreating them, I have the FK error. I check via EM and
> the constraints are not there. What must be going on is beyond my
> comprehension. Initially, the first 4 attempts works fine but there-after,
> nothing works. Remember that the tables have data.
> Let me know...
> Thanks in advance.
> Stoko.
> "Tom Moreau" wrote:
>
>
|||It will save you even more time if you simply provide the DDL for BOTH
tables. I cannot help you until you do that.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"stoko" <stoko@.discussions.microsoft.com> wrote in message
news:F2F57F9B-D3E5-4B47-A53E-4A2F2750E2FB@.microsoft.com...
Tom,
It would have saved us some time if you renamed the tables to DEF. The DDL
I sent are production tables. I was trying to change the names, etc, but
decided to send you the live information. Thus far, no one has been able to
help explain why I cannot delete and recreate constraints in a sp or script
or dts on tables that have records. The irony is that this thing worked the
first few times and just fails thereafter -- requiring me to recreate the
constraints manually.
I am still waiting for your support.
Thanks.
Stoko.
"Tom Moreau" wrote:

> Actually, we really need the DDL for table dbo.DEF.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "stoko" <stoko@.discussions.microsoft.com> wrote in message
> news:72208196-B2EE-4A61-B46C-0F9C7DA0AAE6@.microsoft.com...
> Below is the info you requested. Each time I drop the constraints via sql
> analyzer and try recreating them, I have the FK error. I check via EM and
> the constraints are not there. What must be going on is beyond my
> comprehension. Initially, the first 4 attempts works fine but
there-after,
> nothing works. Remember that the tables have data.
> Let me know...
> Thanks in advance.
> Stoko.
> "Tom Moreau" wrote:
>
>

Alter Table Row Size Error

I am trying to alter a table and getting the following error.
Server: Msg 1701, Level 16, State 2, Line 1
Creation of table 'CustomerMaster' failed because the row size would be
8508, including internal overhead. This exceeds the maximum allowable table
row size, 8060.
I can create a new table with the same fields, but not alter an existing one.
I have calculated the maximum row size as only 6004 after the changes. Here
is what I used to calculate.
69 columns
39 char fields (5816 total characters)
9 tinyint fields (size = 9 * 1 = 9)
4 int fields (size = 4 * 4 = 16)
5 datetime fields (size = 5 * 8 = 40)
12 decimal 19,5 fields (size = 12 * 9 = 108)
Num_Cols = 69
Fixed_Data_Size = 5989
Num_Var_Cols = 0
Max_Var_Size = 0
Null_Bitmap = 2 + ((69 + 7 ) / 8 = 11.5
Row_Size = 5981 + 0 + 11 + 4 = 6004> 39 char fields (5816 total characters)
What are the definitions of these columns? Are they
CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
> 9 tinyint fields (size = 9 * 1 = 9)
> 4 int fields (size = 4 * 4 = 16)
> 5 datetime fields (size = 5 * 8 = 40)
> 12 decimal 19,5 fields (size = 12 * 9 = 108)
Are any of these NULLable?|||"Aaron Bertrand [SQL Server MVP]" wrote:
> > 39 char fields (5816 total characters)
> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
> > 9 tinyint fields (size = 9 * 1 = 9)
> > 4 int fields (size = 4 * 4 = 16)
> > 5 datetime fields (size = 5 * 8 = 40)
> > 12 decimal 19,5 fields (size = 12 * 9 = 108)
> Are any of these NULLable?
>
>
39 CHAR fields
field length - field count
3 - 2
5 - 3
10 - 4
15 - 1
20 - 11
25 - 2
30 - 11
45 - 2
50 - 1
2000 - 1
3000 - 1
total characters = 5816
all fields are NULLABLE but one CHAR field (in my calculations I considered
all fields as nullable)|||"Aaron Bertrand [SQL Server MVP]" wrote:
> > 39 char fields (5816 total characters)
> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
> > 9 tinyint fields (size = 9 * 1 = 9)
> > 4 int fields (size = 4 * 4 = 16)
> > 5 datetime fields (size = 5 * 8 = 40)
> > 12 decimal 19,5 fields (size = 12 * 9 = 108)
> Are any of these NULLable?
>
>
I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
calculate my row size.|||Are you certain they're all CHAR and none are NCHAR or NVARCHAR? Can you
post the results of:
EXEC sp_help 'tablename';
?
Why would you have a CHAR(2000) or CHAR(3000)? Is every row always going to
be 3000 characters?
"Matt Soukup" <MattSoukup@.discussions.microsoft.com> wrote in message
news:F6ADF63E-E3FD-4D8E-88C7-8D1B1C4F2D22@.microsoft.com...
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> > 39 char fields (5816 total characters)
>> What are the definitions of these columns? Are they
>> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>> > 9 tinyint fields (size = 9 * 1 = 9)
>> > 4 int fields (size = 4 * 4 = 16)
>> > 5 datetime fields (size = 5 * 8 = 40)
>> > 12 decimal 19,5 fields (size = 12 * 9 = 108)
>> Are any of these NULLable?
>>
> 39 CHAR fields
> field length - field count
> 3 - 2
> 5 - 3
> 10 - 4
> 15 - 1
> 20 - 11
> 25 - 2
> 30 - 11
> 45 - 2
> 50 - 1
> 2000 - 1
> 3000 - 1
> total characters = 5816
> all fields are NULLABLE but one CHAR field (in my calculations I
> considered
> all fields as nullable)|||> I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
> calculate my row size.
Yes, that's all fine and good. But if you wrote down CHAR(3000) when it's
actually NCHAR(3000), and used the former in your calculations, it doesn't
really matter how accurate the calculation is, the source input invalidates
it.|||"Aaron Bertrand [SQL Server MVP]" wrote:
> > I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
> > calculate my row size.
> Yes, that's all fine and good. But if you wrote down CHAR(3000) when it's
> actually NCHAR(3000), and used the former in your calculations, it doesn't
> really matter how accurate the calculation is, the source input invalidates
> it.
>
>
all character fields are CHAR(####)
Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
Here is a list of the fields:
Customer_Number char(20)
CUSTNMBR char(45)
VENDORID char(45)
Attn_Name char(30)
Contact_Name char(30)
ContactPhone char(20)
BusinessName char(30)
AddressLine1 char(30)
AddressLine2 char(30)
City char(25)
State char(3)
ZipCode char(10)
Phone char(20)
Fax char(20)
ICC_Number char(20)
FedID_Number char(15)
Contracted tinyint
Start_Date datetime
InsAgentName char(30)
InsAgentAddress1 char(30)
InsAgentAddress2 char(30)
InsAgentCity char(25)
InsAgentState char(3)
InsAgentZipCode char(10)
InsAgentPhone char(20)
InsAgentFax char(20)
InsAgentContact char(30)
CargoInsName char(28)
CargoInsAmount decimal(19, 5)
CargoInsExpirationDate datetime
AutoLibInsName char(30)
AutoLibInsAmount decimal(19, 5)
AutoLibInsExpirationDate datetime
Agent_YN tinyint
Broker_YN tinyint
Carrier_YN tinyint
Shipper_YN tinyint
Consignee_YN tinyint
BillTo_YN tinyint
CreatedDate datetime
CreatedUserID char(20)
Billing_Rate decimal(19, 5)
Billing_Rate_Code char(5)
Rating char(5)
Color char(20)
Flagged tinyint
FlagDate datetime
FlagUserID char(20)
FlagReason char(50)
Active tinyint
Notes char(3000)
PayCode char(5)
PayRateFlat decimal(19, 5)
PayRatePercent decimal(19, 5)
PayRateLoaded decimal(19, 5)
PayRateUnloaded decimal(19, 5)
DefaultSalesperson char(20)
Directions char(2000)
FSType char(10)
FSRateAmount decimal(19, 5)
Weight decimal(19, 5)
AgentPayAccount int
CarrierPayAccount int
DropPayAmount decimal(19, 5)
PickupPayAmount decimal(19, 5)
ISType char(10)
ISRateAmount decimal(19, 5)
Latitude int
Longitude int|||Sorry, but there is still information missing. I don't want to see your
compiled list of fields. Can you please copy and paste the result of:
EXEC sp_help 'CustomerMaster';
? Otherwise, I have no further input on this issue. The list of columns
you provided below can't possibly be 8508 bytes unless (a) the column you're
trying to add is a lot longer than you're letting on, (b) some of those data
types are not correct, or (c) you've left out some columns.
> all character fields are CHAR(####)
> Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
> Here is a list of the fields:
> Customer_Number char(20)|||"Aaron Bertrand [SQL Server MVP]" wrote:
> Sorry, but there is still information missing. I don't want to see your
> compiled list of fields. Can you please copy and paste the result of:
> EXEC sp_help 'CustomerMaster';
> ? Otherwise, I have no further input on this issue. The list of columns
> you provided below can't possibly be 8508 bytes unless (a) the column you're
> trying to add is a lot longer than you're letting on, (b) some of those data
> types are not correct, or (c) you've left out some columns.
>
>
> > all character fields are CHAR(####)
> > Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
> >
> > Here is a list of the fields:
> >
> > Customer_Number char(20)
>
>
Just forget it. SQL is being stupid.
This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20).
I can create this table from scratch with the columns/sizes I listed above.
The table is created with no problem. I have to up the size of one of my CHAR
fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get the
CREATE TABLE to fail with the same error.|||> Just forget it. SQL is being stupid.
I am willing to bet that it is not. But I can't prove it unless you post an
honest result from EXEC sp_help.
> This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
> COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20).
Is it possible this column is involved in a foreign key relationship, and
you are trying to implement the change through the GUI?
A|||> I can create this table from scratch with the columns/sizes I listed
> above.
> The table is created with no problem. I have to up the size of one of my
> CHAR
> fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get
> the
> CREATE TABLE to fail with the same error.
I also strongly recommend you familiarize yourself with the reasons we have
CHAR and VARCHAR. CHAR(2000) is not exactly a great choice here.
http://databases.aspfaq.com/database/what-datatype-should-i-use-for-my-character-based-database-columns.html
A|||Hi Matt
If you ALTER the length of fixed length columns, SQL Server may not reuse
the original space.
See my blog post on the subject:
http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/13/301.aspx
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Matt Soukup" <MattSoukup@.discussions.microsoft.com> wrote in message
news:9EF8C9DF-A8DE-406D-B1BA-1C67338D8FB3@.microsoft.com...
>I am trying to alter a table and getting the following error.
> Server: Msg 1701, Level 16, State 2, Line 1
> Creation of table 'CustomerMaster' failed because the row size would be
> 8508, including internal overhead. This exceeds the maximum allowable
> table
> row size, 8060.
> I can create a new table with the same fields, but not alter an existing
> one.
> I have calculated the maximum row size as only 6004 after the changes.
> Here
> is what I used to calculate.
> 69 columns
> 39 char fields (5816 total characters)
> 9 tinyint fields (size = 9 * 1 = 9)
> 4 int fields (size = 4 * 4 = 16)
> 5 datetime fields (size = 5 * 8 = 40)
> 12 decimal 19,5 fields (size = 12 * 9 = 108)
> Num_Cols = 69
> Fixed_Data_Size = 5989
> Num_Var_Cols = 0
> Max_Var_Size = 0
> Null_Bitmap = 2 + ((69 + 7 ) / 8 = 11.5
> Row_Size = 5981 + 0 + 11 + 4 = 6004
>|||On Feb 7, 2:13 pm, Matt Soukup <MattSou...@.discussions.microsoft.com>
wrote:
> Just forget it. SQL is being stupid.
> This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
> COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20).
> I can create this table from scratch with the columns/sizes I listed above.
> The table is created with no problem. I have to up the size of one of my CHAR
> fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get the
> CREATE TABLE to fail with the same error.
Aaron asked you twice for the results of sp_help, and you've refused
to provide that, instead opting for the "SQL is being stupid"
response. WTF? If we can see the table structure, then we know
exactly what you're working with, and can provide a proper answer
instead of guessing.
Here's another suggestion that you can write off as "stupid" - think
about using VARCHAR instead of CHAR for your column definitions.
You're very likely wasting a ton of space with these fixed-length
2000+ column lengths. Read up on the differences between CHAR and
VARCHAR, hopefully the documentation isn't too "stupid".|||This may very well be it; I'm glad I cntinue to learn things from you Kalen.
But the OP stated:
"This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20)."
Yet is able to increase the length of a CHAR(2000) column to CHAR(5000). I
would think that if previous column modifications had caused the row size
threshold to be within 10 bytes of the current row size, that just about
*any* column length extension would cause the problem. I guess having the
actual table structure from sp_help, so understanding the order of the
columns (and a history of modifications made) would help us answer the
question better. Better still would be the result of your query against
sys.partitions/columns/system_internals_partition_columns.
In any case, the last statement in your blog entry is probably the most
helpful to the OP, though Tracy and I have both suggested it to no avail:
"So be careful when using large datatypes, especially if you want to make
them fixed length instead of variable length."
The shortest path is likely to rebuild the table. But the Directions and
Notes columns should be VARCHAR, not CHAR.
A
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e5yhB40SHHA.1552@.TK2MSFTNGP05.phx.gbl...
> Hi Matt
> If you ALTER the length of fixed length columns, SQL Server may not reuse
> the original space.
> See my blog post on the subject:
> http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/13/301.aspx|||I didn't read the whole thread in detail initially, I was just tossing this
out as a possibility.
However, now that I am going back and reading over the posts, I don't see
anywhere that he said he had already altered the table changing a column
from 2000 to 5000 bytes. He said he would have to replace a 2000 byte column
with a 5000 byte column to get the original CREATE TABLE to fail. Has he
indicated he has done other ALTERs?
Yes, it would be nice to get more details from the OP, including WHY he
thinks he needs fixed length columns. Otherwise we just can't know. I agree
Directions and Notes should be variable length.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OpFCoZ4SHHA.1552@.TK2MSFTNGP05.phx.gbl...
> This may very well be it; I'm glad I cntinue to learn things from you
> Kalen.
> But the OP stated:
> "This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
> COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20)."
> Yet is able to increase the length of a CHAR(2000) column to CHAR(5000).
> I would think that if previous column modifications had caused the row
> size threshold to be within 10 bytes of the current row size, that just
> about *any* column length extension would cause the problem. I guess
> having the actual table structure from sp_help, so understanding the order
> of the columns (and a history of modifications made) would help us answer
> the question better. Better still would be the result of your query
> against sys.partitions/columns/system_internals_partition_columns.
> In any case, the last statement in your blog entry is probably the most
> helpful to the OP, though Tracy and I have both suggested it to no avail:
> "So be careful when using large datatypes, especially if you want to make
> them fixed length instead of variable length."
> The shortest path is likely to rebuild the table. But the Directions and
> Notes columns should be VARCHAR, not CHAR.
> A
>
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e5yhB40SHHA.1552@.TK2MSFTNGP05.phx.gbl...
>> Hi Matt
>> If you ALTER the length of fixed length columns, SQL Server may not reuse
>> the original space.
>> See my blog post on the subject:
>> http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/13/301.aspx
>|||> However, now that I am going back and reading over the posts, I don't see
> anywhere that he said he had already altered the table changing a column
> from 2000 to 5000 bytes. He said he would have to replace a 2000 byte
> column with a 5000 byte column to get the original CREATE TABLE to fail.
> Has he indicated he has done other ALTERs?
Here is what I am referring to:
I have to up the size of one of my CHAR
fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get the
CREATE TABLE to fail with the same error.
My interpretation was that he had done that. Maybe I'm wrong. It's
ambiguous.
A|||Yes, that is what I'm referring to, and he explicitly says it is the CREATE
table and not ALTER.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23UIpXh6SHHA.2228@.TK2MSFTNGP03.phx.gbl...
>> However, now that I am going back and reading over the posts, I don't see
>> anywhere that he said he had already altered the table changing a column
>> from 2000 to 5000 bytes. He said he would have to replace a 2000 byte
>> column with a 5000 byte column to get the original CREATE TABLE to fail.
>> Has he indicated he has done other ALTERs?
> Here is what I am referring to:
> I have to up the size of one of my CHAR
> fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get
> the
> CREATE TABLE to fail with the same error.
> My interpretation was that he had done that. Maybe I'm wrong. It's
> ambiguous.
> A
>|||> Yes, that is what I'm referring to, and he explicitly says it is the
> CREATE table and not ALTER.
Ahh. You should know by now that I lack the ability to read entire
sentences. :-)
And you would think the caps would make words stand out more, but for me it
obscures them... I tend to focus on the meat *between* the keywords...
A

Thursday, March 29, 2012

Alternate Rows in Matrix

Sunday, March 25, 2012

Altering a column which has an index defined on it

Alter View permission?

ALTER TABLE/COLUMN syntax

Thursday, March 22, 2012

Alter table weird bug?

ALTER TABLE to add NOT NULL fields

ALTER TABLE statement conflicted with COLUMN FOREIGN KEY

ALTER TABLE statement conflicted with COLUMN FOREIGN KEY

ALTER TABLE statement conflicted with COLUMN FOREIGN KEY

Alter Table Row Size Error

Sql Server Alignment problem

Blog Archive

About Me