mar 1 blog

Lookup values and messages in SCRIBE INTERNAL DATABASE

Quick script to lookup values and messages in the SCRIBEINTERNAL database.

You can easy extend it to support multiple fields and filter in the WHERE clause to lookup a specific message, with this you will be able to push the message directly back into the MSMQ queue for re-processing or testing a flow.


DECLARE @LookupValue1 as NVARCHAR(150)
DECLARE @LookupValue1START as INT
DECLARE @LookupValue1DATA_LENGHT as INT

DECLARE @LookupValue2 as NVARCHAR(150)
DECLARE @LookupValue2START as INT
DECLARE @LookupValue2DATA_LENGHT as INT

DECLARE @SCRIBE_IP as INT
DECLARE @SCRIBE_DATEFILTER as NVARCHAR(150)

SET @SCRIBE_IP = 2
SET @SCRIBE_DATEFILTER = '2012-02-18 16:00:50'

SET @LookupValue1 = 'ScribeObjectKey'
SET @LookupValue1START = LEN(@LookupValue1) + 3
SET @LookupValue1DATA_LENGHT = 36

SET @LookupValue2 = 'ScribeObjectTypeCode'
SET @LookupValue2START = LEN(@LookupValue2) + 2
SET @LookupValue2DATA_LENGHT = 1

SELECT TOP (100)

SCRIBEINTERNAL.SCRIBE.JOBSUMMARY.LISTENERID AS SCRIBE_IP,
SCRIBEINTERNAL.SCRIBE.EXECLOGDETAILERRORS.ERRORCODE,
CAST(SCRIBEINTERNAL.SCRIBE.EXECLOGDETAILERRORS.ERRORMESSAGE AS nvarchar(MAX)) AS ERRORMESSAGE,
SUBSTRING(CAST(SCRIBEINTERNAL.SCRIBE.JOBSUMMARY.MESSAGE AS nvarchar(MAX)),CHARINDEX (@LookupValue1,CAST(SCRIBEINTERNAL.SCRIBE.JOBSUMMARY .MESSAGE AS nvarchar(MAX)),1 )+@LookupValue1START,@LookupValue1DATA_LENGHT) AS LookupValue1,
SUBSTRING(CAST(SCRIBEINTERNAL.SCRIBE.JOBSUMMARY.MESSAGE AS nvarchar(MAX)),CHARINDEX (@LookupValue2,CAST(SCRIBEINTERNAL.SCRIBE.JOBSUMMARY .MESSAGE AS nvarchar(MAX)),1 )+@LookupValue2START,@LookupValue2DATA_LENGHT) AS LookupValue2,
CAST(SCRIBEINTERNAL.SCRIBE.JOBSUMMARY.MESSAGE AS nvarchar(MAX)) AS XML_MESSAGE
FROM
SCRIBEINTERNAL.SCRIBE.EXECLOGDETAILERRORS WITH (NOLOCK) LEFT OUTER JOIN
SCRIBEINTERNAL.SCRIBE.JOBSUMMARY WITH (NOLOCK) ON SCRIBEINTERNAL.SCRIBE.EXECLOGDETAILERRORS.EXECID = SCRIBEINTERNAL.SCRIBE.JOBSUMMARY.EXECID

WHERE

SCRIBEINTERNAL.SCRIBE.JOBSUMMARY.LISTENERID = @SCRIBE_IP --DEFINE YOUR SCRIBE CONSOLE IP NUMBER
AND SCRIBEINTERNAL.SCRIBE.EXECLOGDETAILERRORS.STARTTIME > @SCRIBE_DATEFILTER --DEFINE YOUR DATE FILTER

GROUP BY

SCRIBEINTERNAL.SCRIBE.JOBSUMMARY.LISTENERID,
CAST(SCRIBEINTERNAL.SCRIBE.JOBSUMMARY.MESSAGE AS nvarchar(MAX)),
SCRIBEINTERNAL.SCRIBE.EXECLOGDETAILERRORS.ERRORCODE,
SCRIBEINTERNAL.SCRIBE.EXECLOGDETAILERRORS.TARGETKEY,
CAST(SCRIBEINTERNAL.SCRIBE.EXECLOGDETAILERRORS.ERRORMESSAGE AS nvarchar(MAX))
Share on FacebookTweet about this on TwitterShare on LinkedInShare on Google+