ads account update frozen jobs

Hi,

Use the new version of the query as posted here.

The query you have will truncate values in the result set which might be why it is failing.

HTH, Barry.

Hi Barry, I am getting this error Unfortunately, the page you've requested no longer exists. Please use the search form above to locate the information you're interested in. Could you please post the query here.

Hi Barry, I am getting this error Unfortunately, the page you've requested no longer exists. Please use the search form above to locate the information you're interested in. Could you please post the query here.

Enjoy!

(Don't forget to adjust the where clause.)

Declare @MyTempTable Table
(
ParameterValue varchar(max)
)
Declare @MyTempTable2 Table
(
OpTable varchar(64),
OpUID varchar(50),
Display varchar(264),
Columnname varchar(64),
OldValue varchar(max),
NewValue varchar(max)
)

DECLARE @X AS XML, @hDoc AS INT

Insert into @MyTempTable (ParameterValue)
select p.ParameterValue
--from jobhistory q
from JobQueue q
cross apply dbo.QBM_FCVJobParameterToList(q.ParamIN) p
where
JobChainName = 'CCC_ADSGroup_Update'
and Ready2EXE = 'frozen'
--and waserror=0
and p.ParameterName = 'CausingEntityPatch'
--and ParamIN like '%eu.res.FSFrankfurt-ISRO.ls%'

UPDATE @MyTempTable
SET ParameterValue = Replace(ParameterValue,'&lt;', '<')
WHERE ParameterValue LIKE '%&lt;%';

UPDATE @MyTempTable
SET ParameterValue = Replace(ParameterValue,'&gt;', '>')
WHERE ParameterValue LIKE '%&gt;%';

UPDATE @MyTempTable
SET ParameterValue = Replace(ParameterValue,'&#x7', '')
WHERE ParameterValue LIKE '%&#x7%';

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](64) '../../Key/T',
OpUID [varchar](50) '../../Key/P',
Display [varchar](264) '../../@Display',
Columnname [varchar](64) '@Columnname',
OldValue [varchar](max) 'OldValue',
NewValue [varchar](max) '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

Hi Barry Jackson  still getting the same error running below statement  

Declare @MyTempTable Table
(
ParameterValue varchar(max)
)
Declare @MyTempTable2 Table
(
OpTable varchar(64),
OpUID varchar(50),
Display varchar(264),
Columnname varchar(64),
OldValue varchar(max),
NewValue varchar(max)
)

DECLARE @X AS XML, @hDoc AS INT

Insert into @MyTempTable (ParameterValue)
select p.ParameterValue
--from jobhistory q
from JobQueue q
cross apply dbo.QBM_FCVJobParameterToList(q.ParamIN) p
where
JobChainName = 'CCC_ADS_ADSAccount_Update/(De-)activate'
and Ready2EXE = 'frozen'
--and waserror=0
and p.ParameterName = 'CausingEntityPatch'
--and ParamIN like '%eu.res.FSFrankfurt-ISRO.ls%'

UPDATE @MyTempTable
SET ParameterValue = Replace(ParameterValue,'&lt;', '<')
WHERE ParameterValue LIKE '%&lt;%';

UPDATE @MyTempTable
SET ParameterValue = Replace(ParameterValue,'&gt;', '>')
WHERE ParameterValue LIKE '%&gt;%';

UPDATE @MyTempTable
SET ParameterValue = Replace(ParameterValue,'&#x7', '')
WHERE ParameterValue LIKE '%&#x7%';

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](64) '../../Key/T',
OpUID [varchar](50) '../../Key/P',
Display [varchar](264) '../../@Display',
Columnname [varchar](64) '@Columnname',
OldValue [varchar](max) 'OldValue',
NewValue [varchar](max) '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   error message :  Database error 9438: XML parsing: line 1, character 4675, text/xmldecl not at the beginning of input
XML parsing: line 1, character 4675, text/xmldecl not at the beginning of input

Hi,

This was written for V7/V8 ...... I have used it up to 8.1.2 ..... if you are on a version later than that I can only assume that something has changed inside the CausingEntityPatch ...... the only thing I can suggest is to include some select/print statements to examine the data at each point.  Perhaps this will identify where the data cannot be parsed.

HTH, B.