SQL Server Error: 8629

microsoft.public.sqlserver.programming

9 Sep 2005 7:21 PM

Dave

I am getting a SQL Server Error: 8629
    "The query processor could not produce a query plan from the optimizer
    because a query cannot update a text, ntext, or image column and a
    clustering key at the same time."

I turned the clustering option off for this key and that worked but have
no idea why I need to, what the repercussions are, or if there is another
way to do this.

I am trying to insert the >> very first << row of the table using .NET
and SQL Server 2000.

Here is the layout of my table.

Any thoughts are much appreciated.

Dave

===========================================

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Table_1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_1]
GO

CREATE TABLE [dbo].[Table_1] (
    [ID] [uniqueidentifier] NOT NULL ,
    [Field_1] [uniqueidentifier] NULL ,
    [Field_2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Field_3] [bit] NOT NULL CONSTRAINT [DF_Table_1_Field_3] DEFAULT (0),
    [Field_4] [bit] NOT NULL CONSTRAINT [DF_Table_1_Field_4] DEFAULT (0),
    [Field_5] [bit] NOT NULL CONSTRAINT [DF_Table_1_Field_5] DEFAULT (0),
    [Field_6] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Field_7] [smallint] NOT NULL ,
    [Field_8] [uniqueidentifier] NULL ,
    [Field_9] [uniqueidentifier] NULL ,
    [Field_10] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Field_11] [int] NOT NULL ,
    CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
    (
        [ID]
    ) ON [PRIMARY] ,
    CONSTRAINT [FK_Table_1IDToTable_1AliasTable_1ID] FOREIGN KEY
    (
        [Field_1]
    ) REFERENCES [dbo].[Table_1] (
        [ID]
    ),
    CONSTRAINT [FK_Table_2IDToTable_4ID] FOREIGN KEY
    (
        [Field_8]
    ) REFERENCES [dbo].[Table_2] (
        [ID]
    ),
    CONSTRAINT [FK_Field_7ToTable_1Field_7] FOREIGN KEY
    (
        [Field_7]
    ) REFERENCES [dbo].[Table_3] (
        [ID]
    ) ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE UNIQUE INDEX [UN_Table_1_Field_10_Field_11] ON
[dbo].[Table_1]([Field_10], [Field_11]) ON [PRIMARY]
GO

alter table [dbo].[Table_1] nocheck constraint
[FK_Table_1IDToTable_1AliasTable_1ID]
GO

alter table [dbo].[Table_1] nocheck constraint [FK_Table_2IDToTable_4ID]
GO

11 Sep 2005 12:51 AM

This must be an oversight, right.

CONSTRAINT [FK_Field_7ToTable_1Field_7] FOREIGN KEY
(
[Field_7]
) REFERENCES [dbo].[Table_3] (
[ID]
) ON UPDATE CASCADE

You're creating a fk on a text column that references an id column with
cascade updating? Removing the dri should at least stop the error from
raising. Though, I still question you about the choice for using blob for
your key.

--
-oj

Show quote

"Dave" <D***@discussions.microsoft.com> wrote in message
news:5AB8AB5F-4793-4274-A257-C3B16AE2589D@microsoft.com...
>I am getting a SQL Server Error: 8629
>    "The query processor could not produce a query plan from the optimizer
>    because a query cannot update a text, ntext, or image column and a
>    clustering key at the same time."
>
> I turned the clustering option off for this key and that worked but have
> no idea why I need to, what the repercussions are, or if there is another
> way to do this.
>
> I am trying to insert the >> very first << row of the table using .NET
> and SQL Server 2000.
>
> Here is the layout of my table.
>
> Any thoughts are much appreciated.
>
> Dave
>
> ===========================================
>
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Table_1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Table_1]
> GO
>
> CREATE TABLE [dbo].[Table_1] (
> [ID] [uniqueidentifier] NOT NULL ,
> [Field_1] [uniqueidentifier] NULL ,
> [Field_2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Field_3] [bit] NOT NULL CONSTRAINT [DF_Table_1_Field_3] DEFAULT (0),
> [Field_4] [bit] NOT NULL CONSTRAINT [DF_Table_1_Field_4] DEFAULT (0),
> [Field_5] [bit] NOT NULL CONSTRAINT [DF_Table_1_Field_5] DEFAULT (0),
> [Field_6] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Field_7] [smallint] NOT NULL ,
> [Field_8] [uniqueidentifier] NULL ,
> [Field_9] [uniqueidentifier] NULL ,
> [Field_10] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Field_11] [int] NOT NULL ,
> CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Table_1IDToTable_1AliasTable_1ID] FOREIGN KEY
> (
> [Field_1]
> ) REFERENCES [dbo].[Table_1] (
> [ID]
> ),
> CONSTRAINT [FK_Table_2IDToTable_4ID] FOREIGN KEY
> (
> [Field_8]
> ) REFERENCES [dbo].[Table_2] (
> [ID]
> ),
> CONSTRAINT [FK_Field_7ToTable_1Field_7] FOREIGN KEY
> (
> [Field_7]
> ) REFERENCES [dbo].[Table_3] (
> [ID]
> ) ON UPDATE CASCADE
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> CREATE UNIQUE INDEX [UN_Table_1_Field_10_Field_11] ON
> [dbo].[Table_1]([Field_10], [Field_11]) ON [PRIMARY]
> GO
>
>
>
> alter table [dbo].[Table_1] nocheck constraint
> [FK_Table_1IDToTable_1AliasTable_1ID]
> GO
>
> alter table [dbo].[Table_1] nocheck constraint [FK_Table_2IDToTable_4ID]
> GO
>
>

