Skip to main content

Notifications

Small and medium business | Business Central, N...
Suggested answer

GP Item Transactions Quantity does not allow DBNull

(0) ShareShare
ReportReport
Posted on by 320

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,'

pastedimage1662634919864v1.png

  • Suggested answer
    Andrea Melroe Profile Picture
    Andrea Melroe 3,437 on at
    RE: GP Item Transactions Quantity does not allow DBNull

    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!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Announcing Category Subscriptions!

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,359 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,370 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans