October 12, 2005 at 2:36 pm
Hi all, I've noticed this behavior today and I think I pinpointed the reason. But I cannot understand why this is happening. I would really appreciate if someone could shed some light on this for me.
We have a table that has instead of insert trigger on it. Whenever someone is running an insert with order by the query fails with AV. If the trigger is disabled the query executes fine. Here is the script below to recreate the problem. We are running sql2k sp4 on win2003
/********************************************************/
-- create table
CREATE TABLE [dbo].[test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[refGeoID] [int] NOT NULL ,
[Action] [char] (1) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[test] WITH NOCHECK ADD
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[ID] )
ON [PRIMARY]
GO
ALTER TABLE [dbo].[test] ADD
CONSTRAINT [DF_test_DateInserted] DEFAULT (getdate()) FOR [DateInserted]
GO
-- create trigger
create TRIGGER test_I_Trig ON dbo.test
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
DELETE FROM dbo.test
From dbo.test JT Join inserted I On I.[ID] = JT.[ID]
INSERT INTO dbo.test (refGeoID, [Action])
SELECT i.refGeoID, i.[Action]
FROM inserted i
set nocount off
end
-- insert data
insert into test (refGeoID, [Action])
select 1,'I'
union all
select 2,'I'
union all
select 3,'I'
union all
select 4,'I'
union all
select 5,'I'
union all
select 6,'I'
union all
select 7,'I'
union all
select 8,'I'
union all
select 9,'I'
union all
select 10,'I'
union all
select 11,'I'
union all
select 12,'I'
union all
select 13,'I'
union all
select 14,'I'
union all
select 15,'I'
union all
select 16,'I'
union all
select 17,'I'
union all
select 18,'I'
union all
select 19,'I'
union all
select 20,'I'
-- try to run an insert statement - it will fail
INSERT INTO dbo.test (refGeoID, [Action])
SELECT top 10 refGeoID, [Action] from test (nolock) order by id desc
-- now run the same query without order by - it works
INSERT INTO dbo.test (refGeoID, [Action])
SELECT top 10 refGeoID, [Action] from test (nolock) -- order by id desc
-- now drop the trigger and run the insert with order by - it works
drop TRIGGER test_I_Trig
INSERT INTO dbo.test (refGeoID, [Action])
SELECT top 10 refGeoID, [Action] from test (nolock) order by id desc
thank you
October 12, 2005 at 3:40 pm
I was able to reproduce the access violation under both 8.00.818 (SP3 with security patch MS03-031) and 8.00.2039 (SP4).
Recommend calling Microsoft Product Support. As you have a reproducable problem, there should be no charge.
P.S.
This contraint in the script references a column that does not exist.
ALTER TABLE [dbo].[test] ADD CONSTRAINT [DF_test_DateInserted] DEFAULT (getdate()) FOR [DateInserted]
GO
SQL = Scarcely Qualifies as a Language
October 13, 2005 at 6:16 am
Thank you Carl. Sorry about the constraint. I removed that column at the last minute.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply