Error between 2 installations

alainmartin

I have this error went I run a script on a another server.

Server: Msg 518, Level 16, State 1, Line 3
Cannot convert data type text to ntext (or ntext to text)
The statement has been terminated.

The error occur with the blanks at the beginning of a line with blanks ....

Why?

Extract of the script:

EXEC(N'UPDATE [dbo].[gd_tpl] SET [tpl_poubelle]=N''<?xml version="1.0" encoding="UTF-8"?>
<document-definition><document-type>1afa63e0-bb9b-1004-881a-000476d77077</document-type><document-templates xsi:type="java:com.magrit.gesdoc.template.DocumentTemplate" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><filename>AUT_DEB_FR</filename><lng-cd>fr</lng-cd><title>Autorisation de débourser</title></document-templates><document-templates xsi:type="java:com.magrit.gesdoc.template.DocumentTemplate" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><filename>AUT_DEB_EN</filename><lng-cd>en</lng-cd><title>Autorisation de débourser</title></document-templates><data-source xsi:type="java:com.magrit.gesdoc.template.GenDocListDataSetDefinition" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><sub-querys xsi:type="java:com.magrit.gesdoc.template.DocumentQueryTemplate"><join-table-columns xsi:type="java:com.magrit.gesdoc.template.JoinTableColumn"><main-table-column-name>DEM_NRI</main-table-column-name><join-table-column-name>DEM.DEM_NRI</join-table-column-name><domain-id>INTEGER$</domain-id></join-table-columns><query autoloadable="true"><root-table physical-name="DEM" id="DEM"><table-caption><captions language="en" xsi:type="caption-label-info">Demande de service</captions><captions language="fr" xsi:type="caption-label-info">Demande de service</captions></table-caption><columns physical-name="DEM_NRI" domain-id="INTEGER$" table-parent="DEM" id="DEM_NRI" length="10" scale="0" mandatory="true" xsi:type="source-model-column"><column-caption><captions language="en" xsi:type="caption-label-info">Référence</captions><captions language="fr" xsi:type="caption-label-info">Référence</captions></column-caption></columns><primary-keys column-name="DEM_NRI" domain-id="INTEGER$" xsi:type="source-model-primary-key"/><relations physical-name="DCR" table-parent="DEM" id="DCR" xsi:type="source-model-table"><table-caption><captions language="en" xsi:type="caption-label-info">Demande de crédit</captions><captions language="fr" xsi:type="caption-label-info">Demande de crédit</captions></table-caption><primary-keys column-name="DCR_NRI" domain-id="INTEGER$" xsi:type="source-model-primary-key"/><relations physical-name="CDCR" table-parent="DCR" id="CDCR_EMP" xsi:type="source-model-table"><table-caption><captions language="fr" xsi:type="caption-label-info">Client
de la demande de crédit</captions><captions language="en" xsi:type="caption-label-info">Client
de la demande de crédit</captions></table-caption><columns physical-name="CDCR_DT_STATUT_RES" domain-id="DATE$" table-parent="CDCR_EMP" id="CDCR_DT_STATUT_RES" length="0" scale="0" mandatory="false" xsi:type="source-model-column"><column-caption><captions language="fr" xsi:type="caption-label-info">Date
statut résidence actuele</captions><captions language="en" xsi:type="caption-label-info">Date
statut résidence actuele</captions></column-caption></columns><primary-keys column-name="CLI_NRI" domain-id="INTEGER$" xsi:type="source-model-primary-key"/><primary-keys column-name="DCR_NRI" domain-id="INTEGER$" xsi:type="source-model-primary-key"/><relation-info sequence="12" multiple="false" should-be-outer="false" natural="true"><join-columns from="DCR_NRI" to="DCR_NRI" xsi:type="join-column"/><join-filters xsi:type="java:com.magrit.mglibx.model.JoinFilter"><column>REL_NRI</column><filter>=1</filter></join-filters></relation-info></relations><relations physical-name="CDCR" table-parent="DCR" id="CDCR" xsi:type="source-model-table"><table-caption><captions language="fr" xsi:type="caption-label-info">Client
de la demande de crédit</captions><captions language="en" xsi:type="caption-label-info">Client
de la demande de crédit</captions></table-caption><columns physical-name="CDCR_NO_SEQ" domain-id="SHORT$" table-parent="CDCR" id="CDCR_NO_SEQ"'' WHERE [tpl_cd]=N''$AUT_DEB''')

Brian Donahue

Hello,

What is the datatype of the tpl_poubelle column? I think that Data Compare always puts the N at the front of every text value. I can see how this could potentially cause a problem with a non-Unicode field like text or varchar or char.

alainmartin

Hello,

sorry for the delay... :-(

The datatype is text.

As I said, it append only on the production server, not went I test it on development server. Environnement problem?

I erase some blanks and the problem disappear.

Thnaks.

Brian Donahue

Perhaps a different collation order on the column between these two databases? Maybe you can use SQL Compare to point this out.