limit parsing a XML -datafield from one server to the CMS using Powershell

• gkramer 23701

  Ten Centuries

  Points: 1381

  More actions

  July 19, 2022 at 5:41 am

  #4065113

  Guys,

  I do need some help on a limit parsing a XML -datafield from one server to the CMS using Powershell

  ---------------------------------------------------------------

  I registered some servers in a folder in the CMS (SSMS).

  On each server there is a DBA-database holding the XML_REPORT table and a Stored-Procedure (which generates a XML-report).

  On the CMS itself there is an extra table XML_REPORT_ALL.

  From the CMS server I execute the SP on ALL servers (even the CMS itself).

  The SP generates the XML and stores it locally in the XML_REPORT table (retention 6 months).

  As soon as the XML has been inserted in the XML_REPORT table with a max(RunNum)+1 this last insert must be copied to the CMS server into table XML_REPORT_ALL.

  Whenever all XML-reports are gathered these reports must be sent to a URL for creating charts (this part is still to be done).

  The execution of the SP on the servers is working fine but transferring the data from the servers to the CMS-server gives the error (limited at 4000 chars).

  Due to security reasons Linked-servers, SP_OAmethode, open_rowset, open_query and XP_Cmdshell are not allowed !

  Here is the complete script for copying from server to CMS :

  ======================================================

  CLS

  # This script retrieves the last row written in the [XML_REPORT] table from the Course-servers (reporting to the CMS)

  # and insert it into the table [XML_REPORT_ALL] on the CMS server

  #Import the SQLPS module to use the Invoke-SQLCmd

  Import-Module -Name SQLServer -DisableNameChecking -Force

  $SourceServer = 'SQLserver'

  $SourceDatabase = 'DBAmanagment'

  $SourceTable = 'XML_REPORT'

  $DestServer = 'SQLserverDBA'

  $DestDatabase = 'DBAmanagement'

  $DestTable = 'XML_REPORT_ALL'

  $c_XmlMaxLength = 2097152

  #Retrieving the last written XML from the source-server

  $SelectQuery = @"

  select logdate, runnum, servername, Report from.[dbo].[XML_REPORT] where RunNum = (select Max(RunNum) from [DBAmanagment].[dbo].[XML_REPORT] )

  "@

  try

  {

  $SelectQuery = Invoke-Sqlcmd -ServerInstance $SourceServer -Database $SourceDatabase -Query $SelectQuery -MaxCharLength $c_XmlMaxLength -QueryTimeout 65535 -ErrorAction Stop

  $returnvalue = $SelectQuery.ItemArray[0]

  if ($returnvalue -eq $null -or $returnvalue -eq "") {

  write-output "Failed to get XML-data"

  }

  $v_XmlLength = $SelectQuery.ItemArray[3].length

  if ($v_XmlLength -eq $c_XmlMaxLength) {

  write-output "The XML output exceeds $c_XmlMaxLength characters"

  }

  write-output "THE SIZE OF THE XML FILE IS $v_XmlLength CHARACTERS OF THE MAX $c_XmlMaxLength CHARACTERS. "

  }

  catch

  {

  write-output "The XML output could not be retreived"

  continue

  }

  $Properties = $SelectRows |

  Get-Member -MemberType Property |

  Select-Object -ExpandProperty Name

  $InsertValues = foreach( $Row in $SelectRows ){

  $Values = foreach( $Prop in $Properties ){

  "'$($Row.$Prop)'"

  }

  $Values -join ','

  }

  #write-output $Insert

  # creating the INSERT-string for handling the data for INSERT into de CMS-server

  $Insert = $InsertValues -join '), ('

  $InsertQuery = "INSERT INTO $DestTable ( $( $Properties -join ', ' ) ) VALUES ( $Insert )"

  write-output "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&"

  write-output $InsertQuery

  #write the last-writen XML from the source-server tot the CMS server

  #$InsertQuery2 is for testing !!!

  #$InsertQuery2 = "INSERT INTO XML_REPORT_ALL ( logdate, Report, runnum, servername ) VALUES ( '07/11/2022 11:43:12','<CMDB>hallo</CMDB>','5001','SQLDB1P' ) "

  Invoke-Sqlcmd -ServerInstance $DestServer -Database $DestDatabase -Query $InsertQuery -MaxCharLength $c_XmlMaxLength -QueryTimeout 65535 -ErrorAction Stop

  ==================================================================

  This results in ERROR:

  Invoke-Sqlcmd : XML parsing: line 1, character 4000, unexpected end of input

  Msg 9400, Level 16, State 1, Procedure , Line 1.

  NOTE: using $InsertQuery2 the insert in the CMS is made so the mechanism is working.

  ==================================================================

  Where is this limit of 4000 chars set and can I overrule it?

  or

  Do I have to re-write the complete SP (not generating XML)?

  Please help me out.

  Guus Kramer

• Site Owners

  SSC Guru

  Points: 80320

  More actions

  July 20, 2022 at 6:10 am

  #4065722

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• July 22, 2022 at 10:34 am

  This was removed by the editor as SPAM

• July 25, 2022 at 10:52 am

  This was removed by the editor as SPAM