SQL Server Audit Trail

Anybody familiar with using SQL Server and ODBC for Audit Trails willing to take a look at some code and give any feedback as to my error?

I have some code that was not very robust but worked in the current application as long as you know how to manually add tags to SQL Server for the initial reference... This becomes problematic when new Tags are added to the PLC and they are not manually added after. I have revised the code so that it will check for the existence of the tag and if it does not exist it will INSERT but if it does exist it will UPDATE. The issue I am having is a parsing instruction that does not seem to want dynamically change the length of a string for the tag name.
See below for sample code and Actual Problem....( I will try to make this as readable as possible)


begin

IF(EXISTS(SELECT Messagetext
FROM Diagnostics
where (( Messagetext
LIKE '%CMS%' AND Messagetext LIKE '%WRITE%' )
AND Messagetext LIKE
'%.Cfg_InpRawMin%'
OR Messagetext LIKE
'%.Cfg_InpRawMax%'
OR Messagetext LIKE
'%.Cfg_PVEUMin%'
OR Messagetext LIKE
'%.Cfg_PVEUMax%'
OR Messagetext LIKE
'%.Cfg_FiltTC%'
OR Messagetext LIKE
'%.Cfg_HiHiDB%'
OR Messagetext LIKE
'%.Cfg_LoDB%'
OR Messagetext LIKE
'%.Cfg_HiDB%'
OR Messagetext LIKE
'%.Cfg_LoLoDB%'
OR Messagetext LIKE
'%.Cfg_FailHiLim%'
OR Messagetext LIKE
'%.Cfg_FailLoLim%'
OR Messagetext LIKE
'%.Cfg_FailDB%'
OR Messagetext LIKE
'%.Cfg_NoSubstPV%'
OR Messagetext LIKE
'%.Cfg_HasChanObj%'
OR Messagetext LIKE
'%.Cfg_HasHiHiAlm%'
OR Messagetext LIKE
'%.Cfg_HiHiResetReqd%'
OR Messagetext LIKE
'%.Cfg_HiHiAckReqd%'
OR Messagetext LIKE
'%.Cfg_HiHiSeverity%'
OR Messagetext LIKE
'%.Cfg_HiHiOnDly%'
OR Messagetext LIKE
'%.Cfg_HiHiOffDly%'
OR Messagetext LIKE
'%.Cfg_HiHiGateDly%'
OR Messagetext LIKE
'%.HiHiGate.Cfg_InpCond%'
OR Messagetext LIKE
'%.HiHiGate.Cfg_GateCond%'
OR Messagetext LIKE
'%.HiHi.Cfg_AllowShelve%'
OR Messagetext LIKE
'%.HiHi.Cfg_AllowDisable%'
OR Messagetext LIKE
'%.HiHi.Cfg_AlmMinOnT%'
OR Messagetext LIKE
'%.HiHi.Cfg_MaxShelfT%'
OR Messagetext LIKE
'%.HiHi.Cfg_Cond%'
OR Messagetext LIKE
'%.HiHi.Cfg_Tag%'
OR Messagetext LIKE
'%.Cfg_HasHiAlm%'
OR Messagetext LIKE
'%.Cfg_HiResetReqd%'
OR Messagetext LIKE
'%.Cfg_HiAckReqd%'
OR Messagetext LIKE
'%.Cfg_HiSeverity%'
OR Messagetext LIKE
'%.Cfg_HiOnDly%'
OR Messagetext LIKE
'%.Cfg_HiOffDly%'
OR Messagetext LIKE
'%.Cfg_HiGateDly%'
OR Messagetext LIKE
'%.HiGate.Cfg_InpCond%'
OR Messagetext LIKE
'%.HiGate.Cfg_GateCond%'
OR Messagetext LIKE
'%.Hi.Cfg_AllowShelve%'
OR Messagetext LIKE
'%.Hi.Cfg_AllowDisable%'
OR Messagetext LIKE
'%.Hi.Cfg_AlmMinOnT%'
OR Messagetext LIKE
'%.Hi.Cfg_MaxShelfT%'
OR Messagetext LIKE
'%.Hi.Cfg_Cond%'
OR Messagetext LIKE
'%.Hi.Cfg_Tag%'
OR Messagetext LIKE
'%.Cfg_HasLoAlm%'
OR Messagetext LIKE
'%.Cfg_LoResetReqd%'
OR Messagetext LIKE
'%.Cfg_LoAckReqd%'
OR Messagetext LIKE
'%.Cfg_LoSeverity%'
OR Messagetext LIKE
'%.Cfg_LoOnDly%'
OR Messagetext LIKE
'%.Cfg_LoOffDly%'
OR Messagetext LIKE
'%.Cfg_LoGateDly%'
OR Messagetext LIKE
'%.LoGate.Cfg_InpCond%'
OR Messagetext LIKE
'%.LoGate.Cfg_GateCond%'
OR Messagetext LIKE
'%.Lo.Cfg_AllowShelve%'
OR Messagetext LIKE
'%.Lo.Cfg_AllowDisable%'
OR Messagetext LIKE
'%.Lo.Cfg_AlmMinOnT%'
OR Messagetext LIKE
'%.Lo.Cfg_MaxShelfT%'
OR Messagetext LIKE
'%.Lo.Cfg_Cond%'
OR Messagetext LIKE
'%.Lo.Cfg_Tag%'
OR Messagetext LIKE
'%.Cfg_HasLoLoAlm%'
OR Messagetext LIKE
'%.Cfg_LoLoResetReqd%'
OR Messagetext LIKE
'%.Cfg_LoLoAckReqd%'
OR Messagetext LIKE
'%.Cfg_LoLoSeverity%'
OR Messagetext LIKE
'%.Cfg_LoLoOnDly%'
OR Messagetext LIKE
'%.Cfg_LoLoOffDly%'
OR Messagetext LIKE
'%.Cfg_LoLoGateDly%'
OR Messagetext LIKE
'%.LoLoGate.Cfg_InpCond%'
OR Messagetext LIKE
'%.LoLoGate.Cfg_GateCond%'
OR Messagetext LIKE
'%.LoLo.Cfg_AllowShelve%'
OR Messagetext LIKE
'%.LoLo.Cfg_AllowDisable%'
OR Messagetext LIKE
'%.LoLo.Cfg_AlmMinOnT%'
OR Messagetext LIKE
'%.LoLo.Cfg_MaxShelfT%'
OR Messagetext LIKE
'%.LoLo.Cfg_Cond%'
OR Messagetext LIKE
'%.LoLo.Cfg_Tag%'
OR Messagetext LIKE
'%.Cfg_HasFailAlm%'
OR Messagetext LIKE
'%.Cfg_FailResetReqd%'
OR Messagetext LIKE
'%.Cfg_FailAckReqd%'
OR Messagetext LIKE
'%.Cfg_FailSeverity%'
OR Messagetext LIKE
'%.Cfg_FailOnDly%'
OR Messagetext LIKE
'%.Cfg_FailOffDly%'
OR Messagetext LIKE
'%.Cfg_FailGateDly%'
OR Messagetext LIKE
'%.FailGate.Cfg_InpCond%'
OR Messagetext LIKE
'%.FailGate.Cfg_GateCond%'
OR Messagetext LIKE
'%.Fail.Cfg_AllowShelve%'
OR Messagetext LIKE
'%.Fail.Cfg_AllowDisable%'
OR Messagetext LIKE
'%.Fail.Cfg_AlmMinOnT%'
OR Messagetext LIKE
'%.Fail.Cfg_MaxShelfT%'
OR Messagetext LIKE
'%.Fail.Cfg_Cond%'
OR Messagetext LIKE
'%.Fail.Cfg_Tag%'
OR Messagetext LIKE
'%.Cfg_PCmdClear%'
OR Messagetext LIKE
'%.Cfg_ProgDefault%'
OR Messagetext LIKE
'%.Cfg_SetTrack%'
OR Messagetext LIKE
'%.Pset_LoLim%'
OR Messagetext LIKE
'%.Oset_LoLim%'
OR Messagetext LIKE
'%.Pset_LoLoLim%'
OR Messagetext LIKE
'%.Oset_LoLoLim%'
OR Messagetext LIKE
'%.Pset_HiLim%'
OR Messagetext LIKE
'%.Oset_HiLim%'
OR Messagetext LIKE
'%.Pset_HiHiLim%'
OR Messagetext LIKE
'%.Oset_HiHiLim%'
OR Messagetext LIKE
'%.Cfg_FailHiLim%'
OR Messagetext LIKE
'%.Cfg_FailLoLim%'
OR Messagetext LIKE
'%.Cfg_FailDB%' )))

begin

declare @StartPOS INT
SET @StartPOS = (SELECT CHARINDEX(']', [MessageText], 1)FROM Diagnostics)

Update Diagnostics
Set Newtag = SUBSTRING([MessageText],CHARINDEX(']',[MessageText]),CHARINDEX('''.',[MessageText], @StartPOS))


/* The above portion of code takes [message Text] which looks like
"Write '70' to '[CMS]CMS_TEST2.OSet_HiHiLim'. Previous value was '88'."
and the @StartPOS that will output the Length but when trying to change the length of the string it does not seem to work.

If I enter a Value or '1'
My NewTag looks like ']CMS_TEST2.OSet_HiHiLim' (This is Perfect)
If I enter a Value of '10'
My Tag Looks Like ']CMS_TEST2.OSet_HiHiLim w'
If I enter a Value of '100'
My Tag Looks Like ']CMS_TEST2.OSet_HiHiLim wa'

Figured it out... Took the route of creating 3 local variables that I used to do some basic subtraction instead of the using CHARINDEX to find the start Position.

I noticed you have a wildcard on both sides of the string. %string%. If the string you are searching for is a prefix or suffix I think you can remove one of the wildcards. This will turn it into an index seek/lookup from a pure index scan. It might improve the performance.