12 Sep 2005 11:37 AM

Dave

Let me say up front I'm not a DBA.
I'm a Software Engineer that was handed this to implement.
I think that something got lost in the cut and paste so I will try again.

Before I do that, I didn't understand what you were getting at.
Field_7 is a smallint and Field_6 is the text.

Any help is appreciated.

Dave

================================================

CREATE TABLE [dbo].[Table_1] (
..    [ID] [uniqueidentifier] NOT NULL ,
..    [Field_1] [uniqueidentifier] NULL ,
..    [Field_2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
..    [Field_3] [bit] NOT NULL ,
..    [Field_4] [bit] NOT NULL ,
..    [Field_5] [bit] NOT NULL ,
..    [Field_6] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
..    [Field_7] [smallint] NOT NULL ,
..    [Field_8] [uniqueidentifier] NULL ,
..    [Field_9] [uniqueidentifier] NULL ,
..    [Field_10] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
..    [Field_11] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table_1] ADD
..    CONSTRAINT [DF_Table_1_Field_3] DEFAULT (0) FOR [Field_3],
..    CONSTRAINT [DF_Table_1_Field_4] DEFAULT (0) FOR [Field_4],
..    CONSTRAINT [DF_Table_1_Field_5] DEFAULT (0) FOR [Field_5],
..    CONSTRAINT [PK_Table_1] PRIMARY KEY NONCLUSTERED
..    (
..        [ID]
..    ) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [UN_Table_1_Field_10_Field_11] ON
[dbo].[Table_1]([Field_10], [Field_11]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table_1] ADD
..    CONSTRAINT [FK_Table_1IDToTable_1Field_1] FOREIGN KEY
..    (
..        [Field_1]
..    ) REFERENCES [dbo].[Table_1] (
..        [ID]
..    ),
..    CONSTRAINT [FK_Table_2IDToTable_3] FOREIGN KEY
..    (
..        [Field_8]
..    ) REFERENCES [dbo].[Table_2] (
..        [ID]
..    ),
..    CONSTRAINT [FK_Table_4_3ToTable_1Field_7] FOREIGN KEY
..    (
..        [Field_7]
..    ) REFERENCES [dbo].[Table_4] (
..        [ID]
..    ) ON UPDATE CASCADE
GO

alter table [dbo].[Table_1] nocheck constraint [FK_Table_1IDToTable_1Field_1]
GO

alter table [dbo].[Table_1] nocheck constraint [FK_Table_2IDToTable_3]
GO

====================================================

Show quote

"oj" wrote:

> This must be an oversight, right.
>
> CONSTRAINT [FK_Field_7ToTable_1Field_7] FOREIGN KEY
> (
> [Field_7]
> ) REFERENCES [dbo].[Table_3] (
> [ID]
> ) ON UPDATE CASCADE
>
> You're creating a fk on a text column that references an id column with
> cascade updating? Removing the dri should at least stop the error from
> raising. Though, I still question you about the choice for using blob for
> your key.
>
> --
> -oj
>
>
>
> "Dave" <D***@discussions.microsoft.com> wrote in message
> news:5AB8AB5F-4793-4274-A257-C3B16AE2589D@microsoft.com...
> >I am getting a SQL Server Error: 8629
> >    "The query processor could not produce a query plan from the optimizer
> >    because a query cannot update a text, ntext, or image column and a
> >    clustering key at the same time."
> >
> > I turned the clustering option off for this key and that worked but have
> > no idea why I need to, what the repercussions are, or if there is another
> > way to do this.
> >
> > I am trying to insert the >> very first << row of the table using .NET
> > and SQL Server 2000.
> >
> > Here is the layout of my table.
> >
> > Any thoughts are much appreciated.
> >
> > Dave
> >
> > ===========================================
> >
> >
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[Table_1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> > drop table [dbo].[Table_1]
> > GO
> >
> > CREATE TABLE [dbo].[Table_1] (
> > [ID] [uniqueidentifier] NOT NULL ,
> > [Field_1] [uniqueidentifier] NULL ,
> > [Field_2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [Field_3] [bit] NOT NULL CONSTRAINT [DF_Table_1_Field_3] DEFAULT (0),
> > [Field_4] [bit] NOT NULL CONSTRAINT [DF_Table_1_Field_4] DEFAULT (0),
> > [Field_5] [bit] NOT NULL CONSTRAINT [DF_Table_1_Field_5] DEFAULT (0),
> > [Field_6] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [Field_7] [smallint] NOT NULL ,
> > [Field_8] [uniqueidentifier] NULL ,
> > [Field_9] [uniqueidentifier] NULL ,
> > [Field_10] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [Field_11] [int] NOT NULL ,
> > CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
> > (
> > [ID]
> > ) ON [PRIMARY] ,
> > CONSTRAINT [FK_Table_1IDToTable_1AliasTable_1ID] FOREIGN KEY
> > (
> > [Field_1]
> > ) REFERENCES [dbo].[Table_1] (
> > [ID]
> > ),
> > CONSTRAINT [FK_Table_2IDToTable_4ID] FOREIGN KEY
> > (
> > [Field_8]
> > ) REFERENCES [dbo].[Table_2] (
> > [ID]
> > ),
> > CONSTRAINT [FK_Field_7ToTable_1Field_7] FOREIGN KEY
> > (
> > [Field_7]
> > ) REFERENCES [dbo].[Table_3] (
> > [ID]
> > ) ON UPDATE CASCADE
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> > GO
> >
> > CREATE UNIQUE INDEX [UN_Table_1_Field_10_Field_11] ON
> > [dbo].[Table_1]([Field_10], [Field_11]) ON [PRIMARY]
> > GO
> >
> >
> >
> > alter table [dbo].[Table_1] nocheck constraint
> > [FK_Table_1IDToTable_1AliasTable_1ID]
> > GO
> >
> > alter table [dbo].[Table_1] nocheck constraint [FK_Table_2IDToTable_4ID]
> > GO
> >
> >
>
>
>

14 Sep 2005 1:43 AM

My apology. I misread your original post and thought Field_7 was defined as
Text and you were making it a FK to another table.

Anyhow, I was not able to repro. Perhaps, you have a trigger that does
something with the text column.

Here is the repro script that works just fine.

create table Table_2(ID uniqueidentifier primary key)
create table Table_4(ID smallint primary key)
go

CREATE TABLE [dbo].[Table_1] (
[ID] [uniqueidentifier] NOT NULL ,
[Field_1] [uniqueidentifier] NULL ,
[Field_2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field_3] [bit] NOT NULL ,
[Field_4] [bit] NOT NULL ,
[Field_5] [bit] NOT NULL ,
[Field_6] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field_7] [smallint] NOT NULL ,
[Field_8] [uniqueidentifier] NULL ,
[Field_9] [uniqueidentifier] NULL ,
[Field_10] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Field_11] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table_1] ADD
CONSTRAINT [DF_Table_1_Field_3] DEFAULT (0) FOR [Field_3],
CONSTRAINT [DF_Table_1_Field_4] DEFAULT (0) FOR [Field_4],
CONSTRAINT [DF_Table_1_Field_5] DEFAULT (0) FOR [Field_5],
CONSTRAINT [PK_Table_1] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [UN_Table_1_Field_10_Field_11] ON
[dbo].[Table_1]([Field_10], [Field_11]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table_1] ADD
CONSTRAINT [FK_Table_1IDToTable_1Field_1] FOREIGN KEY
(
[Field_1]
) REFERENCES [dbo].[Table_1] (
[ID]
),
CONSTRAINT [FK_Table_2IDToTable_3] FOREIGN KEY
(
[Field_8]
) REFERENCES [dbo].[Table_2] (
[ID]
),
CONSTRAINT [FK_Table_4_3ToTable_1Field_7] FOREIGN KEY
(
[Field_7]
) REFERENCES [dbo].[Table_4] (
[ID]
) ON UPDATE CASCADE
GO
alter table [dbo].[Table_1] nocheck constraint
[FK_Table_1IDToTable_1Field_1]
GO
alter table [dbo].[Table_1] nocheck constraint [FK_Table_2IDToTable_3]
GO
declare @n uniqueidentifier,@i smallint
set @i=1
set @n=newid()

insert Table_2 select @n
insert Table_4 select @i

insert Table_1(ID,Field_1,Field_7,Field_8,Field_10,Field_11)
select newid(),null,@i,@n,'abc',1
go
select * from Table_1

go
alter table Table_1 drop constraint FK_Table_4_3ToTable_1Field_7
alter table Table_1 drop constraint FK_Table_2IDToTable_3
go

drop table Table_1,Table_2,Table_4
go

--
-oj

Show quote

"Dave" <D***@discussions.microsoft.com> wrote in message
news:168816DB-290B-4098-AA9D-47DFF33AE4F1@microsoft.com...
> Let me say up front I'm not a DBA.
> I'm a Software Engineer that was handed this to implement.
> I think that something got lost in the cut and paste so I will try again.
>
> Before I do that, I didn't understand what you were getting at.
> Field_7 is a smallint and Field_6 is the text.
>
> Any help is appreciated.
>
> Dave