Transfer DirPartyTable with Test Data TRansfer Tool - BCP Error

(0) Share

Report

Posted on by ThomasWilke

766

Hi All,

I'm trying to Transfer the DirPartyTable with the Test Data Transfer Tool (TDTT) but I always get the following BSP Error: "Warning: BCP Import with a format file will convert empty strings in delimited columns to NULL" and after that the Export is not created correctly.

Has anyone an idea on how to folve this? I tried to create a filter.XML file which filters out all the columns that can contain Null values. Than I'm able to do the Export and Import but the imported data is not complete and it creates Errors within AX.

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

André Arnaud de Cal... 301,130 Super User 2025 Season 2 on at

Like (0)

Report

Hi Thomas,

Can you tell more about the errors you get in AX?

Was this reply helpful? Yes No
ThomasWilke 766 on at

Like (0)

Report

Yes I can, but that's not solving my problem.

If I exclude the columns that lead to the BSP Errors they will be imported into the database with 0 or '' in the excluded columns instead of NULL. So what I did is to write some SQL scripts to correct most of it. But these scripts are all just workarounds.

For me it is important to get a complete export of the table to get an exact copy.

Was this reply helpful? Yes No
André Arnaud de Cal... 301,130 Super User 2025 Season 2 on at

Like (0)

Report

Hi Thomas,

I asked the question to be able to better understand your issue. I have used the TDTT until now without major problems. The only error I had was when I forgot to install some SQL components.

Did you use the xpo to generate new xml metadata and used this new file?

Do you have some related events in the Windows event viewer or perhaps some more log files of the SQL server?

Was this reply helpful? Yes No
ThomasWilke 766 on at

Like (0)

Report

Hi André,

okay if that works for you I'll try it in a contoso Environment to figure out where the differences are.

to answer your questions: I installed the latest Version of TDTT from Information source. I imported the xpo and created the XML file. After that I copied it to the lists folder and run the Export in a command window running with admin priviliges.

During the export the error Count is increasing and it Fails for 20 tables. The only info I get is in the DPLog.xml

It Looks like this: (German Error)

<item>

<status>Failed</status>

<message>Microsoft.Dynamics.AX.Dataport.BulkCopyException:

Kopiervorgang wird gestartet...

SQLState = S1000, NativeError = 0

Error = [Microsoft][SQL Server Native Client 11.0]Warnung: Beim BCP-Import mit einer Formatdatei werden leere Zeichenfolgen in durch Trennzeichen getrennten Spalten in NULL konvertiert.

1 Zeilen kopiert.

Netzwerkpaketgröße (Bytes): 4096

Zeit (ms.) gesamt: 32 Durchschnitt: (31.25 Zeilen pro Sek.)

bcp "SELECT [INVENTBATCHID], [EXPDATE], [ITEMID], [PRODDATE], [DESCRIPTION], [PDSBESTBEFOREDATE], [PDSCOUNTRYOFORIGIN1], [PDSCOUNTRYOFORIGIN2], [PDSDISPOSITIONCODE], [PDSFINISHEDGOODSDATETESTED], [PDSINHERITBATCHATTRIB], [PDSINHERITEDSHELFLIFE], [PDSSAMELOT], [PDSSHELFADVICEDATE], [PDSUSEVENDBATCHDATE], [PDSUSEVENDBATCHEXP], [PDSVENDBATCHDATE], [PDSVENDBATCHID], [PDSVENDEXPIRYDATE], [DATAAREAID], [RECVERSION] = 1, [PARTITION], [RECID] FROM [AX2012_R2_DB_TEMPLATE]..[INVENTBATCH]" queryout "C:\TDTT\TEMPLATE2DEV20140908\INVENTBATCH.out" -E -b 100 -e "C:\TDTT\TEMPLATE2DEV20140908\INVENTBATCH.err" -w -S XYZ-S-SQLAX -T -t "#|EOC|#" -r #|EOR|#\n

bei Microsoft.Dynamics.AX.Dataport.BulkCopy.ExecuteBCPCall(String args) in e:\bt\110700\Source\DataPort\Dataport\Library\BulkCopy.cs:Zeile 331.

