Hi !
We recently changed server to a SQL 2000 SP4 on Windows 2003 (Old was SP3
with Windows 2000)
Since the server change we've had Internal SQL Server error on a few Stored
procedures.
Server: Msg 8624, Level 16, State 1, Line 5
Internal SQL Server error.
We were able to create this script that can be run on any test environment.
Notes: Index View must have at least 3 index + clustered index
Header Table rerquires 2 Foreign Keys
Details also requires 2 Foreign Keys
Final Select (To get error) requires :
1 Temp table (Declare @. or Temp #)
4 joins to any tables
2 conditions in the where clause
-- Drop view Search_IVw
-- Drop Table Header
-- Drop Table Detail
-- Drop Table FK1
-- Create Table for Foreign Keys
Create Table FK1 (KeyId Int ,CONSTRAINT PK_KeyId PRIMARY KEY CLUSTERED
(KeyId))
Insert into FK1 Values (1)
Insert into FK1 Values (2)
-- Create Header Table
CREATE TABLE [Header] (
[POHId] [int] IDENTITY (1, 1) NOT NULL ,
[FK2] [int] NOT NULL ,
[FK3] [int] NULL ,
[Bit1] [bit] NOT NULL ,
[Int1] [int] NOT NULL ,
[Int2] [int] NULL ,
[Int3] [int] NULL ,
[FK1] [int] NULL ,
CONSTRAINT [PK_Header] PRIMARY KEY CLUSTERED ([POHId]) ON [PRIMARY] ,
CONSTRAINT [FK_Header_FK1] FOREIGN KEY ([FK1]) REFERENCES [FK1] ([KeyId]),
CONSTRAINT [FK_Header_FK2] FOREIGN KEY ([FK2]) REFERENCES [FK1] ([KeyId]),
CONSTRAINT [FK_Header_FK3] FOREIGN KEY ([FK3]) REFERENCES [FK1] ([KeyId])
) ON [PRIMARY]
GO
-- Create Detail Table
CREATE TABLE [Detail] (
[PODId] [int] IDENTITY (1, 1) NOT NULL ,
[FK1] [int] NOT NULL ,
[FK2] [int] NOT NULL ,
CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED ([PODId]) ON [PRIMARY] ,
CONSTRAINT [FK_Detail_FK1] FOREIGN KEY ([FK1]) REFERENCES [FK1] ([KeyId]),
CONSTRAINT [FK_Detail_FK2] FOREIGN KEY ([FK2]) REFERENCES [FK1] ([KeyId])
) ON [PRIMARY]
GO
-- Create Header table with at least 136977 entries
Create table #G (Number Int)
Declare @.A Int
Set @.A =0
Insert into #G Values(1)
While @.A <=17
Begin
Insert into #G Select 1 From #G
Set @.A = @.A + 1
End
Insert into Header Select top 200000 2, 1, 0, 1, 1, 1, 1 From #G
Drop Table #G
GO
-- Create Index view with at least 3 index + clustered index
CREATE View Search_IVw WITH SCHEMABINDING As
Select D.PODId,
H.POHId
From Dbo.Header H
join Dbo.Detail D on D.FK1 = H.POHId
Go
Create unique clustered index Search_IVw_Idx on Search_IVw (PODId)
Create index Search_IVw_Idx2 on Search_IVw (POHId)
Create index Search_IVw_Idx3 on Search_IVw (POHId)
Create index Search_IVw_Idx4 on Search_IVw (POHId)
GO
Declare @.Temp Table (KeyId Int)
Insert into Dbo.Detail
Select 1,
2
From Dbo.Header H (NoLock)
join @.Temp X on X.KeyId =H.FK1
join Dbo.FK1 DI1 (NoLock) on DI1.KeyId = 2
join Dbo.FK1 DI2 (NoLock) on DI2.KeyId = 2
join Dbo.FK1 DI3 (NoLock) on DI3.KeyId = 2
Where H.FK3 =1
and H.FK2=1
--
Thank You
Hartco Oasys TeamWe found this issue post sp3a and Microsoft support identified it as a bug
and relased a private HOTFIX for us. As per MS support, this bug exists in
SP4 as well. So you may have to open a case with MS support.
"Hartco Developers" <HartcoDevelopers@.discussions.microsoft.com> wrote in
message news:30445DFB-C7DE-4275-BA4C-05662EC420C3@.microsoft.com...
> Hi !
> We recently changed server to a SQL 2000 SP4 on Windows 2003 (Old was SP3
> with Windows 2000)
> Since the server change we've had Internal SQL Server error on a few
> Stored
> procedures.
> Server: Msg 8624, Level 16, State 1, Line 5
> Internal SQL Server error.
>
> We were able to create this script that can be run on any test
> environment.
> Notes: Index View must have at least 3 index + clustered index
> Header Table rerquires 2 Foreign Keys
> Details also requires 2 Foreign Keys
> Final Select (To get error) requires :
> 1 Temp table (Declare @. or Temp #)
> 4 joins to any tables
> 2 conditions in the where clause
>
> -- Drop view Search_IVw
> -- Drop Table Header
> -- Drop Table Detail
> -- Drop Table FK1
> -- Create Table for Foreign Keys
> Create Table FK1 (KeyId Int ,CONSTRAINT PK_KeyId PRIMARY KEY CLUSTERED
> (KeyId))
> Insert into FK1 Values (1)
> Insert into FK1 Values (2)
> -- Create Header Table
> CREATE TABLE [Header] (
> [POHId] [int] IDENTITY (1, 1) NOT NULL ,
> [FK2] [int] NOT NULL ,
> [FK3] [int] NULL ,
> [Bit1] [bit] NOT NULL ,
> [Int1] [int] NOT NULL ,
> [Int2] [int] NULL ,
> [Int3] [int] NULL ,
> [FK1] [int] NULL ,
> CONSTRAINT [PK_Header] PRIMARY KEY CLUSTERED ([POHId]) ON [PRIMARY] ,
> CONSTRAINT [FK_Header_FK1] FOREIGN KEY ([FK1]) REFERENCES [FK1] ([KeyId]),
> CONSTRAINT [FK_Header_FK2] FOREIGN KEY ([FK2]) REFERENCES [FK1] ([KeyId]),
> CONSTRAINT [FK_Header_FK3] FOREIGN KEY ([FK3]) REFERENCES [FK1] ([KeyId])
> ) ON [PRIMARY]
> GO
> -- Create Detail Table
> CREATE TABLE [Detail] (
> [PODId] [int] IDENTITY (1, 1) NOT NULL ,
> [FK1] [int] NOT NULL ,
> [FK2] [int] NOT NULL ,
> CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED ([PODId]) ON [PRIMARY] ,
> CONSTRAINT [FK_Detail_FK1] FOREIGN KEY ([FK1]) REFERENCES [FK1] ([KeyId]),
> CONSTRAINT [FK_Detail_FK2] FOREIGN KEY ([FK2]) REFERENCES [FK1] ([KeyId])
> ) ON [PRIMARY]
> GO
> -- Create Header table with at least 136977 entries
> Create table #G (Number Int)
> Declare @.A Int
> Set @.A =0
> Insert into #G Values(1)
> While @.A <=17
> Begin
> Insert into #G Select 1 From #G
> Set @.A = @.A + 1
> End
> Insert into Header Select top 200000 2, 1, 0, 1, 1, 1, 1 From #G
> Drop Table #G
> GO
> -- Create Index view with at least 3 index + clustered index
> CREATE View Search_IVw WITH SCHEMABINDING As
> Select D.PODId,
> H.POHId
> From Dbo.Header H
> join Dbo.Detail D on D.FK1 = H.POHId
> Go
> Create unique clustered index Search_IVw_Idx on Search_IVw (PODId)
> Create index Search_IVw_Idx2 on Search_IVw (POHId)
> Create index Search_IVw_Idx3 on Search_IVw (POHId)
> Create index Search_IVw_Idx4 on Search_IVw (POHId)
> GO
> Declare @.Temp Table (KeyId Int)
> Insert into Dbo.Detail
> Select 1,
> 2
> From Dbo.Header H (NoLock)
> join @.Temp X on X.KeyId =H.FK1
> join Dbo.FK1 DI1 (NoLock) on DI1.KeyId = 2
> join Dbo.FK1 DI2 (NoLock) on DI2.KeyId = 2
> join Dbo.FK1 DI3 (NoLock) on DI3.KeyId = 2
> Where H.FK3 =1
> and H.FK2=1
>
> --
> Thank You
> Hartco Oasys Team
