Versions: DSP 6.x
Issue:
Receiving the following error in the Common ttDebugLog table:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ttTraceObjectHistory_ttTraceObject". The conflict occurred in database "DSPCommon", table "dbo.ttTraceObject"
Root cause:
This error occurs when a database's DSPCommonTrace_EventData has records that reference objects that are not in the dspCommon.dbo.ttTraceObject table.
Resolution:
Steps for missing ttTraceObject entries:
1. Run the following script which will create a query for each DataSource with Trace Activated to display any references to missing Objects in ttTraceObject (but where there are entries in DSPCommonTrace_EventData that reference those missing objects).
SELECT
ttDataSource.DataSourceID
, ttDataSourceRegistry.[Database]
,'SELECT * FROM [' + LTRIM(ttDataSourceRegistry.[Database]) + '].[dbo].[DSPCommonTrace_EventData] WHERE Objectname NOT IN (SELECT ObjectName FROM DSPCommon.dbo.ttTraceObject WHERE DataSourceID=''' + cast (ttDataSource.DataSourceID as nvarchar(50))+ '''' + ') AND ObjectName NOT IN ( SELECT ObjectName FROM [' + ltrim(ttDataSourceRegistry.[Database]) + '].[dbo].[DSPCommonTrace_EventData] WHERE EventType LIKE ''' + 'c%' + ''')' + ' ORDER BY ObjectName, EventType' as ExecSQL
FROM [dspCommon].[dbo].ttDataSource
INNER JOIN [dspCommon].[dbo].ttDataSourceRegistry
ON ttDataSource.DataSourceID = ttDataSourceRegistry.DataSourceID
WHERE ttDataSource.Active = 1
2. For any results from the individual queries from step 1, check in the Database to see if the object exists.
a) If the object doesn't exist: Delete those entries from the DSPCommonTrace_EventData table in the database.
b) If the object does exist: In dsp/Common/Analyze, click on the Trace Icon for this particular DataSource. Disable then enable Trace again. Enabling trace again should populate the ttTraceObject with anything that was missing originally. Note that enabling Trace is a different process than the SourceTrack – Pull Data Service Page. To ensure that the foreign key errors have been resolved, create a test view in the database in question before running the service page.
3. Rerun the SourceTrack – Pull Data service page.
NOTE: On the vertical of datasource in Common/Analyze, there is a Status field at the bottom of the page:
The view behind this page creates a count of all error records in the dspCommon.dbo.ttDebugLog table that originated from the SourceTrack – Data Pull service page. If there aren’t any error records for the SourceTrack – Data Pull service page, the Status will be a Green ball. If there are errors, the Status will be a red X (as shown above).
Remember, the dspCommon.dbo.ttDebugLog doesn’t automatically get records deleted out of it, so if you have had foreign key errors in the past, the status will display the Red X until the SourceTrack Error records have been deleted out of the dspCommon.dbo.ttDebugLog.