Hi Team,
ADS account update jobs are failing with error.
Error committing object 26d067ce-d586-4844-a479-a181d8143029.(Error: A constraint violation occurred.
Error in COM access layer: number: 8007202F, description: A constraint violation occurred.
I am trying to find the violation using below query but getting error as "Database error 9438: XML parsing: line 1, character 7420, text/xmldecl not at the beginning of input"
Version 8.1
Barry Jackson Could you please help here?
Declare @MyTempTable Table
(
ParameterValue varchar(max)
)
Declare @MyTempTable2 Table
(
OpTable varchar(50),
OpUID varchar(50),
Display varchar(50),
Columnname varchar(50),
OldValue varchar(100),
NewValue varchar(100)
)
DECLARE @X AS XML, @hDoc AS INT
Insert into @MyTempTable (ParameterValue)
select p.ParameterValue
from Jobqueue q
cross apply dbo.QBM_FCVJobParameterToList(q.ParamIN) p
where
JobChainName = 'CCC_ADS_ADSAccount_Update/(De-)activate'
and Ready2EXE = 'frozen'
and p.ParameterName = 'CausingEntityPatch'
UPDATE @MyTempTable
SET ParameterValue = Replace(ParameterValue,'<', '<')
WHERE ParameterValue LIKE '%<%';
UPDATE @MyTempTable
SET ParameterValue = Replace(ParameterValue,'>', '>')
WHERE ParameterValue LIKE '%>%';
UPDATE @MyTempTable
SET ParameterValue = Replace(ParameterValue,'', '')
WHERE ParameterValue LIKE '%%';
declare XmlList cursor for
select ParameterValue from @MyTempTable
OPEN XmlList
FETCH NEXT FROM XmlList
INTO @X
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_xml_preparedocument @hDoc OUTPUT, @X
Insert into @MyTempTable2 (OpTable,
OpUID,
Display,
Columnname,
OldValue,
NewValue)
SELECT OpTable, OpUID, Display, Columnname, OldValue, NewValue
FROM OPENXML(@hDoc, '/Patch/Diff/Op')
WITH
(
OpTable [varchar](50) '../../Key/T',
OpUID [varchar](50) '../../Key/P',
Display [varchar](50) '../../@Display',
Columnname [varchar](50) '@Columnname',
OldValue [varchar](100) 'OldValue',
NewValue [varchar](100) 'Value'
)
EXEC sp_xml_removedocument @hDoc
FETCH NEXT FROM XmlList
INTO @X
END
CLOSE XmlList
DEALLOCATE XmlList
Select * from @MyTempTable2 order by 1,2,3,4
