Access Violoation

The following fairly basic stored proc runs perfectly on my laptop (SQL
Server 2000 - 8.00.194 Personal Edition), but on my dev server (SQL Server
2000 - 8.00.760 Enterprise Edition, SP3) I get: SqlDumpExceptionHandler:
Process xx generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process..
Any help is most appreciated.
CREATE Procedure oContent_ins_Attachment
@.int_AttachmentTypeID int,
@.str_LinkPath text,
@.str_UserID varchar(100),
@.str_HistoryUserID varchar(100),
@.byt_FileContent image = null,
@.int_FileLength int = 0,
@.str_FileType varchar(50) = null,
@.str_LinkTitle varchar(255) = null,
@.str_FileName varchar(100) = null,
@.str_FileExt varchar(10) = null,
@.int_AttachmentID int output
AS
BEGIN
SET NOCOUNT ON
declare @.str_HyperlinkPath varchar(1000)
begin tran
select @.int_AttachmentID = isnull(max(AttachmentID),0)+1 from
AttachmentDetail
if @.@.error != 0
begin
if @.@.trancount > 0
begin
rollback tran
select @.int_AttachmentID = 0
return -1
end
else
return -1
end
if @.str_FileExt is null
insert into AttachmentDetail (AttachmentID, AttachmentTypeID, LinkPath,
LinkDate, LinkTitle, UserID, FileName)
values (@.int_AttachmentID, @.int_AttachmentTypeID, @.str_LinkPath,
getdate(), @.str_LinkTitle, @.str_UserID, @.str_FileName)
else
insert into AttachmentDetail (AttachmentID, AttachmentTypeID, LinkPath,
LinkDate, LinkTitle, UserID, FileName)
values (@.int_AttachmentID,
@.int_AttachmentTypeID,
convert(varchar(1000),@.str_LinkPath) +
convert(varchar(1000),@.int_AttachmentID) + @.str_FileExt,
getdate(),
@.str_LinkTitle,
@.str_UserID,
@.str_FileName)
if @.@.error != 0
begin
if @.@.trancount > 0
begin
rollback tran
select @.int_AttachmentID = 0
return -2
end
else
return -2
end
else
if @.@.trancount > 0
commit tran
insert into AttachmentDetail_H
select *, @.str_HistoryUserID, 'Inserted', getdate(), null
from AttachmentDetail
where AttachmentID = @.int_AttachmentID
if @.byt_FileContent is not null
begin
insert into AttachedFiles (AttachmentID, FileContent, FileSize, MimeType)
values (@.int_AttachmentID, @.byt_FileContent, @.int_FileLength,
@.str_FileType)
insert into AttachedFiles_H
select *, @.str_HistoryUserID, 'Inserted', getdate(), null
from AttachedFiles
where AttachmentID = @.int_AttachmentID
end
SET NOCOUNT OFF
ENDIf you give a full repro (e.g. table structure, sample data, an example call
to the proc, etc.) we can try to reproduce the AV on newer builds, so you
can know if it is already fixed on a post-SP3 build, or if you've found a
new bug.
See http://www.aspfaq.com/5006 for details.
(Also, update your laptop... you are practically begging to get infected by
Slammer.)
--
http://www.aspfaq.com/
(Reverse address to reply.)
"smay" <smay@.discussions.microsoft.com> wrote in message
news:4C55F48E-741E-41BF-8C13-37AE9EBBE5E2@.microsoft.com...
> The following fairly basic stored proc runs perfectly on my laptop (SQL
> Server 2000 - 8.00.194 Personal Edition), but on my dev server (SQL
Server
> 2000 - 8.00.760 Enterprise Edition, SP3) I get: SqlDumpExceptionHandler:
> Process xx generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
SQL
> Server is terminating this process..
> Any help is most appreciated.
> CREATE Procedure oContent_ins_Attachment
> @.int_AttachmentTypeID int,
> @.str_LinkPath text,
> @.str_UserID varchar(100),
> @.str_HistoryUserID varchar(100),
> @.byt_FileContent image = null,
> @.int_FileLength int = 0,
> @.str_FileType varchar(50) = null,
> @.str_LinkTitle varchar(255) = null,
> @.str_FileName varchar(100) = null,
> @.str_FileExt varchar(10) = null,
> @.int_AttachmentID int output
> AS
> BEGIN
> SET NOCOUNT ON
> declare @.str_HyperlinkPath varchar(1000)
> begin tran
> select @.int_AttachmentID = isnull(max(AttachmentID),0)+1 from
> AttachmentDetail
> if @.@.error != 0
> begin
> if @.@.trancount > 0
> begin
> rollback tran
> select @.int_AttachmentID = 0
> return -1
> end
> else
> return -1
> end
> if @.str_FileExt is null
> insert into AttachmentDetail (AttachmentID, AttachmentTypeID, LinkPath,
> LinkDate, LinkTitle, UserID, FileName)
> values (@.int_AttachmentID, @.int_AttachmentTypeID, @.str_LinkPath,
> getdate(), @.str_LinkTitle, @.str_UserID, @.str_FileName)
> else
> insert into AttachmentDetail (AttachmentID, AttachmentTypeID, LinkPath,
> LinkDate, LinkTitle, UserID, FileName)
> values (@.int_AttachmentID,
> @.int_AttachmentTypeID,
> convert(varchar(1000),@.str_LinkPath) +
> convert(varchar(1000),@.int_AttachmentID) + @.str_FileExt,
> getdate(),
> @.str_LinkTitle,
> @.str_UserID,
> @.str_FileName)
> if @.@.error != 0
> begin
> if @.@.trancount > 0
> begin
> rollback tran
> select @.int_AttachmentID = 0
> return -2
> end
> else
> return -2
> end
> else
> if @.@.trancount > 0
> commit tran
> insert into AttachmentDetail_H
> select *, @.str_HistoryUserID, 'Inserted', getdate(), null
> from AttachmentDetail
> where AttachmentID = @.int_AttachmentID
> if @.byt_FileContent is not null
> begin
> insert into AttachedFiles (AttachmentID, FileContent, FileSize, MimeType)
> values (@.int_AttachmentID, @.byt_FileContent, @.int_FileLength,
> @.str_FileType)
> insert into AttachedFiles_H
> select *, @.str_HistoryUserID, 'Inserted', getdate(), null
> from AttachedFiles
> where AttachmentID = @.int_AttachmentID
> end
> SET NOCOUNT OFF
> END|||CREATE TABLE AttachmentTypes
(
AttachmentTypeID int not null,
TypeName varchar(20) not null,
TypeDesc text null
Primary Key (AttachmentTypeID)
)
GO
insert into AttachmentTypes select 3,'UploadedFile_DB','File uploaded into
the AttachedFiles table.'
go
CREATE TABLE AttachmentDetail
(
AttachmentID int not null,
AttachmentTypeID int not null references AttachmentTypes(AttachmentTypeID),
LinkPath text not null,
LinkDate datetime not null,
LinkTitle varchar(255) null,
UserID varchar(100) not null,
FileName varchar(100) null
Primary Key (AttachmentID)
)
GO
CREATE TABLE AttachedFiles
(
AttachmentID int not null references AttachmentDetail(AttachmentID),
FileContent image not null,
FileSize float not null,
MimeType varchar(50) not null
Primary Key (AttachmentID)
)
GO
CREATE TABLE AttachmentDetail_H
(
AttachmentID int not null,
AttachmentTypeID int not null,
LinkPath text not null,
LinkDate datetime not null,
LinkTitle varchar(255) null,
UserID varchar(100) not null,
FileName varchar(100) null,
HistoryUserID varchar(100) not null,
HistoryAction varchar(20) not null,
HistoryDateTime datetime not null,
HistoryComments text null
)
GO
CREATE clustered index AttachmentDetail_H_idc on
AttachmentDetail_H(AttachmentID, HistoryDateTime)
GO
CREATE TABLE AttachedFiles_H
(
AttachmentID int not null,
FileContent image not null,
FileSize float not null,
MimeType varchar(50) not null,
HistoryUserID varchar(100) not null,
HistoryAction varchar(20) not null,
HistoryDateTime datetime not null,
HistoryComments text null
)
GO
CREATE clustered index AttachedFiles_H_idc on AttachedFiles_H(AttachmentID,
HistoryDateTime)
GO
declare @.P1 int
set @.P1=NULL
exec oContent_ins_Attachment @.int_AttachmentTypeID = 3, @.str_LinkPath ='Production Chang', @.str_UserID = 'smay', @.byt_FileContent =0x50726F64756374696F6E206368616E6765733A0D0A0D0A2D2D205345435552495459202D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D0D0A64726F702070726F636564757265206F53656375726974795F73656C5F417574686F72697A6555736572436F6D706F6E656E740D0A64726F702070726F636564757265206F53656375726974795F73656C5F417574686F72697A65557365724D6F64756C650D0A636F6D70696C6520616C6C205350730D0A696E6372656173652055736572494420746F2076617263686172283130302920696E3A0D0A0955736572732C2055736572735465726D696E617465642C2055736572496E666F2C205573657250617373776F7264732C20436F6D70616E69657355736572732C204D6F64756C6573436F6D706F6E656E747355736572732C2047726F75707355736572730D0A0D0A0D0A2D2D20454D455247454E4359204D534753202D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D0D0A44524F502050726F636564757265206F456D657267656E63794D7367735F73656C5F4D6573736167654974656D730D0A636F6D70696C65206F456D657267656E63794D7367735F73656C5F4D73674974656D730D0A0D0A0D0A2D2D20474C4F42414C202D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D0D0A44524F502050726F636564757265206F476C6F62616C5F73656C5F4170706C69636174696F6E5661726961626C65730D0A636F6D70696C65206F476C6F62616C5F73656C5F4170705661726961626C65730D0A0D0A0D0A2D2D204D454E55202D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D0D0A696E6372656173652055736572494420746F2076617263686172283130302920696E204D656E75557365720D0A636F6D70696C65206F4D656E755F73656C5F5363726F6C6C526F77436F756E740D0A0D0A0D0A2D2D2053544154495354494353202D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D2D0D0A696E6372656173652055736572494420746F2076617263686172283130302920696E2050616765537461746973746963730D0A636F6D70696C65206F537461746973746963735F696E735F506167655374617469737469630D0A0D0A0D0A,
@.int_FileLength = 1165, @.str_FileType = 'text/plain', @.str_HistoryUserID ='smay', @.int_AttachmentID = @.P1 output
select @.P1
"Aaron [SQL Server MVP]" wrote:
> If you give a full repro (e.g. table structure, sample data, an example call
> to the proc, etc.) we can try to reproduce the AV on newer builds, so you
> can know if it is already fixed on a post-SP3 build, or if you've found a
> new bug.
> See http://www.aspfaq.com/5006 for details.
> (Also, update your laptop... you are practically begging to get infected by
> Slammer.)
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "smay" <smay@.discussions.microsoft.com> wrote in message
> news:4C55F48E-741E-41BF-8C13-37AE9EBBE5E2@.microsoft.com...
> > The following fairly basic stored proc runs perfectly on my laptop (SQL
> > Server 2000 - 8.00.194 Personal Edition), but on my dev server (SQL
> Server
> > 2000 - 8.00.760 Enterprise Edition, SP3) I get: SqlDumpExceptionHandler:
> > Process xx generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
> SQL
> > Server is terminating this process..
> >
> > Any help is most appreciated.
> >
> > CREATE Procedure oContent_ins_Attachment
> > @.int_AttachmentTypeID int,
> > @.str_LinkPath text,
> > @.str_UserID varchar(100),
> > @.str_HistoryUserID varchar(100),
> > @.byt_FileContent image = null,
> > @.int_FileLength int = 0,
> > @.str_FileType varchar(50) = null,
> > @.str_LinkTitle varchar(255) = null,
> > @.str_FileName varchar(100) = null,
> > @.str_FileExt varchar(10) = null,
> > @.int_AttachmentID int output
> > AS
> > BEGIN
> > SET NOCOUNT ON
> >
> > declare @.str_HyperlinkPath varchar(1000)
> >
> > begin tran
> > select @.int_AttachmentID = isnull(max(AttachmentID),0)+1 from
> > AttachmentDetail
> >
> > if @.@.error != 0
> > begin
> > if @.@.trancount > 0
> > begin
> > rollback tran
> > select @.int_AttachmentID = 0
> > return -1
> > end
> > else
> > return -1
> > end
> >
> > if @.str_FileExt is null
> > insert into AttachmentDetail (AttachmentID, AttachmentTypeID, LinkPath,
> > LinkDate, LinkTitle, UserID, FileName)
> > values (@.int_AttachmentID, @.int_AttachmentTypeID, @.str_LinkPath,
> > getdate(), @.str_LinkTitle, @.str_UserID, @.str_FileName)
> > else
> > insert into AttachmentDetail (AttachmentID, AttachmentTypeID, LinkPath,
> > LinkDate, LinkTitle, UserID, FileName)
> > values (@.int_AttachmentID,
> > @.int_AttachmentTypeID,
> > convert(varchar(1000),@.str_LinkPath) +
> > convert(varchar(1000),@.int_AttachmentID) + @.str_FileExt,
> > getdate(),
> > @.str_LinkTitle,
> > @.str_UserID,
> > @.str_FileName)
> >
> > if @.@.error != 0
> > begin
> > if @.@.trancount > 0
> > begin
> > rollback tran
> > select @.int_AttachmentID = 0
> > return -2
> > end
> > else
> > return -2
> > end
> > else
> > if @.@.trancount > 0
> > commit tran
> >
> > insert into AttachmentDetail_H
> > select *, @.str_HistoryUserID, 'Inserted', getdate(), null
> > from AttachmentDetail
> > where AttachmentID = @.int_AttachmentID
> >
> > if @.byt_FileContent is not null
> > begin
> > insert into AttachedFiles (AttachmentID, FileContent, FileSize, MimeType)
> > values (@.int_AttachmentID, @.byt_FileContent, @.int_FileLength,
> > @.str_FileType)
> >
> > insert into AttachedFiles_H
> > select *, @.str_HistoryUserID, 'Inserted', getdate(), null
> > from AttachedFiles
> > where AttachmentID = @.int_AttachmentID
> > end
> >
> > SET NOCOUNT OFF
> > END
>
>|||You didn't provide enough for a repro. The AV is likely coming from the
code in the stored procedure, oContent_ins_Attachment, which you didn't
include. Please make sure to include it and all dependent objects, and test
your repro on a blank database to make sure you've included all the
structure and data necessary for someone else to reproduce the problem.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||I did include it in my original post. Here it is again.
CREATE Procedure oContent_ins_Attachment
@.int_AttachmentTypeID int,
@.str_LinkPath text,
@.str_UserID varchar(100),
@.str_HistoryUserID varchar(100),
@.byt_FileContent image = null,
@.int_FileLength int = 0,
@.str_FileType varchar(50) = null,
@.str_LinkTitle varchar(255) = null,
@.str_FileName varchar(100) = null,
@.str_FileExt varchar(10) = null,
@.int_AttachmentID int output
AS
BEGIN
SET NOCOUNT ON
declare @.str_HyperlinkPath varchar(1000)
begin tran
select @.int_AttachmentID = isnull(max(AttachmentID),0)+1 from
AttachmentDetail
if @.@.error != 0
begin
if @.@.trancount > 0
begin
rollback tran
select @.int_AttachmentID = 0
return -1
end
else
return -1
end
if @.str_FileExt is null
insert into AttachmentDetail (AttachmentID, AttachmentTypeID, LinkPath,
LinkDate, LinkTitle, UserID, FileName)
values (@.int_AttachmentID, @.int_AttachmentTypeID, @.str_LinkPath,
getdate(), @.str_LinkTitle, @.str_UserID, @.str_FileName)
else
insert into AttachmentDetail (AttachmentID, AttachmentTypeID, LinkPath,
LinkDate, LinkTitle, UserID, FileName)
values (@.int_AttachmentID,
@.int_AttachmentTypeID,
convert(varchar(1000),@.str_LinkPath) +
convert(varchar(1000),@.int_AttachmentID) + @.str_FileExt,
getdate(),
@.str_LinkTitle,
@.str_UserID,
@.str_FileName)
if @.@.error != 0
begin
if @.@.trancount > 0
begin
rollback tran
select @.int_AttachmentID = 0
return -2
end
else
return -2
end
else
if @.@.trancount > 0
commit tran
insert into AttachmentDetail_H
select *, @.str_HistoryUserID, 'Inserted', getdate(), null
from AttachmentDetail
where AttachmentID = @.int_AttachmentID
if @.byt_FileContent is not null
begin
insert into AttachedFiles (AttachmentID, FileContent, FileSize, MimeType)
values (@.int_AttachmentID, @.byt_FileContent, @.int_FileLength,
@.str_FileType)
insert into AttachedFiles_H
select *, @.str_HistoryUserID, 'Inserted', getdate(), null
from AttachedFiles
where AttachmentID = @.int_AttachmentID
end
SET NOCOUNT OFF
END
"Aaron [SQL Server MVP]" wrote:
> You didn't provide enough for a repro. The AV is likely coming from the
> code in the stored procedure, oContent_ins_Attachment, which you didn't
> include. Please make sure to include it and all dependent objects, and test
> your repro on a blank database to make sure you've included all the
> structure and data necessary for someone else to reproduce the problem.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>|||I don't get an access violation, but I'm using a build far later than 760
(and don't have a 760 handy to confirm your AV). You could try a newer
hotfix in the dev environment; the most recent I know of that is publicly
available is 878 (http://support.microsoft.com/?kbid=838166).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"smay" <smay@.discussions.microsoft.com> wrote in message
news:D75DE4EE-F63E-4FA7-B3CF-98CEE15C115E@.microsoft.com...
> I did include it in my original post. Here it is again.
> CREATE Procedure oContent_ins_Attachment
> @.int_AttachmentTypeID int,
> @.str_LinkPath text,
> @.str_UserID varchar(100),
> @.str_HistoryUserID varchar(100),
> @.byt_FileContent image = null,
> @.int_FileLength int = 0,
> @.str_FileType varchar(50) = null,
> @.str_LinkTitle varchar(255) = null,
> @.str_FileName varchar(100) = null,
> @.str_FileExt varchar(10) = null,
> @.int_AttachmentID int output
> AS
> BEGIN
> SET NOCOUNT ON
> declare @.str_HyperlinkPath varchar(1000)
> begin tran
> select @.int_AttachmentID = isnull(max(AttachmentID),0)+1 from
> AttachmentDetail
> if @.@.error != 0
> begin
> if @.@.trancount > 0
> begin
> rollback tran
> select @.int_AttachmentID = 0
> return -1
> end
> else
> return -1
> end
> if @.str_FileExt is null
> insert into AttachmentDetail (AttachmentID, AttachmentTypeID, LinkPath,
> LinkDate, LinkTitle, UserID, FileName)
> values (@.int_AttachmentID, @.int_AttachmentTypeID, @.str_LinkPath,
> getdate(), @.str_LinkTitle, @.str_UserID, @.str_FileName)
> else
> insert into AttachmentDetail (AttachmentID, AttachmentTypeID, LinkPath,
> LinkDate, LinkTitle, UserID, FileName)
> values (@.int_AttachmentID,
> @.int_AttachmentTypeID,
> convert(varchar(1000),@.str_LinkPath) +
> convert(varchar(1000),@.int_AttachmentID) + @.str_FileExt,
> getdate(),
> @.str_LinkTitle,
> @.str_UserID,
> @.str_FileName)
> if @.@.error != 0
> begin
> if @.@.trancount > 0
> begin
> rollback tran
> select @.int_AttachmentID = 0
> return -2
> end
> else
> return -2
> end
> else
> if @.@.trancount > 0
> commit tran
> insert into AttachmentDetail_H
> select *, @.str_HistoryUserID, 'Inserted', getdate(), null
> from AttachmentDetail
> where AttachmentID = @.int_AttachmentID
> if @.byt_FileContent is not null
> begin
> insert into AttachedFiles (AttachmentID, FileContent, FileSize, MimeType)
> values (@.int_AttachmentID, @.byt_FileContent, @.int_FileLength,
> @.str_FileType)
> insert into AttachedFiles_H
> select *, @.str_HistoryUserID, 'Inserted', getdate(), null
> from AttachedFiles
> where AttachmentID = @.int_AttachmentID
> end
> SET NOCOUNT OFF
> END
> "Aaron [SQL Server MVP]" wrote:
> > You didn't provide enough for a repro. The AV is likely coming from the
> > code in the stored procedure, oContent_ins_Attachment, which you didn't
> > include. Please make sure to include it and all dependent objects, and
test
> > your repro on a blank database to make sure you've included all the
> > structure and data necessary for someone else to reproduce the problem.
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >