Home > Archive > MS SQL Server > October 2006 > 2000 to 2005: The query processor could not produce a query plan









You are viewing an archived Text-only version of the thread. To view this thread in it's original format and/or if you want to reply to this thread please [click here]

 

Author 2000 to 2005: The query processor could not produce a query plan
lindseyc5

2006-10-24, 6:28 pm

Hi,

We are trying to upgrade from 2000 to 2005 and get the following error
message when we try to execute an insert statement against certain tables in
a query window.

The query processor could not produce a query plan

If we change the definition of the table (i.e. modifying a column, adding a
new column, etc.), the insert statement works fine. If we script out the
creation of the table, drop it, and then recreate it, the insert statement
works fine.

The database was created initially by detaching the DB in 2000 and then
attaching it in 2005. We have also tried backing it up in 2000 and then
restoring it in 2005 and both methods produce the same error.

We are running SQL Server 2005 Dev Edition SP1 on Windows XP Media Center,
but have also tried it on a Windows 2003 Server.

Any suggestions?

Below is the table definition and the insert statement that fails.

Thanks
Lindsey

CREATE TABLE [dbo].[tmpnmcrs](
[nmcrs_rid] [char](16) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT NULL,
[soc_sec] [char](9) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[sid] [char](16) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[sch_yr] [char](6) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[crstypcod] [char](2) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[course] [char](8) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[crs_txt] [char](50) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[transfer] [bit] NULL,
[section] [char](2) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[semester] [char](2) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[sm_desc] [char](10) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[grade] [char](5) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[aclas_grad] [char](2) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[class_grad] [float] NULL,
[clin_grade] [char](2) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[officialg] [bit] NULL,
[rpt_grd] [bit] NULL,
[rpt_schlyr] [char](7) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[rpt_sem] [char](2) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[rpt_semyr] [char](8) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[date_comp] [datetime] NULL,
[cred_rec] [float] NULL,
[practice] [float] NULL,
[class_hrs] [float] NULL,
[qlty_pnts] [float] NULL,
[institut] [char](50) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT NULL,
[nmcrs_mem] [text] COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[posted] [bit] NULL,
[addfee] [bit] NULL,
[dropfee] [bit] NULL,
[post_date] [datetime] NULL,
[trans_no] [char](16) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[enrollcode] [char](2) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[enrol_date] [datetime] NULL,
[wait_date] [datetime] NULL,
[with_date] [datetime] NULL,
[ce_creds] [float] NULL,
[occurance] [float] NULL,
[orig_cred] [float] NULL,
[invoice_no] [char](16) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[del_flag] [bit] NULL,
[nmcrs_ce1] [char](10) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[nmcrs_ce2] [char](10) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[nmcrs_ce3] [char](10) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[nmcrs_le1] [bit] NULL,
[nmcrs_ne1] [float] NULL,
[weight_id] [char](10) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[date_maint] [datetime] NULL,
[time_maint] [char](8) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[operator] [char](8) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[sec_id] [char](25) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
& #91;timestamp_column
] [timestamp] NULL,
& #91;crs_authorize_co
d] [char](1) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT NULL
CONSTRAINT & #91;DF_tmpnmcrs_crs_
authorize_cod] DEFAULT ('N'),
[no_repeat] [bit] NULL,
[timeout] [datetime] NULL,
[tmpnmcrs_ce1] [char](10) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[tmpnmcrs_le1] [bit] NULL,
[tmpnmcrs_ne1] [int] NULL,
[tmpnmcrs_de1] [datetime] NULL,
[tmpnmcrs_fe1] [float] NULL,
[tmpnmcrs_ce2] [char](10) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[tmpnmcrs_ce3] [char](20) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[incqpa] [bit] NOT NULL DEFAULT (1),
[edu_rid] [char](16) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT NULL DEFAULT
(' '),
[pf] [bit] NOT NULL DEFAULT (0),
CONSTRAINT [PK_tmpnmcrs] PRIMARY KEY CLUSTERED
(
[nmcrs_rid] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

insert into tmpnmcrs(nmcrs_rid, soc_sec, sid, institut, crs_authorize_cod,
incqpa, edu_rid, pf)
values('020214402425
965F', 'AN8321568','2001120
401389407', 'SonisWeb
University', 'N', 1, '020529349698124N', 0)

Narayana Vyas Kondreddi

2006-10-24, 6:28 pm

Check KB article 916795 to see if it applies to your situation
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/



"lindseyc5" < lindseyc5@discussion
s.microsoft.com> wrote in message
news:5C506815-743C-4EDC-BB7F- D0D38F1B5259@microso
ft.com...
> Hi,
>
> We are trying to upgrade from 2000 to 2005 and get the following error
> message when we try to execute an insert statement against certain tables
> in
> a query window.
>
> The query processor could not produce a query plan
>
> If we change the definition of the table (i.e. modifying a column, adding
> a
> new column, etc.), the insert statement works fine. If we script out the
> creation of the table, drop it, and then recreate it, the insert statement
> works fine.
>
> The database was created initially by detaching the DB in 2000 and then
> attaching it in 2005. We have also tried backing it up in 2000 and then
> restoring it in 2005 and both methods produce the same error.
>
> We are running SQL Server 2005 Dev Edition SP1 on Windows XP Media Center,
> but have also tried it on a Windows 2003 Server.
>
> Any suggestions?
>
> Below is the table definition and the insert statement that fails.
>
> Thanks
> Lindsey
>
> CREATE TABLE [dbo].[tmpnmcrs](
> [nmcrs_rid] [char](16) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT NULL,
> [soc_sec] [char](9) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [sid] [char](16) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [sch_yr] [char](6) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [crstypcod] [char](2) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [course] [char](8) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [crs_txt] [char](50) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [transfer] [bit] NULL,
> [section] [char](2) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [semester] [char](2) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [sm_desc] [char](10) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [grade] [char](5) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [aclas_grad] [char](2) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [class_grad] [float] NULL,
> [clin_grade] [char](2) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [officialg] [bit] NULL,
> [rpt_grd] [bit] NULL,
> [rpt_schlyr] [char](7) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [rpt_sem] [char](2) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [rpt_semyr] [char](8) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [date_comp] [datetime] NULL,
> [cred_rec] [float] NULL,
> [practice] [float] NULL,
> [class_hrs] [float] NULL,
> [qlty_pnts] [float] NULL,
> [institut] [char](50) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT NULL,
> [nmcrs_mem] [text] COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [posted] [bit] NULL,
> [addfee] [bit] NULL,
> [dropfee] [bit] NULL,
> [post_date] [datetime] NULL,
> [trans_no] [char](16) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [enrollcode] [char](2) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [enrol_date] [datetime] NULL,
> [wait_date] [datetime] NULL,
> [with_date] [datetime] NULL,
> [ce_creds] [float] NULL,
> [occurance] [float] NULL,
> [orig_cred] [float] NULL,
> [invoice_no] [char](16) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [del_flag] [bit] NULL,
> [nmcrs_ce1] [char](10) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [nmcrs_ce2] [char](10) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [nmcrs_ce3] [char](10) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [nmcrs_le1] [bit] NULL,
> [nmcrs_ne1] [float] NULL,
> [weight_id] [char](10) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [date_maint] [datetime] NULL,
> [time_maint] [char](8) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [operator] [char](8) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [sec_id] [char](25) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> & #91;timestamp_column
] [timestamp] NULL,
> & #91;crs_authorize_co
d] [char](1) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT
> NULL
> CONSTRAINT & #91;DF_tmpnmcrs_crs_
authorize_cod] DEFAULT ('N'),
> [no_repeat] [bit] NULL,
> [timeout] [datetime] NULL,
> [tmpnmcrs_ce1] [char](10) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [tmpnmcrs_le1] [bit] NULL,
> [tmpnmcrs_ne1] [int] NULL,
> [tmpnmcrs_de1] [datetime] NULL,
> [tmpnmcrs_fe1] [float] NULL,
> [tmpnmcrs_ce2] [char](10) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [tmpnmcrs_ce3] [char](20) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
> [incqpa] [bit] NOT NULL DEFAULT (1),
> [edu_rid] [char](16) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT NULL DEFAULT
> (' '),
> [pf] [bit] NOT NULL DEFAULT (0),
> CONSTRAINT [PK_tmpnmcrs] PRIMARY KEY CLUSTERED
> (
> [nmcrs_rid] ASC
> )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
> insert into tmpnmcrs(nmcrs_rid, soc_sec, sid, institut, crs_authorize_cod,
> incqpa, edu_rid, pf)
> values('020214402425
965F', 'AN8321568','2001120
401389407', 'SonisWeb
> University', 'N', 1, '020529349698124N', 0)
>


Sponsored Links





Also available: Server administration forum archive | Web Design forum archive | Software forum archive | Hardware reviews archive | Programming forum archive

Copyright 2009 droptable.com