bei Microsoft.Dynamics.AX.Dataport.BulkCopy.WriteOutFile(String serverName, SqlTableName table, String query, String folder) in e:\bt\110700\Source\DataPort\Dataport\Library\BulkCopy.cs:Zeile 274.

bei Microsoft.Dynamics.AX.Dataport.DataportQueryExtensions.WriteBulkCopyFiles(IDataportQuery query, String database, String server, String folder) in e:\bt\110700\Source\DataPort\Dataport\Model\DataportQuery.cs:Zeile 94.

bei Microsoft.Dynamics.AX.Dataport.DataportQueue.<>c__DisplayClass8.<>c__DisplayClassc.<ProcessItem>b__7() in e:\bt\110700\Source\DataPort\Dataport\Model\DataportQueue.cs:Zeile 240.

bei Microsoft.Dynamics.AX.Dataport.Functional.<>c__DisplayClass1.<Retry>b__0() in e:\bt\110700\Source\DataPort\Dataport\Library\Functional.cs:Zeile 28.

bei Microsoft.Dynamics.AX.Dataport.DataportQueue.<>c__DisplayClass8.<ProcessItem>b__6(Object state) in e:\bt\110700\Source\DataPort\Dataport\Model\DataportQueue.cs:Zeile 260.</message>

<direction>Export</direction>

<action>Overwrite</action>

<database>AX2012_R2_DB_TEMPLATE</database>

<table>INVENTBATCH</table>

<targetTable>INVENTBATCH</targetTable>

<folder>C:\TDTT\TEMPLATE2DEV20140908</folder>

</item>

As mentioned above I can get rid of These errors either by filling the columns that contain NULL values with empty strings or 0, or by excluding them from the export.

Kind regards,

Thomas

Was this reply helpful? Yes No
ThomasWilke 766 on at

Like (0)

Report

Okay, the same export on a contoso environment ran through without any errors. So I guess it could be related to the SQL Server or the SQL Server settings. I will have a closer look at the SQL Server.

You also mentioned some SQL components that Needs to be installed. What do you think of?

Was this reply helpful? Yes No
ThomasWilke 766 on at

Like (0)

Report

Okay, I found out that it has something to do with the SQL Server. I copied my DB into the contoso machine. Running the TDTT there works fine without errors.

Now I'm trying to identify whether it is a Setting in the SQL Server or just the version or a patch?

The Contoso SQL has the following Version Informations:

Microsoft SQL Server Management Studio 11.0.3368.0

Microsoft Analysis Services Client Tools 11.0.3368.0

Microsoft Data Access Components (MDAC) 6.2.9200.16384

Microsoft MSXML 3.0 6.0

Microsoft Internet Explorer 9.10.9200.16798

Microsoft .NET Framework 4.0.30319.18449

Operating System 6.2.9200

My SQL Server:

Microsoft SQL Server Management Studio 11.0.5058.0

Microsoft Analysis Services-Clienttools 11.0.5058.0

Microsoft Data Access Components (MDAC) 6.1.7601.17514

Microsoft MSXML 3.0 6.0

Microsoft Internet Explorer 9.11.9600.17239

Microsoft .NET Framework 4.0.30319.18444

Betriebssystem 6.1.7601

Was this reply helpful? Yes No
AG-21051816-0 on at

Like (0)

Report

Hello Thomas, did you solve the problem by installing some components or change an sql server configuration? I've seen the same error today, because there were null values in the table. My problem is that the table is skipped and no data is written to the output directory for a table containing null values.

Was this reply helpful? Yes No
Verified answer

ThomasWilke 766 on at

Like (0)

Report

Hello Andreas,

We were not able to identify the component, update or SQL Server Version that is crating this issue. I had detailed discussions with MS about it.

The way we solved this problem for us was moving the database onto an english Installation of SQL Server 2012 SP2 CU2. There we are able to export the same table with null values.

I know this is just a workaround and I'm happy for every hint that could solve this issue.

Are you using a german SQL Server Installation?

Kind regards,

Thomas

Was this reply helpful? Yes No

Community site session details

Transfer DirPartyTable with Test Data TRansfer Tool - BCP Error

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

Transfer DirPartyTable with Test Data TRansfer Tool - BCP Error

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates