SA Developer .NET

Hi

 I'm getting the following error when executing a stored procedure on SQL Server 2005:

"The query processor could not produce a query plan. For more information, contact Customer Support Services."

This is my stored procedure:

USE [DefectRelocator]

GO

/****** Object: StoredProcedure [dbo].[pr_MOVE_Defects_test] Script Date: 08/18/2008 09:36:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Jayan Kistasami

-- Create date: 2008-07-24

-- Description: Moves defects from source to destination.

-- =============================================

ALTER PROCEDURE [dbo].[pr_MOVE_Defects_test]

-- Add the parameters for the stored procedure here

@SourceProjectID int = 0,

@SourceDefectID int = 0,

@SourceReferenceDBTypeID int = 0,

@DestinationProjectID int = 0,

@DestinationReferenceDBTypeID int = 0

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

SET XACT_ABORT ON

 

BEGIN TRAN

 

 

DECLARE @NewDefectID int

CREATE TABLE #Defect (

DefectID INT,

Description VARCHAR(3000),

StepsToRepeatDefect VARCHAR(3000),

FrequencyID INT,

SeverityID INT,

TestTypeID INT,

TestTypeName VARCHAR(100),

DateAdded DATETIME,

DefectTypeID INT,

EnteredByID INT,

EnteredByUsername VARCHAR(100),

DeveloperID INT,

DeveloperUsername VARCHAR(100),

ResolutionStatus INT,

ResolutionStatusName VARCHAR(100),

DefectStatusID INT,

LanguageID INT,

SystemInfo VARCHAR(1000))

-- Insert statements for procedure here

-- DISC Streamline Derivco Actual

 

-- DTS Ash Gaming

INSERT #Defect (

--DefectID,

Description,

StepsToRepeatDefect,

FrequencyID,

SeverityID,

TestTypeID,

TestTypeName,

DateAdded,

DefectTypeID,

EnteredByID,

EnteredByUsername,

DeveloperID,

DeveloperUsername,

ResolutionStatus,

ResolutionStatusName,

DefectStatusID,

LanguageID,

SystemInfo)

SELECT

--d.DefectID AS DefectID,

d.Description AS Description,

d.StepsToRepeatDefect AS StepsToRepeatDefect,

--d.ProductID AS ProductID,

d.FrequencyID AS FrequencyID,

d.SeverityID AS SeverityID,

d.TestTypeID AS TestTypeID,

(SELECT TOP 1 Description FROM Dertest17.DTS_AshGaming.dbo.tb_TestType WHERE TestTypeID = d.TestTypeID) AS TestTypeName,

--(SELECT TT.Description FROM Dertest17.DTS_AshGaming.dbo.tb_TestType TT WHERE TT.TestTypeID = d.TestTypeID) AS TestTypeName,

d.DateAdded AS DateAdded,

--d.TestCycle AS TestCycle,

d.DefectTypeID AS DefectTypeID,

d.EnteredByID AS EnteredByID,

(SELECT TOP 1 Opname FROM Dertest17.DTS_AshGaming.dbo.tb_Operator WHERE OperatorID = d.EnteredByID) AS EnteredByUsername,

d.DeveloperID AS DeveloperID,

(SELECT Opname FROM Dertest17.DTS_AshGaming.dbo.tb_Operator WHERE OperatorID = d.DeveloperID) AS DeveloperUsername,

d.ResolutionStatusID AS ResolutionStatus,

(SELECT TOP 1 Description FROM Dertest17.DTS_AshGaming.dbo.tb_ResolutionStatus WHERE ResolutionStatusID = d.ResolutionStatusID) AS ResolutionStatusName,

d.DefectStatusID AS StatusID,

null,

d.SystemInfo AS SystemInfo

FROM

Dertest17.DTS_AshGaming.dbo.tb_DefectProjectMap dpm

INNER JOIN Dertest17.DTS_AshGaming.dbo.tb_Defect d ON d.defectID = dpm.defectID

WHERE

d.DefectID = @SourceDefectID

 

 

-- DTS Ludo Voipster

-------------------------------------------------------------------------------------

-- Insert the defect.

-------------------------------------------------------------------------------------

-- DISC Streamline Derivco Actual

 

INSERT Dertest17.TestPro.dbo.tb_TS_Defect (

--DefectID,

DefectDescription,

ExpectedResults,

StepsToRepeatDefect,

FrequencyID,

SeverityID,

TestTypeID,

DateAdded,

CauseCategoryID,

PriorityID,

PhaseDetectedID,

PhaseInjectedID,

ActivityID,

DefectTypeID,

EnteredByID,

DeveloperID,

ResolutionStatusID,

LanguageID,

SystemInfo,

FeatureTestedID,

DefectResolutionTime,

AcknowledgedBy)

SELECT

--DefectID,

Description,

'NB: Transfered from a DTS systems. No expected results.',

StepsToRepeatDefect,

FrequencyID,

SeverityID,

1,

DateAdded,

11,

3,

9,

9,

3,

DefectTypeID,

533,

1,

1,

LanguageID,

SystemInfo,

null,

null,

null

FROM

#Defect d

 

 

 

IF @@ERROR <> 0

BEGIN

ROLLBACK TRAN

RETURN

END

COMMIT TRAN

END

 

It seems to break on the 2nd insert as it works when I comment the 2nd insert out. I use the following parameters to execute this stored proc:

pr_MOVE_Defects_test 964,52338,2,835,1

 

I've been struggling with this error for a few days. I searched on google and they say that u can download a patch, but I am unable to apply this path as this is a critical database. Please help.

 

Regards,

Terence

Hi there,

On which INSERT do you get the error? On the one inserting into the temp table, or the one into the physical table?

Thanks,

---

The Question is the Answer, and the Answer is the Question!

It breaks on the insert into the physical table. I commented it out and replaced it with just selecting from the temp table and it worked fine. Seems to be some issue with the physical table.

Can you do a quick SELECT @@VERISON and report what it says?

---

Microsoft Xbox MVP | http://craign.net/

I get the following:

Microsoft SQL Server  2000 - 8.00.760 (Intel X86)   Dec 17 2002 14:22:05   Copyright (c) 1988-2003 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

In your original post you said SQL Server 2005, but you are actually running SQL Server 2000 Service Pack 3. You might want to consider upgrading to Service Pack 4 to fix bug described in KB841404. Alternatively use the workaround described in KB841404.

---

Microsoft Xbox MVP | http://craign.net/

I've tried using the FORCE ORDER workaround described but that doesn't seem to work. I did manage to get permission to install Service Pack 4 on this server, so I'll try that and let you know if it works. Thanks for your help.

Hi,

As far as I can tell it has something to do with the inner queries (especially the "TOP 1") you are using in the Temp table Select statement. I've read on MS forums that this is apparently a very generic error message and has been logged as a bug with MS for SQL Server 2005 SP1 (and probably RTM, as well). I would suggest maybe rewriting the query somehow, possibly making use of UDF functions to get that specific data instead of using a "SELECT TOP 1" inner query.

Hope this can help,

Regards

---

The Question is the Answer, and the Answer is the Question!

I tried removing all TOP 1 functions but it still produces the same error. Hopefully when I install Service Pack 4, it will pinpoint the exact error.

This isn't directly related to your problem but, why two inserts?

First you insert some rows into a temp table, then you insert all the rows from that temp table into a real one. They could easily be done in the same statement and you wouldn't need the temp table at all. Especially seeing as half the stuff put into the temp table isn't used in the second select.

From what I can see, unless I'm completely missing something, the proc can be simplified down to this:

ALTER PROCEDURE [dbo].[pr_MOVE_Defects_test]
-- Add the parameters for the stored procedure here
@SourceProjectID int = 0,
@SourceDefectID int = 0,
@SourceReferenceDBTypeID int = 0,
@DestinationProjectID int = 0,
@DestinationReferenceDBTypeID int = 0
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT Dertest17.TestPro.dbo.tb_TS_Defect (
        --DefectID,
        DefectDescription,
        ExpectedResults,
        StepsToRepeatDefect,
        FrequencyID,
        SeverityID,
        TestTypeID,
        DateAdded,
        CauseCategoryID,
        PriorityID,
        PhaseDetectedID,
        PhaseInjectedID,
        ActivityID,
        DefectTypeID,
        EnteredByID,
        DeveloperID,
        ResolutionStatusID,
        LanguageID,
        SystemInfo,
        FeatureTestedID,
        DefectResolutionTime,
        AcknowledgedBy
    )
    SELECT
        --d.DefectID AS DefectID,
        d.Description AS Description,
        'NB: Transfered from a DTS systems. No expected results.',
        d.StepsToRepeatDefect AS StepsToRepeatDefect,
        --d.ProductID AS ProductID,
        d.FrequencyID AS FrequencyID,
        d.SeverityID AS SeverityID,
        1,
        d.DateAdded AS DateAdded,
        11,
        3,
        9,
        9,
        3,
        d.DefectTypeID AS DefectTypeID,
        533,
        1,
        1,
        NULL,
        d.SystemInfo AS SystemInfo,
        NULL,
        NULL,
        NULL
    FROM
        Dertest17.DTS_AshGaming.dbo.tb_DefectProjectMap dpm
        INNER JOIN Dertest17.DTS_AshGaming.dbo.tb_Defect d ON d.defectID = dpm.defectID
    WHERE
        d.DefectID = @SourceDefectID

END

No transaction, because it's now a single statement.

---

Gail Shaw - SQL In the Wild
SQL Server MVP
--
Chaos, panic and disorder. My job here is done!

Thanks. You are right. It only requires 1 insert statement. However, I tried it and it still gives the same error. I've also installed Service Pack 4 last night. When I selec @@version, the following is produced now:

Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)   May  3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

The Service Pack doesn't seem to have made any difference as I still get the error.

That's a dead simple insert. Optimiser shouldn't have a problem with it...

Two things to check please.

First, is there a trigger on the destination table?
Second, if you create a test table in your local DB and do the second insert to that, does it still give an error?
 

---

Gail Shaw - SQL In the Wild
SQL Server MVP
--
Chaos, panic and disorder. My job here is done!

Yes, there is a trigger on the destination table that inserts into the audit table. The scriopt for it is as follows:

USE [TestPro]

GO

/****** Object: Trigger [tr_Update2Defect] Script Date: 08/19/2008 10:20:10 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create TRIGGER [tr_Update2Defect]

ON [dbo].[tb_TS_Defect]

AFTER UPDATE

AS

DECLARE @Error int

SET @Error = 0

BEGIN TRANSACTION

 

INSERT INTO [TestPro].[dbo].[tb_TS_Audit_Defect]

([DefectID]

,[DefectDescription]

,[ExpectedResults]

-- ,[StepsToRepeatDefect]

,[FrequencyID]

,[SeverityID]

,[TestTypeID]

,[DateAdded]

,[CauseCategoryID]

,[PriorityID]

,[PhaseDetectedID]

,[PhaseInjectedID]

,[ActivityID]

,[DefectTypeID]

,[EnteredByID]

,[DeveloperID]

,[LanguageID]

,[SystemInfo]

,[ResolutionStatusID]

,[FeatureTestedID]

,[DefectResolutionTime]

,[ModifiedDate])

SELECT [DefectID]

,[DefectDescription]

,[ExpectedResults]

-- ,cast([StepsToRepeatDefect] as Varchar(Max))

,[FrequencyID]

,[SeverityID]

,[TestTypeID]

,[DateAdded]

,[CauseCategoryID]

,[PriorityID]

,[PhaseDetectedID]

,[PhaseInjectedID]

,[ActivityID]

,[DefectTypeID]

,[EnteredByID]

,[DeveloperID]

,[LanguageID]

,[SystemInfo]

,[ResolutionStatusID]

,[FeatureTestedID]

,[DefectResolutionTime]

,GetDate()

FROM deleted

SELECT @Error = @@ERROR

IF @Error <> 0

BEGIN

RAISERROR ('Error in update after trigger for tb_TS_Defect', 16, 1)

ROLLBACK TRANSACTION

END

ELSE

COMMIT TRANSACTION

 

 

I tried creating the table on the local DB without any primary/foreign keys or any other constraints. I ran the stored procedure and it worked fine and inserted the record into the table.

 

It seems that there must be some sort of contraint on the destination table then. What do you think?

Could be the constraints, could be that it's remote.

That trigger's an after update. it won't fire for an insert. Is there an after insert trigger as well?

---

Gail Shaw - SQL In the Wild
SQL Server MVP
--
Chaos, panic and disorder. My job here is done!

No, there are no other triggers on that table.

Dertest17.TestPro is a SQL Server 2005 database and the database where the stored procedure

"pr_MOVE_Defects_test" is located is a SQL Server 2000 database. I think that it might be the problem.