In .NET when I call a stored proc that returns two sets of data, each
set is put into a datatable within the dataset. I have an SRS report
that is calling a stored proc that does the same thing. However, SRS
doesn't show me that second data table so I can get access to it's
columns. Is there a way to see multiple data tables from within a
Dataset in SRS?On Jun 5, 1:49 pm, Doogie <dnlwh...@.dtgnet.com> wrote:
> In .NET when I call a stored proc that returns two sets of data, each
> set is put into a datatable within the dataset. I have an SRS report
> that is calling a stored proc that does the same thing. However, SRS
> doesn't show me that second data table so I can get access to it's
> columns. Is there a way to see multiple data tables from within a
> Dataset in SRS?
As far as I know, there is not; however, you can create multiple
datasets: one dataset per resultset. Sorry that I could not be of
greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant
Showing posts with label proc. Show all posts
Showing posts with label proc. Show all posts
Sunday, March 25, 2012
Tuesday, March 20, 2012
Accessing #temp table in a proc as a user with minimal rights.
I need to create a #temp table in a proc as a user with minimal rights and t
hen
insert into it and select from it.
However, I am getting an error telling me that either the table does not exi
st or I
do not have sufficient rights to access it.
How do I get around this rights problem?
Thank you.
MikeMike,
Can we see the code?
AMB
"Mike Malter" wrote:
> I need to create a #temp table in a proc as a user with minimal rights and
then
> insert into it and select from it.
> However, I am getting an error telling me that either the table does not e
xist or I
> do not have sufficient rights to access it.
> How do I get around this rights problem?
> Thank you.
> Mike
>
>|||YOu have to post soe DDL for us to see the error.
HTH (us), Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Mike Malter" <mikemalter@.newsgroup.nospam> schrieb im Newsbeitrag
news:%23yqIndRRFHA.3544@.TK2MSFTNGP12.phx.gbl...
>I need to create a #temp table in a proc as a user with minimal rights and
>then insert into it and select from it.
> However, I am getting an error telling me that either the table does not
> exist or I do not have sufficient rights to access it.
> How do I get around this rights problem?
> Thank you.
> Mike
>|||Anyone can create temp tables. Post use the code, or code to reproduce the p
roblem and we can have a
look at it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Malter" <mikemalter@.newsgroup.nospam> wrote in message
news:%23yqIndRRFHA.3544@.TK2MSFTNGP12.phx.gbl...
>I need to create a #temp table in a proc as a user with minimal rights and
then insert into it and
>select from it.
> However, I am getting an error telling me that either the table does not e
xist or I do not have
> sufficient rights to access it.
> How do I get around this rights problem?
> Thank you.
> Mike
>|||Guys,
This was my goof.
When I was in the process of reviewing the code I was going to put up here,
I
realized that instead of creating the table and then inserting from a select
. I was
creating the table and then selected into. Which you can't do if the table
already
exists.
Sorry for bothering you guys, and thank everyone for their willingness to ju
mp in and
help.
Best regards.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:274CA8A2-F99D-4506-B6B8-025E10504135@.microsoft.com...
> Mike,
> Can we see the code?
>
> AMB
> "Mike Malter" wrote:
>|||That is what SQL Community is for :-))
"Mike Malter" <mikemalter@.newsgroup.nospam> schrieb im Newsbeitrag
news:e8BTHwRRFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Guys,
> This was my goof.
> When I was in the process of reviewing the code I was going to put up
> here, I realized that instead of creating the table and then inserting
> from a select. I was creating the table and then selected into. Which
> you can't do if the table already exists.
> Sorry for bothering you guys, and thank everyone for their willingness to
> jump in and help.
> Best regards.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:274CA8A2-F99D-4506-B6B8-025E10504135@.microsoft.com...
>
hen
insert into it and select from it.
However, I am getting an error telling me that either the table does not exi
st or I
do not have sufficient rights to access it.
How do I get around this rights problem?
Thank you.
MikeMike,
Can we see the code?
AMB
"Mike Malter" wrote:
> I need to create a #temp table in a proc as a user with minimal rights and
then
> insert into it and select from it.
> However, I am getting an error telling me that either the table does not e
xist or I
> do not have sufficient rights to access it.
> How do I get around this rights problem?
> Thank you.
> Mike
>
>|||YOu have to post soe DDL for us to see the error.
HTH (us), Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Mike Malter" <mikemalter@.newsgroup.nospam> schrieb im Newsbeitrag
news:%23yqIndRRFHA.3544@.TK2MSFTNGP12.phx.gbl...
>I need to create a #temp table in a proc as a user with minimal rights and
>then insert into it and select from it.
> However, I am getting an error telling me that either the table does not
> exist or I do not have sufficient rights to access it.
> How do I get around this rights problem?
> Thank you.
> Mike
>|||Anyone can create temp tables. Post use the code, or code to reproduce the p
roblem and we can have a
look at it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Malter" <mikemalter@.newsgroup.nospam> wrote in message
news:%23yqIndRRFHA.3544@.TK2MSFTNGP12.phx.gbl...
>I need to create a #temp table in a proc as a user with minimal rights and
then insert into it and
>select from it.
> However, I am getting an error telling me that either the table does not e
xist or I do not have
> sufficient rights to access it.
> How do I get around this rights problem?
> Thank you.
> Mike
>|||Guys,
This was my goof.
When I was in the process of reviewing the code I was going to put up here,
I
realized that instead of creating the table and then inserting from a select
. I was
creating the table and then selected into. Which you can't do if the table
already
exists.
Sorry for bothering you guys, and thank everyone for their willingness to ju
mp in and
help.
Best regards.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:274CA8A2-F99D-4506-B6B8-025E10504135@.microsoft.com...
> Mike,
> Can we see the code?
>
> AMB
> "Mike Malter" wrote:
>|||That is what SQL Community is for :-))
"Mike Malter" <mikemalter@.newsgroup.nospam> schrieb im Newsbeitrag
news:e8BTHwRRFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Guys,
> This was my goof.
> When I was in the process of reviewing the code I was going to put up
> here, I realized that instead of creating the table and then inserting
> from a select. I was creating the table and then selected into. Which
> you can't do if the table already exists.
> Sorry for bothering you guys, and thank everyone for their willingness to
> jump in and help.
> Best regards.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:274CA8A2-F99D-4506-B6B8-025E10504135@.microsoft.com...
>
Monday, March 19, 2012
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.)
> >
> >
> >
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.)
> >
> >
> >
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
END
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
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
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
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. SQ
L
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
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. SQ
L
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
Sunday, March 11, 2012
Access to statistical functions in stored procs
Is there anyway I can use the Correlation function in a stored proc? The
correlation function is part of OLAP. I have an application which requires
the display of the correlation coefficients between a base data set and many
instances of another data set. If I can't use the Correlation function in
OLAP, does anyone have suggestions of what I might be able to use?
AlanS
If this is for T-SQL and not MDX queries with analysis
services, then you can write your own. You can find some
examples if you search the SQL Server newsgroups on google.
One such example can be found at:
http://tinyurl.com/3rlhw
Another option would be to use the correlation function in
Excel and call the Excel functions in SQL Server using the
OLE Automation stored procedures. You can find more
information on the OLE Automation stored procedures in SQL
Server books online.
-Sue
On Fri, 3 Sep 2004 08:29:09 -0700, "AlanS"
<AlanS@.discussions.microsoft.com> wrote:
>Is there anyway I can use the Correlation function in a stored proc? The
>correlation function is part of OLAP. I have an application which requires
>the display of the correlation coefficients between a base data set and many
>instances of another data set. If I can't use the Correlation function in
>OLAP, does anyone have suggestions of what I might be able to use?
>AlanS
correlation function is part of OLAP. I have an application which requires
the display of the correlation coefficients between a base data set and many
instances of another data set. If I can't use the Correlation function in
OLAP, does anyone have suggestions of what I might be able to use?
AlanS
If this is for T-SQL and not MDX queries with analysis
services, then you can write your own. You can find some
examples if you search the SQL Server newsgroups on google.
One such example can be found at:
http://tinyurl.com/3rlhw
Another option would be to use the correlation function in
Excel and call the Excel functions in SQL Server using the
OLE Automation stored procedures. You can find more
information on the OLE Automation stored procedures in SQL
Server books online.
-Sue
On Fri, 3 Sep 2004 08:29:09 -0700, "AlanS"
<AlanS@.discussions.microsoft.com> wrote:
>Is there anyway I can use the Correlation function in a stored proc? The
>correlation function is part of OLAP. I have an application which requires
>the display of the correlation coefficients between a base data set and many
>instances of another data set. If I can't use the Correlation function in
>OLAP, does anyone have suggestions of what I might be able to use?
>AlanS
Saturday, February 25, 2012
Access SMO objects in CLR proc
I would like to write some CLR procs that use SMO objects. In visual studio I am unable to add refrences to the SMO objects. How can I do this?
Thanks
Bert
Because CLR was designed to work within the SQL Server engine the references available for a CLR assembly are limited. The assemblies actually run within the SQL Server context, not as operating system processes.
What would you want to do using SMO that you can't do using Transact-SQL?
|||You are unable to add them because SMO is dependent on Batchparser90.dll which is half managed/half unmanaged code, so SQL server will not load it. They don't show up in visual studio for this reason.
If you want to use assemblies that don't show up, try creating just a normal class library and then use the CREATE ASSEMBLY T-SQL command. You will have to load all of your external assemblies as well, so I would see if they load before you start writing it, as some will and some wont.
Access SMO objects in CLR proc
I would like to write some CLR procs that use SMO objects. In visual studio I am unable to add refrences to the SMO objects. How can I do this?
Thanks
Bert
Because CLR was designed to work within the SQL Server engine the references available for a CLR assembly are limited. The assemblies actually run within the SQL Server context, not as operating system processes.
What would you want to do using SMO that you can't do using Transact-SQL?
|||You are unable to add them because SMO is dependent on Batchparser90.dll which is half managed/half unmanaged code, so SQL server will not load it. They don't show up in visual studio for this reason.
If you want to use assemblies that don't show up, try creating just a normal class library and then use the CREATE ASSEMBLY T-SQL command. You will have to load all of your external assemblies as well, so I would see if they load before you start writing it, as some will and some wont.
Access Rights
I created a new database role called OpsRepo in MS Sql Server 2000. I assigned a stored proc called get_null_vals to this role only.
I log into Sql Query Analyser using windows authentication (non-administrator). I am able to execute the stored proc even though I am NOT in the OpsRepo role. However, I am in the public role.
I was expecting a permission problem - this did not happen. Why?
I would appreciate your help
Thanks
YogeshWhat rights does public have?|||Originally posted by ykverma
I created a new database role called OpsRepo in MS Sql Server 2000. I assigned a stored proc called get_null_vals to this role only.
I log into Sql Query Analyser using windows authentication (non-administrator). I am able to execute the stored proc even though I am NOT in the OpsRepo role. However, I am in the public role.
I was expecting a permission problem - this did not happen. Why?
I would appreciate your help
Thanks
Yogesh
Check if you (or your windows group) belong to db_owner role or sysadmin role on server.|||Originally posted by Brett Kaiser
What rights does public have?
My user name is in the public role. The tables which are accessed by the stored proc are also in public but not the stored proc itself.
I've tried after bouncing the server - still it did not work.
Thanks in advance for your help
Yogesh|||Originally posted by snail
Check if you (or your windows group) belong to db_owner role or sysadmin role on server.
I am only in the public role; my id is not set up in any of the windows group
Thanks in advance
Yogesh
I log into Sql Query Analyser using windows authentication (non-administrator). I am able to execute the stored proc even though I am NOT in the OpsRepo role. However, I am in the public role.
I was expecting a permission problem - this did not happen. Why?
I would appreciate your help
Thanks
YogeshWhat rights does public have?|||Originally posted by ykverma
I created a new database role called OpsRepo in MS Sql Server 2000. I assigned a stored proc called get_null_vals to this role only.
I log into Sql Query Analyser using windows authentication (non-administrator). I am able to execute the stored proc even though I am NOT in the OpsRepo role. However, I am in the public role.
I was expecting a permission problem - this did not happen. Why?
I would appreciate your help
Thanks
Yogesh
Check if you (or your windows group) belong to db_owner role or sysadmin role on server.|||Originally posted by Brett Kaiser
What rights does public have?
My user name is in the public role. The tables which are accessed by the stored proc are also in public but not the stored proc itself.
I've tried after bouncing the server - still it did not work.
Thanks in advance for your help
Yogesh|||Originally posted by snail
Check if you (or your windows group) belong to db_owner role or sysadmin role on server.
I am only in the public role; my id is not set up in any of the windows group
Thanks in advance
Yogesh
Subscribe to:
Posts (Atom)