EXCEPTION_ACCESS_VIOLATION using ntext

I get the following error when i execute the SQL below.
It works if the sp parameter is text rather than ntext or if the update uses
the full key.
(Windows 2K sp4, sql 2k sp3 no hot fixes)
================================================== === BugCheck Dump
================================================== ===
This file is generated by Microsoft SQL Server 8.00.760
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is AT/AT COMPATIBLE.
Current time is 11:23:50 09/21/04.
1 Intel x86 level 15, 3 Mhz processor(s).
Windows NT 5.0 Build 2195 CSD Service Pack 4.
Memory
MemoryLoad = 80%
Total Physical = 1021 MB
Available Physical = 196 MB
Total Page File = 1326 MB
Available Page File = 645 MB
Total Virtual = 2047 MB
Available Virtual = 948 MB
*Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDu
mp0170.txt
*
************************************************** ***************************
**
*
* BEGIN STACK DUMP:
* 09/21/04 11:23:50 spid 54
*
* Exception Address = 00000000
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
* Input Buffer 82 bytes -
* exec spStoreCompanyTest 1, 2, 3, N'test'
================Test.sql=========================
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblCompanyTest]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblCompanyTest]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[spStoreCompanyTest]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[spStoreCompanyTest]
GO
CREATE TABLE [dbo].[tblCompanyTest] (
[source] [int] NOT NULL ,
[feed] [int] NOT NULL ,
[code] [int] NOT NULL ,
[sourceData] [ntext] COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCompanyTest] WITH NOCHECK ADD
CONSTRAINT [PK_tblCompanyTest] PRIMARY KEY CLUSTERED
(
[source],
[feed],
[code]
) ON [PRIMARY]
GO
CREATE PROCEDURE dbo.spStoreCompanyTest
@.source int,
@.feed int,
@.code int,
@.sourceData ntext --text
AS
IF NOT EXISTS(SELECT * FROM tblCompanyTest WHERE source = @.source AND feed
= @.feed AND code = @.code)
BEGIN
INSERT INTO tblCompanyTest
VALUES(@.source, @.feed, @.code, @.sourceData)
END
ELSE
BEGIN
UPDATE tblCompanyTest
SET sourceData = @.sourceData
WHERE source = @.source
AND feed = @.feed
--and code = @.code
END
RETURN
GO
exec spStoreCompanyTest 1, 2, 3, N'test'
GO
exec spStoreCompanyTest 1, 2, 3, N'test'
GO
> I get the following error when i execute the SQL below.
> It works if the sp parameter is text rather than ntext or if the update
uses
> the full key.
> (Windows 2K sp4, sql 2k sp3 no hot fixes)
> ================================================== === BugCheck Dump

> ================================================== ===

> This file is generated by Microsoft SQL Server 8.00.760

> upon detection of fatal unexpected error. Please return this file,

> the query or program that produced the bugcheck, the database and

> the error log, and any other pertinent information with a Service
Request.
>

> Computer type is AT/AT COMPATIBLE.

> Current time is 11:23:50 09/21/04.

> 1 Intel x86 level 15, 3 Mhz processor(s).

> Windows NT 5.0 Build 2195 CSD Service Pack 4.

>
> Memory
> MemoryLoad = 80%
> Total Physical = 1021 MB
> Available Physical = 196 MB
> Total Page File = 1326 MB
> Available Page File = 645 MB
> Total Virtual = 2047 MB
> Available Virtual = 948 MB
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
> Server\MSSQL\log\SQLDu
> mp0170.txt

> *
>
************************************************** **************************
*
> **

> *

> * BEGIN STACK DUMP:

> * 09/21/04 11:23:50 spid 54

> *

> * Exception Address = 00000000

> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION

> * Access Violation occurred reading address 00000000

> * Input Buffer 82 bytes -

> * exec spStoreCompanyTest 1, 2, 3, N'test'

>
> ================Test.sql=========================
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblCompanyTest]') and OBJECTPROPERTY(id,
N'IsUserTable') =
> 1)
> drop table [dbo].[tblCompanyTest]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[spStoreCompanyTest]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[spStoreCompanyTest]
> GO
> CREATE TABLE [dbo].[tblCompanyTest] (
> [source] [int] NOT NULL ,
> [feed] [int] NOT NULL ,
> [code] [int] NOT NULL ,
> [sourceData] [ntext] COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblCompanyTest] WITH NOCHECK ADD
> CONSTRAINT [PK_tblCompanyTest] PRIMARY KEY CLUSTERED
> (
> [source],
> [feed],
> [code]
> ) ON [PRIMARY]
> GO
>
> CREATE PROCEDURE dbo.spStoreCompanyTest
> @.source int,
> @.feed int,
> @.code int,
> @.sourceData ntext --text
> AS
> IF NOT EXISTS(SELECT * FROM tblCompanyTest WHERE source = @.source AND
feed
> = @.feed AND code = @.code)
> BEGIN
> INSERT INTO tblCompanyTest
> VALUES(@.source, @.feed, @.code, @.sourceData)
> END
> ELSE
> BEGIN
> UPDATE tblCompanyTest
> SET sourceData = @.sourceData
> WHERE source = @.source
> AND feed = @.feed
> --and code = @.code
> END
> RETURN
> GO
> exec spStoreCompanyTest 1, 2, 3, N'test'
> GO
> exec spStoreCompanyTest 1, 2, 3, N'test'
> GO
>
Paste the whole sqldmpxxxx.txt and I'll give it a go.
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.