| esgraham 2004-09-29, 8:03 pm |
| 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.
|