Parsing a lange XML data-column from one server to another
-
Dear PS-scripters.
As a newbee I encouter the next challange....
I have a PS script that needs to copy data from server A (table VV) to server B (table VV).
One of the columns is an XML column.
Now I run:
Invoke-Sqlcmd -ServerInstance $DestServer -Database $DestDatabase -MaxBinaryLength 204850000 -Query $InsertQuery
The $InsertQuery holds just one INSERT statement (one row) with a large XML-column-value.
But get the error message:
Invoke-Sqlcmd : XML parsing: line 1, character 4000, unexpected end of input
Msg 9400, Level 16, State 1, Procedure , Line 1.
If I fill the XML string (manually) with only a few characters, it does work so the mechanism works....
The table is identical on both servers - the column in question has data type XML on both servers.
Now this barrier of 4000 is regulated somewhere, so it may also be possible to overwrite it.....
Where and how?
If not - please your advice on this.
Regards
Guus Kramer.
the Netherlands
-
My guess is that somewhere in the process there is a cast, implicit or explicit, to NVARCHAR(4000), which will obviously generate that error if the content is greater than NVARCHAR(4000).
😎
If you can, it would be helpful to see the relevant parts of the code.
-
Eirikur,
Thanks for the reply - I will provide the (basic) code used:
----------------------------------------------------------------------------------------
Table on the CMS server to collect info from different other servers:
CREATE TABLE [dbo].[XML_REPORT_ALL](
[Logdate] [datetime] NULL,
[RunNum] [int] NULL,
[ServerName] [varchar](400) NULL,
[Report] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
----------------------------------------------------------------------------------------
Table on the Source-servers where the XML is made and stored localy:
CREATE TABLE [dbo].[XML_REPORT](
[Logdate] [datetime] NULL,
[RunNum] [int] NULL,
[ServerName] [varchar](400) NULL,
[Report] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
----------------------------------------------------------------------------------------
When a SP is executed it inserts data into the XML_REPORT table (XML-into the Report-column and a new (increasing) RunNum(ber)).
When this is done the row with the highest RowNum(ber) is selected in an array (converted to an insert-statement) and must be inserted into the XML_REPORT_ALL on the CMS server.
----------------------------------------------------------------------------------------
Collecting the last row om the Course-server (later version as stated in the initial post):
$SelectQuery = Invoke-Sqlcmd -ServerInstance $SourceServer -Database $SourceDatabase -Query $SelectQuery -MaxCharLength $c_XmlMaxLength -QueryTimeout 65535 -ErrorAction Stop
----------------------------------------------------------------------------------------
Inserting the data from the arrey converted to an insert statement):
Invoke-Sqlcmd -ServerInstance $DestServer -Database $DestDatabase -Query $InsertQuery -MaxCharLength $c_XmlMaxLength -QueryTimeout 65535 -ErrorAction Stop
----------------------------------------------------------------------------------------
Write-output from the PS script:
INSERT INTO XML_REPORT_ALL ( logdate, Report, runnum, servername ) VALUES ( '07/11/2022 11:43:12','<LOG><ALG><ROW><CUSTOMER>... many more info here..... TYPE><','5','COURSE-SQLSERVER' )
After the "TYP><" the XML string is cut of (so it seems).....
----------------------------------------------------------------------------------------
Error-message:
Invoke-Sqlcmd : XML parsing: line 1, character 4000, unexpected end of input
Msg 9400, Level 16, State 1, Procedure , Line 1.
At line:71 char:1
+ Invoke-Sqlcmd -ServerInstance $DestServer -Database $DestDatabase -Qu ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
----------------------------------------------------------------------------------------
Hope this is more clear to investigate.....
Regards,
Guus Kramer
-
Thank you Guus for the reply, Quick question, do you have the source code for the actual select code used in the source database/server?
😎
-
Eirikur,
This is the select from the table on the source server:
-----------------------------------------------------------------------
#Retreiving the last written XML from the source-server
$SelectQuery = @"select logdate, runnum, servername, Report from.[dbo].[XML_REPORT] where RunNum = (select Max(RunNum) from [tablename].[dbo].[XML_REPORT])"@
---------------------------------------------------------------------
This select is returning one row only (withe the highest 'RunNum' as inserted by a previous statement).
When executing this select in SSMS the complete XML is returned...
Regards,
Guus
-
This won't fix the issue, but your query can be written more tidily as
SELECT TOP (1) logdate
,runnum
,servername
,Report
FROM dbname.dbo.XML_REPORT
ORDER BY RunNum DESC;(I assume your database is not called 'tablename'!)
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
First thing to try would be adding an explicit conversion of the Report column to an XML data type, even if the source column is of the type XML.
😎
Another test you can do is to use the same queries to transfer an NVARXHAR(MAX) column from one server to another, just use the REPLICATE function to populate the source column to something greater than 8000 bytes 😉
- gkramer 23701 wrote:
Write-output from the PS script:
INSERT INTO XML_REPORT_ALL ( logdate, Report, runnum, servername ) VALUES ( '07/11/2022 11:43:12','<LOG><ALG><ROW><CUSTOMER>... many more info here..... TYPE><','5','COURSE-SQLSERVER' )
I guess I'll never understand why people intentionally shoot performance in the face by executing an INSERT per row regardless of what tool they use.
My recommendation is to make a call to BCP.EXE to create a "native" export from the source server and then make another call to BCP.EXE to import from that file to the table on the target server. It'll blow the doors off of virtually all other methods for both simplicity, low resource usage, and transfer rates.
https://docs.microsoft.com/en-us/sql/tools/bcp-utility/
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Even though the MS documentation states that literals longer than 4000 characters are cast as NVARCHAR(MAX) this doesn't seem to be the case.
Constants (Transact-SQL) - SQL Server | Microsoft Docs
At least that is what I conclude after doing a simple test:
declare @test nvarchar(max)
set @test = N'<rec>' + replicate('<row>x</row>',1000) + '</rec>'
select len(@test) as TestLenTestLen = 4000
The same test with a non-unicode literal (no N'):
declare @test nvarchar(max)
set @test = '<rec>' + replicate('<row>x</row>',1000) + '</rec>'
select len(@test) as TestLenTestLen = 8000
My conclusion: Literals are cut off at 8000 chars, or 4000 chars for unicode literals.
- kaj wrote:
Even though the MS documentation states that literals longer than 4000 characters are cast as NVARCHAR(MAX) this doesn't seem to be the case.
Constants (Transact-SQL) - SQL Server | Microsoft Docs
At least that is what I conclude after doing a simple test:
declare @test nvarchar(max)
set @test = N'<rec>' + replicate('<row>x</row>',1000) + '</rec>'
select len(@test) as TestLenTestLen = 4000
The same test with a non-unicode literal (no N'):
declare @test nvarchar(max)
set @test = '<rec>' + replicate('<row>x</row>',1000) + '</rec>'
select len(@test) as TestLenTestLen = 8000
My conclusion: Literals are cut off at 8000 chars, or 4000 chars for unicode literals.
I've run into the issue a whole lot with Dynamic SQL. The "fix" is to make sure that you cast the all the literals as the correct MAX datatype and I DO mean ALL of them.
DECLARE @Test NVARCHAR(MAX)
;
SELECT @Test = CONVERT(NVARCHAR(MAX),N'<rec>')
+ REPLICATE(CONVERT(NVARCHAR(MAX),'<row>x</row>'),2000)
+ CONVERT(NVARCHAR(MAX),N'</rec>')
;
SELECT TestLen = LEN(@Test)
;Result:
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Can you just create a linked server in SSMS, then just do an "insert into select * from" command in SSMS?
If you cannot create a linked server then, if it is a one off insert, you could create a new database on the current server, Insert the rows from the table you want to export into it, take a backup of the new database, then restore the new database on the server you want to copy the data to?
- Jonathan AC Roberts wrote:
Can you just create a linked server in SSMS, then just do an "insert into select * from" command in SSMS?
If you cannot create a linked server then, if it is a one off insert, you could create a new database on the current server, Insert the rows from the table you want to export into it, take a backup of the new database, then restore the new database on the server you want to copy the data to?
That would be my drother, as well.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Guys,
thanks for all information but not everyone understands what I want to do (and script style is irrelevant in this issue):
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.
==================================================================
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
-
what you need is to convert your script to something more clear and easier to maintain - and more robust as well.
from what I can see your intention is to copy the data across one server to another without doing any "change" to the data like adding new columns or doing some manipulation of the source data.
for this, and to avoid the issue you are getting, the best is to transfer the data all the way through as a datatable object.
few options here - one that is likely something you should dig into and really learn how to use is Dbatools (https://dbatools.io/) which contains a huge amount of commands useful for dba work (and not only)
the particular example you would use almost out of the box is Example 2 (without the create table parameter) or 8 on https://docs.dbatools.io/Write-DbaDbTableData
if you can't use dbatools for some reason you can still use a custom made write-datatable for this purpose.
SQL Server itself also supplies some powershell scripts to do just this - see example of how to use at https://www.mssqltips.com/sqlservertip/4627/new-powershell-cmdlets-to-read-and-write-sql-server-tables/
-
Just an opinion but using XML to transfer data between servers seems counter productive to me. Instead, I'd have a common area that all the servers could write to and I'd BCP the data out of a real table on each server using the "Native" format as a dated file for each server and then write an import on the main server to do whatever you need to do to combine things (whatever).
Since you can't (or won't) use xp_CmdShell (a mistake in my opinion but that's a whole nother discussion as to why it's NOT actually a security risk when done correctly), could write a simple batch query that uses SQLCMD and is scheduled by Windows Task Scheduler. Then, your main server would simply us xp_Dirtree to get the file names and you'd use BULK INSERT to load up your "ALL" table and Bob's your uncle.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply