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.)
> >
> >
> >
Showing posts with label runs. Show all posts
Showing posts with label runs. Show all posts
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
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
Access violation with DBCC UNPINTABLE
Customer's server, so I can't explain or change the jobs...
At 3:00 am every day, a job runs that calls DBCC PINTABLE on three tables
At 5:00 PM, another job runs that calls DBCC UNPINTABLE on those same three
tables
Both report to complete successfully, but the UNPINTABLE job causes an
access violation and creates a mini-dump file.
SQL Server 2000, sp4 plus the AWE hotfix (.2040), Windows 2K3, sp1,
single-instance 2 node cluster
Nothing on MS Site about UNPINTABLE causing AVs...
All input appreciated
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
Contact Product Support...
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:e7tDJ6iEHHA.4404@.TK2MSFTNGP06.phx.gbl...
> Customer's server, so I can't explain or change the jobs...
> At 3:00 am every day, a job runs that calls DBCC PINTABLE on three tables
> At 5:00 PM, another job runs that calls DBCC UNPINTABLE on those same
> three tables
> Both report to complete successfully, but the UNPINTABLE job causes an
> access violation and creates a mini-dump file.
> SQL Server 2000, sp4 plus the AWE hotfix (.2040), Windows 2K3, sp1,
> single-instance 2 node cluster
> Nothing on MS Site about UNPINTABLE causing AVs...
> All input appreciated
>
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
>
|||Hi Paul,
I have asked my customer if he wants to pay for that call. Unless you can
hit //bugcheck for me and see if this a known but "not going to fix"
bug...
I've also asked him why on earth he is still using PINTABLE and UNPINTABLE.
I thought that went out of style with 6.5
Thanks for the response!
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OKsXrnnEHHA.3836@.TK2MSFTNGP02.phx.gbl...
> Contact Product Support...
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:e7tDJ6iEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>
At 3:00 am every day, a job runs that calls DBCC PINTABLE on three tables
At 5:00 PM, another job runs that calls DBCC UNPINTABLE on those same three
tables
Both report to complete successfully, but the UNPINTABLE job causes an
access violation and creates a mini-dump file.
SQL Server 2000, sp4 plus the AWE hotfix (.2040), Windows 2K3, sp1,
single-instance 2 node cluster
Nothing on MS Site about UNPINTABLE causing AVs...
All input appreciated
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
Contact Product Support...
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:e7tDJ6iEHHA.4404@.TK2MSFTNGP06.phx.gbl...
> Customer's server, so I can't explain or change the jobs...
> At 3:00 am every day, a job runs that calls DBCC PINTABLE on three tables
> At 5:00 PM, another job runs that calls DBCC UNPINTABLE on those same
> three tables
> Both report to complete successfully, but the UNPINTABLE job causes an
> access violation and creates a mini-dump file.
> SQL Server 2000, sp4 plus the AWE hotfix (.2040), Windows 2K3, sp1,
> single-instance 2 node cluster
> Nothing on MS Site about UNPINTABLE causing AVs...
> All input appreciated
>
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
>
|||Hi Paul,
I have asked my customer if he wants to pay for that call. Unless you can
hit //bugcheck for me and see if this a known but "not going to fix"
bug...
I've also asked him why on earth he is still using PINTABLE and UNPINTABLE.
I thought that went out of style with 6.5
Thanks for the response!
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OKsXrnnEHHA.3836@.TK2MSFTNGP02.phx.gbl...
> Contact Product Support...
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:e7tDJ6iEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>
Access violation with DBCC UNPINTABLE
Customer's server, so I can't explain or change the jobs...
At 3:00 am every day, a job runs that calls DBCC PINTABLE on three tables
At 5:00 PM, another job runs that calls DBCC UNPINTABLE on those same three
tables
Both report to complete successfully, but the UNPINTABLE job causes an
access violation and creates a mini-dump file.
SQL Server 2000, sp4 plus the AWE hotfix (.2040), Windows 2K3, sp1,
single-instance 2 node cluster
Nothing on MS Site about UNPINTABLE causing AVs...
All input appreciated
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.comContact Product Support...
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:e7tDJ6iEHHA.4404@.TK2MSFTNGP06.phx.gbl...
> Customer's server, so I can't explain or change the jobs...
> At 3:00 am every day, a job runs that calls DBCC PINTABLE on three tables
> At 5:00 PM, another job runs that calls DBCC UNPINTABLE on those same
> three tables
> Both report to complete successfully, but the UNPINTABLE job causes an
> access violation and creates a mini-dump file.
> SQL Server 2000, sp4 plus the AWE hotfix (.2040), Windows 2K3, sp1,
> single-instance 2 node cluster
> Nothing on MS Site about UNPINTABLE causing AVs...
> All input appreciated
>
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
>|||Hi Paul,
I have asked my customer if he wants to pay for that call. Unless you can
hit //bugcheck for me and see if this a known but "not going to fix"
bug...
I've also asked him why on earth he is still using PINTABLE and UNPINTABLE.
I thought that went out of style with 6.5
Thanks for the response!
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OKsXrnnEHHA.3836@.TK2MSFTNGP02.phx.gbl...
> Contact Product Support...
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:e7tDJ6iEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>
At 3:00 am every day, a job runs that calls DBCC PINTABLE on three tables
At 5:00 PM, another job runs that calls DBCC UNPINTABLE on those same three
tables
Both report to complete successfully, but the UNPINTABLE job causes an
access violation and creates a mini-dump file.
SQL Server 2000, sp4 plus the AWE hotfix (.2040), Windows 2K3, sp1,
single-instance 2 node cluster
Nothing on MS Site about UNPINTABLE causing AVs...
All input appreciated
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.comContact Product Support...
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:e7tDJ6iEHHA.4404@.TK2MSFTNGP06.phx.gbl...
> Customer's server, so I can't explain or change the jobs...
> At 3:00 am every day, a job runs that calls DBCC PINTABLE on three tables
> At 5:00 PM, another job runs that calls DBCC UNPINTABLE on those same
> three tables
> Both report to complete successfully, but the UNPINTABLE job causes an
> access violation and creates a mini-dump file.
> SQL Server 2000, sp4 plus the AWE hotfix (.2040), Windows 2K3, sp1,
> single-instance 2 node cluster
> Nothing on MS Site about UNPINTABLE causing AVs...
> All input appreciated
>
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
>|||Hi Paul,
I have asked my customer if he wants to pay for that call. Unless you can
hit //bugcheck for me and see if this a known but "not going to fix"
bug...
I've also asked him why on earth he is still using PINTABLE and UNPINTABLE.
I thought that went out of style with 6.5
Thanks for the response!
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OKsXrnnEHHA.3836@.TK2MSFTNGP02.phx.gbl...
> Contact Product Support...
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:e7tDJ6iEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>
Access violation with DBCC UNPINTABLE
Customer's server, so I can't explain or change the jobs...
At 3:00 am every day, a job runs that calls DBCC PINTABLE on three tables
At 5:00 PM, another job runs that calls DBCC UNPINTABLE on those same three
tables
Both report to complete successfully, but the UNPINTABLE job causes an
access violation and creates a mini-dump file.
SQL Server 2000, sp4 plus the AWE hotfix (.2040), Windows 2K3, sp1,
single-instance 2 node cluster
Nothing on MS Site about UNPINTABLE causing AVs...
All input appreciated :)
--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.comContact Product Support...
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:e7tDJ6iEHHA.4404@.TK2MSFTNGP06.phx.gbl...
> Customer's server, so I can't explain or change the jobs...
> At 3:00 am every day, a job runs that calls DBCC PINTABLE on three tables
> At 5:00 PM, another job runs that calls DBCC UNPINTABLE on those same
> three tables
> Both report to complete successfully, but the UNPINTABLE job causes an
> access violation and creates a mini-dump file.
> SQL Server 2000, sp4 plus the AWE hotfix (.2040), Windows 2K3, sp1,
> single-instance 2 node cluster
> Nothing on MS Site about UNPINTABLE causing AVs...
> All input appreciated :)
>
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
>|||Hi Paul,
I have asked my customer if he wants to pay for that call. Unless you can
hit //bugcheck for me and see if this a known but "not going to fix"
bug...:)
I've also asked him why on earth he is still using PINTABLE and UNPINTABLE.
I thought that went out of style with 6.5
Thanks for the response!
--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OKsXrnnEHHA.3836@.TK2MSFTNGP02.phx.gbl...
> Contact Product Support...
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:e7tDJ6iEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>> Customer's server, so I can't explain or change the jobs...
>> At 3:00 am every day, a job runs that calls DBCC PINTABLE on three tables
>> At 5:00 PM, another job runs that calls DBCC UNPINTABLE on those same
>> three tables
>> Both report to complete successfully, but the UNPINTABLE job causes an
>> access violation and creates a mini-dump file.
>> SQL Server 2000, sp4 plus the AWE hotfix (.2040), Windows 2K3, sp1,
>> single-instance 2 node cluster
>> Nothing on MS Site about UNPINTABLE causing AVs...
>> All input appreciated :)
>>
>> --
>> Kevin Hill
>> 3NF Consulting
>> http://www.3nf-inc.com/NewsGroups.htm
>> Real-world stuff I run across with SQL Server:
>> http://kevin3nf.blogspot.com
>>
>
At 3:00 am every day, a job runs that calls DBCC PINTABLE on three tables
At 5:00 PM, another job runs that calls DBCC UNPINTABLE on those same three
tables
Both report to complete successfully, but the UNPINTABLE job causes an
access violation and creates a mini-dump file.
SQL Server 2000, sp4 plus the AWE hotfix (.2040), Windows 2K3, sp1,
single-instance 2 node cluster
Nothing on MS Site about UNPINTABLE causing AVs...
All input appreciated :)
--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.comContact Product Support...
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:e7tDJ6iEHHA.4404@.TK2MSFTNGP06.phx.gbl...
> Customer's server, so I can't explain or change the jobs...
> At 3:00 am every day, a job runs that calls DBCC PINTABLE on three tables
> At 5:00 PM, another job runs that calls DBCC UNPINTABLE on those same
> three tables
> Both report to complete successfully, but the UNPINTABLE job causes an
> access violation and creates a mini-dump file.
> SQL Server 2000, sp4 plus the AWE hotfix (.2040), Windows 2K3, sp1,
> single-instance 2 node cluster
> Nothing on MS Site about UNPINTABLE causing AVs...
> All input appreciated :)
>
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
>|||Hi Paul,
I have asked my customer if he wants to pay for that call. Unless you can
hit //bugcheck for me and see if this a known but "not going to fix"
bug...:)
I've also asked him why on earth he is still using PINTABLE and UNPINTABLE.
I thought that went out of style with 6.5
Thanks for the response!
--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OKsXrnnEHHA.3836@.TK2MSFTNGP02.phx.gbl...
> Contact Product Support...
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:e7tDJ6iEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>> Customer's server, so I can't explain or change the jobs...
>> At 3:00 am every day, a job runs that calls DBCC PINTABLE on three tables
>> At 5:00 PM, another job runs that calls DBCC UNPINTABLE on those same
>> three tables
>> Both report to complete successfully, but the UNPINTABLE job causes an
>> access violation and creates a mini-dump file.
>> SQL Server 2000, sp4 plus the AWE hotfix (.2040), Windows 2K3, sp1,
>> single-instance 2 node cluster
>> Nothing on MS Site about UNPINTABLE causing AVs...
>> All input appreciated :)
>>
>> --
>> Kevin Hill
>> 3NF Consulting
>> http://www.3nf-inc.com/NewsGroups.htm
>> Real-world stuff I run across with SQL Server:
>> http://kevin3nf.blogspot.com
>>
>
Thursday, March 8, 2012
Access to SQL Server 7 syntax help
This runs in Access, but SQL Server 7 complains that BETWEEN is unrecognized. Can anyone help me? thanks
SELECT yearId, IIf(Date() BETWEEN [qrtOneStart] AND [qrtOneEnd],1, IIf(Date() BETWEEN [qrtTwoStart] AND [qrtTwoEnd], 2, IIf(Date() BETWEEN [qrtThreeStart] AND [qrtThreeEnd], 3, 4))) AS CurrentQrt, yearName
FROM tblYearSELECT
yearId,
CASE
WHEN GETDATE() BETWEEN qrtOneStart AND qrtOneEnd THEN 1
WHEN GETDATE() BETWEEN qrtTwoStart AND qrtTwoEnd THEN 2
ELSE 0
END AS Quarter
FROM
tblYear
--SQL Server does not support IIF. That's an Access-only command.|||I'd like to address something since someone else brought it up.
I do BETWEEN @.date(s) all the time. However, if it is a datetime field, you have to do it like this:
BETWEEN '05/07/2004 0:00' AND '05/07/2004 23:59'
Right? You have to specify the times if you are doing between on a datetime field. As far as I know, there isn't just a Date datatype, correct? Unless you stored the value as varchar(10) i.e. 05/07/2004.
So, what I've resolved to doing is CONVERT'ng the datetime to just the date, and excluding the time. Does this sound proper? Just Curious...|||Using Convert() to strip the time from a DATETIME is easy, but it causes awful performance problems. Using BETWEEN is usually much better if an index exists, and slightly better even if no index exists.
The reason for this is that BETWEEN can "ride" an index, zeroing in quickly on the rows of interest. Using Convert() forces SQL Server to examine every row in the table (which it would if there were no index), and also to pass every date through the Convert() function (which is pure overhead).
-PatP|||I understand what you are saying for sure, but let's say I've got a dateteime field, and I am running a DTS package daily to get...say daily sales. I use GETDATE to figure out what the date is. So I HAVE to use CONVERT() to strip out the time because I'm not using a BETWEEN statement if I'm using CONVERT(). Right?
UNLESS! (and I'm sure you, pat, will know this one) I can concatenate(sp?) the 0:00 and 23:59 to the GETDATE() function. LIke:
TodaysDate = GETDATE()& 0:00 ??|||Something like:DECLARE @.dBegin DATETIME
, @.dEnd DATETIME
, @.dNow DATETIME
SELECT @.dNow = GetDate()
SELECT @.dBegin = Convert(CHAR(10), @.dNow, 121)
, @.dEnd = Convert(CHAR(10), @.dNow, 121) + ' 23:59:59.997'
SELECT foo, bar, dit, dash
FROM dbo.morse
WHERE date_processed BETWEEN @.dBegin AND @.eEnd...should probably do what you want.
-PatP|||but...why do you use SELECT @.Foo = Bar? I'm in the habit of using SET @.Foo = Bar. What gives?|||I'm a crusty old phart. The ability to use SET is new, and I'm not always good about using it, especially because SELECT is really more convenient in many ways (and old habits die hard).
I used a list of arbitrary column names to keep the SELECT * "police" at bay. While I have no problem with using SELECT * in an example, there are folks here on the forum that get really hyper about it. ;)
Just pretend that I used SELECT * to reduce the confusion, and you'll be just fine! I agree that you shouldn't use SELECT * in production, but I also think that it helps keep things clearer in a code sample like this.
-PatP
SELECT yearId, IIf(Date() BETWEEN [qrtOneStart] AND [qrtOneEnd],1, IIf(Date() BETWEEN [qrtTwoStart] AND [qrtTwoEnd], 2, IIf(Date() BETWEEN [qrtThreeStart] AND [qrtThreeEnd], 3, 4))) AS CurrentQrt, yearName
FROM tblYearSELECT
yearId,
CASE
WHEN GETDATE() BETWEEN qrtOneStart AND qrtOneEnd THEN 1
WHEN GETDATE() BETWEEN qrtTwoStart AND qrtTwoEnd THEN 2
ELSE 0
END AS Quarter
FROM
tblYear
--SQL Server does not support IIF. That's an Access-only command.|||I'd like to address something since someone else brought it up.
I do BETWEEN @.date(s) all the time. However, if it is a datetime field, you have to do it like this:
BETWEEN '05/07/2004 0:00' AND '05/07/2004 23:59'
Right? You have to specify the times if you are doing between on a datetime field. As far as I know, there isn't just a Date datatype, correct? Unless you stored the value as varchar(10) i.e. 05/07/2004.
So, what I've resolved to doing is CONVERT'ng the datetime to just the date, and excluding the time. Does this sound proper? Just Curious...|||Using Convert() to strip the time from a DATETIME is easy, but it causes awful performance problems. Using BETWEEN is usually much better if an index exists, and slightly better even if no index exists.
The reason for this is that BETWEEN can "ride" an index, zeroing in quickly on the rows of interest. Using Convert() forces SQL Server to examine every row in the table (which it would if there were no index), and also to pass every date through the Convert() function (which is pure overhead).
-PatP|||I understand what you are saying for sure, but let's say I've got a dateteime field, and I am running a DTS package daily to get...say daily sales. I use GETDATE to figure out what the date is. So I HAVE to use CONVERT() to strip out the time because I'm not using a BETWEEN statement if I'm using CONVERT(). Right?
UNLESS! (and I'm sure you, pat, will know this one) I can concatenate(sp?) the 0:00 and 23:59 to the GETDATE() function. LIke:
TodaysDate = GETDATE()& 0:00 ??|||Something like:DECLARE @.dBegin DATETIME
, @.dEnd DATETIME
, @.dNow DATETIME
SELECT @.dNow = GetDate()
SELECT @.dBegin = Convert(CHAR(10), @.dNow, 121)
, @.dEnd = Convert(CHAR(10), @.dNow, 121) + ' 23:59:59.997'
SELECT foo, bar, dit, dash
FROM dbo.morse
WHERE date_processed BETWEEN @.dBegin AND @.eEnd...should probably do what you want.
-PatP|||but...why do you use SELECT @.Foo = Bar? I'm in the habit of using SET @.Foo = Bar. What gives?|||I'm a crusty old phart. The ability to use SET is new, and I'm not always good about using it, especially because SELECT is really more convenient in many ways (and old habits die hard).
I used a list of arbitrary column names to keep the SELECT * "police" at bay. While I have no problem with using SELECT * in an example, there are folks here on the forum that get really hyper about it. ;)
Just pretend that I used SELECT * to reduce the confusion, and you'll be just fine! I agree that you shouldn't use SELECT * in production, but I also think that it helps keep things clearer in a code sample like this.
-PatP
Thursday, February 16, 2012
Access Issues
Hi All-
We have a third party app that my company recently bought. It runs on SQL Server 2000. But the way the app is set up is that "Public" need write permissions. If I change this, the app will break. In this situation, can I create a read-only user on this database?
I don't think it is possible. Just want to make sure ...
- CByou can create a user and add this user to the database role called "db_denydatawriter". This deny right is applied over the "public" write right.
We have a third party app that my company recently bought. It runs on SQL Server 2000. But the way the app is set up is that "Public" need write permissions. If I change this, the app will break. In this situation, can I create a read-only user on this database?
I don't think it is possible. Just want to make sure ...
- CByou can create a user and add this user to the database role called "db_denydatawriter". This deny right is applied over the "public" write right.
Subscribe to:
Posts (Atom)