I am using the ArchiveMessage object, and I cannot get
any items back in the enumerator of the object.  To keep
this short, I have drilled down into the object and
believe that the problem is occurring because of an error
in the SQL that the object is using.

In the stored procedure "MBOM_ReadArchivedMessages", the
following code exists; my comments are surrounded by []:
--------------------------------------------------------

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ActiveRefCountLog]') and OBJECTPROPERTY
(id, N'IsUserTable') = 1)

[This is true, value exists]


BEGIN
SELECT TOP 1 @tnActiveTable = CAST
(nvcTableQualifier as tinyint) FROM TrackingSpoolInfo
WITH (ROWLOCK REPEATABLEREAD) WHERE nActive = 1 OPTION
(KEEPFIXED PLAN)
END
else
BEGIN
set @tnActiveTable = 2 --This will make us go to
table 1 which is what I assume people will archive to
END

-----------------------------------------------------

@tnActiveTable returns a value of one, which is correct
because the tracking items are being stored in the table
Tracking_Spool1.

The next bit of code seems to be incorrect:
----------------------------------------------------

if (@tnActiveTable = 1)

[Should this be 2, since the select uses the
Tracking_Spool2 table?]


BEGIN
SELECT CAST(2 as int), uidMsgID, imgContext,
dtTimeStamp, CAST(UserName as nvarchar(128)), nNumParts,
uidBodyPartID
FROM Tracking_Spool2
WHERE dtTimeStamp >= @dtFrom AND
dtTimeStamp <= @dtUntil AND
( (@uidLastMessageID IS NULL) OR (uidMsgID
< @uidLastMessageID) )
ORDER BY dtTimeStamp, uidMsgID
END
else
BEGIN
SELECT CAST(1 as int), uidMsgID, imgContext,
dtTimeStamp, CAST(UserName as nvarchar(128)), nNumParts,
uidBodyPartID
FROM Tracking_Spool1
WHERE dtTimeStamp >= @dtFrom AND
dtTimeStamp <= @dtUntil AND
( (@uidLastMessageID IS NULL) OR (uidMsgID
< @uidLastMessageID) )
ORDER BY dtTimeStamp, uidMsgID
END

Can someone explain the tracking spool tables, and how
they are used?  Also, why isn't the btsv_Tracking_Spool
view being used, since it is a union of both
Tracking_Spool tables?

Thanks.

[ Post a follow-up to this message ]