Dynamics Community Forum Thread Details

GP Item Transactions Quantity does not allow DBNull

We're migrating from GP to BC and receiving the following error message. I've reveiwed IV10001 and IV30301 and there
are no transactions with 0 or Null in TRXQTY.

If I run Diagnostic I receive notice 47 Tables Successful, including GP Item Transactions.

How many tables should I expect in the Tables Successful box?

TEST0$GP Item Transactions$feeb3504-556e-4790-b28d-a2b9ce302d81

Failure happened on 'Sink' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.InvalidOperationException,Message=Column 'Quantity' does not allow DBNull.Value.,Source=System.Data,'

Hi SMS,

I suggest you run the following script against the GP company database you’re migrating from. This script will show you inventory quantities being extracted from GP to be pushed into BC:

SELECT CONVERT(NVARCHAR(75), (substring(a.ITEMNMBR,1,20))) AS [No], CONVERT(NVARCHAR(11), rtrim(a.TRXLOCTN)) AS [Location], CONVERT(DATETIME, a.DATERECD) AS [DateReceived], (CASE ITMTRKOP WHEN 2 THEN 1 WHEN 3 THEN b.QTYRECVD-b.QTYSOLD ELSE a.QTYRECVD-a.QTYSOLD END) AS [Quantity], CONVERT(NVARCHAR(21), RCPTNMBR) AS [ReceiptNumber], COALESCE(CONVERT(NVARCHAR(21), rtrim(SERLNMBR)), '') AS [SerialNumber], COALESCE(CONVERT(NVARCHAR(21), rtrim(LOTNUMBR)), '') AS [LotNumber], COALESCE(CONVERT(DATETIME, EXPNDATE), 0) AS [ExpirationDate], CONVERT(DECIMAL(28,10), a.UNITCOST) AS [UnitCost], CONVERT(DECIMAL(28,10), CURRCOST) AS [CurrentCost], CONVERT(DECIMAL(28,10), STNDCOST) AS [StandardCost], a.RCTSEQNM AS [ReceiptSEQNumber] FROM [TWO].dbo.IV10200 a
INNER JOIN IV00101 item ON a.ITEMNMBR = item.ITEMNMBR
LEFT JOIN IV00300 b ON a.TRXLOCTN = b.LOCNCODE AND a.DATERECD = b.DATERECD AND a.ITEMNMBR = b.ITEMNMBR AND a.RCTSEQNM = b.RCTSEQNM
LEFT JOIN IV00200 c ON a.TRXLOCTN = c.LOCNCODE AND a.DATERECD = c.DATERECD AND a.ITEMNMBR = c.ITEMNMBR AND a.RCTSEQNM = c.RCTSEQNM
WHERE RCPTSOLD = 0
ORDER BY[No], [Location], [DateReceived]

Take a look at the results to find the record(s) with a null quantity. This should help you to find the item/value causing the error, so you can fix the data prior to testing the migration again.

As a side note, here’s a link to our GP to BC Migration landing page where you’ll find all sorts of great information:

Landing Page for GP to BC Migration Information - Dynamics 365 Business Central Community

To answer your question about how many tables you should expect to see in the successful box: It really depends on what data exists in the company you're migrating from. This will vary from migration to migration. With that said, here's a blog that talks about which GP SQL tables the tool looks at, along with where the GP data lands in the BC staging tables:

GP Migration Tool: GP SQL Tables Used, Whether GP Data can be Incrementally Replicated, and Where GP Data Lands in the Business Central Staging Tables

Hope this helps!

Quick Links

Leaderboard > Small and medium business | Business Central, NAV, RMS