mibuso.com
Home› NAV/Navision Classic Client

Record already exits error (only in SQL)

deV.chdeV.ch Member Posts: 543
edited 2012-12-10 in NAV/Navision Classic Client
Hi,

I'am facing a very strange error on a slightly modded standard function. In Sales Invoices you can insert from lines from posted shipment records, Function is in T111 : InsertInvLineFromShptLine().
I this function only on certain documents we receive the Record xy already exits, but that can not be possible. I already compared the function from that old db to the newest one in 2009 R2, just the customer additions are different. Customer additions, insert one new additional record with this Code: 
  BufferRec.RESET;
  BufferRec.SETRANGE("Document Type", TempSalesLine."Document Type");
  BufferRec.SETRANGE("Document No.", TempSalesLine."Document No.");
  IF BufferRec.FINDFIRST THEN BEGIN

    SalesLine.INIT;
    SalesLine."Line No." := NextLineNo;
    SalesLine."Document Type" := TempSalesLine."Document Type";
    SalesLine."Document No." := TempSalesLine."Document No.";
    SalesLine.INSERT;
    NextLineNo := NextLineNo + 10000;
  END;
It inserts a empty line between different lines from different shipments. This works fine on other invoices, but not on that particular one.

I steped trough code with debugger, NextLineNo always gets incremented before an insert happens.

I also tried with Client Monitor, there is no Line beeing inserted with the same Line No: 
Datum	Zeit	Laufnr.	Funktionsname	Parameter-Nr.	Parameter	Nummer	Daten
02.02.2012	16:22:08.772	694	INSERT	1	Table	37	Sales Line
02.02.2012	16:22:08.772	694	INSERT	7	Record		Belegart='Rechnung',Belegnr.='VR-1554',Zeilennr.='10000'
02.02.2012	16:22:08.772	694	INSERT	14	Source Object		Codeunit 1 ApplicationManagement
02.02.2012	16:22:08.772	694	INSERT	15	Source Trigger/Function		GetGlobalTableTriggerMask(TableID)
02.02.2012	16:22:08.772	694	INSERT	16	Source Line No.	967	
02.02.2012	16:22:08.772	694	INSERT	17	Source Text		EXIT(ChangeLogMgt.GetTableTriggerMask(TableID));
02.02.2012	16:22:08.772	694	INSERT	100	Elapsed Time (ms)		
02.02.2012	16:22:09.101	895	INSERT	1	Table	37	Sales Line
02.02.2012	16:22:09.101	895	INSERT	7	Record		Belegart='Rechnung',Belegnr.='VR-1554',Zeilennr.='20000'
02.02.2012	16:22:09.101	895	INSERT	14	Source Object		Table 111 Verkaufslieferzeile
02.02.2012	16:22:09.101	895	INSERT	15	Source Trigger/Function		InsertInvLineFromShptLine(SalesLine,TempFromDocDim)
02.02.2012	16:22:09.101	895	INSERT	16	Source Line No.	152	
02.02.2012	16:22:09.102	895	INSERT	17	Source Text		SalesLine.INSERT;
02.02.2012	16:22:09.102	895	INSERT	100	Elapsed Time (ms)		
02.02.2012	16:22:09.249	973	INSERT	1	Table	37	Sales Line
02.02.2012	16:22:09.249	973	INSERT	7	Record		Belegart='Rechnung',Belegnr.='VR-1554',Zeilennr.='30000'
02.02.2012	16:22:09.249	973	INSERT	14	Source Object		Table 111 Verkaufslieferzeile
02.02.2012	16:22:09.249	973	INSERT	15	Source Trigger/Function		InsertInvLineFromShptLine(SalesLine,TempFromDocDim)
02.02.2012	16:22:09.249	973	INSERT	16	Source Line No.	152	
02.02.2012	16:22:09.249	973	INSERT	17	Source Text		SalesLine.INSERT;
02.02.2012	16:22:09.249	973	INSERT	100	Elapsed Time (ms)		
02.02.2012	16:22:09.259	989	INSERT	1	Table	37	Sales Line
02.02.2012	16:22:09.259	989	INSERT	7	Record		Belegart='Rechnung',Belegnr.='VR-1554',Zeilennr.='40000'
02.02.2012	16:22:09.259	989	INSERT	14	Source Object		Table 111 Verkaufslieferzeile
02.02.2012	16:22:09.259	989	INSERT	15	Source Trigger/Function		InsertInvLineFromShptLine(SalesLine,TempFromDocDim)
02.02.2012	16:22:09.259	989	INSERT	16	Source Line No.	152	
02.02.2012	16:22:09.259	989	INSERT	17	Source Text		SalesLine.INSERT;
02.02.2012	16:22:09.259	989	INSERT	100	Elapsed Time (ms)		
02.02.2012	16:22:09.268	1005	INSERT	1	Table	37	Sales Line
02.02.2012	16:22:09.268	1005	INSERT	7	Record		Belegart='Rechnung',Belegnr.='VR-1554',Zeilennr.='50000'
02.02.2012	16:22:09.268	1005	INSERT	14	Source Object		Table 111 Verkaufslieferzeile
02.02.2012	16:22:09.268	1005	INSERT	15	Source Trigger/Function		InsertInvLineFromShptLine(SalesLine,TempFromDocDim)
02.02.2012	16:22:09.268	1005	INSERT	16	Source Line No.	152	
02.02.2012	16:22:09.268	1005	INSERT	17	Source Text		SalesLine.INSERT;
02.02.2012	16:22:09.268	1005	INSERT	100	Elapsed Time (ms)		
02.02.2012	16:22:09.369	1100	INSERT	1	Table	37	Sales Line
02.02.2012	16:22:09.37	1100	INSERT	7	Record		Belegart='Rechnung',Belegnr.='VR-1554',Zeilennr.='60000'
02.02.2012	16:22:09.37	1100	INSERT	14	Source Object		Table 111 Verkaufslieferzeile
02.02.2012	16:22:09.37	1100	INSERT	15	Source Trigger/Function		InsertInvLineFromShptLine(SalesLine,TempFromDocDim)
02.02.2012	16:22:09.37	1100	INSERT	16	Source Line No.	42	
02.02.2012	16:22:09.37	1100	INSERT	17	Source Text		SalesLine.INSERT;
02.02.2012	16:22:09.37	1100	INSERT	100	Elapsed Time (ms)		
02.02.2012	16:22:09.37	1102	INSERT	1	Table	37	Sales Line
02.02.2012	16:22:09.37	1102	INSERT	7	Record		Belegart='Rechnung',Belegnr.='VR-1554',Zeilennr.='70000'
02.02.2012	16:22:09.37	1102	INSERT	14	Source Object		Table 111 Verkaufslieferzeile
02.02.2012	16:22:09.37	1102	INSERT	15	Source Trigger/Function		InsertInvLineFromShptLine(SalesLine,TempFromDocDim)
02.02.2012	16:22:09.37	1102	INSERT	16	Source Line No.	65	
02.02.2012	16:22:09.37	1102	INSERT	17	Source Text		SalesLine.INSERT;
02.02.2012	16:22:09.37	1102	INSERT	100	Elapsed Time (ms)		
02.02.2012	16:22:09.396	1146	INSERT	1	Table	37	Sales Line
02.02.2012	16:22:09.396	1146	INSERT	7	Record		Belegart='Rechnung',Belegnr.='VR-1554',Zeilennr.='80000'
02.02.2012	16:22:09.396	1146	INSERT	14	Source Object		Table 111 Verkaufslieferzeile
02.02.2012	16:22:09.396	1146	INSERT	15	Source Trigger/Function		InsertInvLineFromShptLine(SalesLine,TempFromDocDim)
02.02.2012	16:22:09.396	1146	INSERT	16	Source Line No.	152	
02.02.2012	16:22:09.396	1146	INSERT	17	Source Text		SalesLine.INSERT;
02.02.2012	16:22:09.396	1146	INSERT	100	Elapsed Time (ms)

And finally i created a fbk of this db and created a native db (as suggested in a post in another thread) to see if this problem lies in sql, and there you go... it worked

So what is wrong here?
My Blog

Answers

  • rhpntrhpnt Member Posts: 688
    deV.ch wrote:
    I this function only on certain documents we receive the Record xy already exits, but that can not be possible.
    Which "certain" documents? If the function works on other documents then the reason for this message sure lies in the mentioned "certain" documents. If you debugged, then the debugger should have stopped where the error occured, so?
  • deV.chdeV.ch Member Posts: 543
    It happens with 2 documents but there is no obvious difference to the other ones where its working on sql.
    The debugger stopped (thanks to read uncommited, and delayed inserting in sql) on this line: 
    IF SalesOrderLine.GET(
    SalesOrderLine."Document Type"::Order,"Order No.","Order Line No.")

    As i bolded out, in native db, this function works, and there are not more records added is i would expect... I can step trough the code and all lines inserted and their line no's are correct as they should and as they are generated in the test native db.

    EDIT: Ok i got now the difference, it happens only for invoices with foreign currency
    My Blog
  • ajhvdbajhvdb Member Posts: 672
    deV.ch wrote:
    EDIT: Ok i got now the difference, it happens only for invoices with foreign currency

    Do you know why? or it's still a bug?
    Create dynamic Excel or Word documents, ReportX
  • deV.chdeV.ch Member Posts: 543
    Nope it still makes no sense, because in my native test db it works regardless of the currency.

    I only know now that it works in sql when using blank currency code, and that is the reason why we haven't encountered that problem earlier, becuase the customer works with foreign currency for about 1-2 months.
    My Blog
  • ajhvdbajhvdb Member Posts: 672
    So the below code will bypass the problem? for now?

    IF NOT SalesLine.INSERT then SalesLine.MODIFY;
    Create dynamic Excel or Word documents, ReportX
  • rhpntrhpnt Member Posts: 688
    deV.ch wrote:
    The debugger stopped (thanks to read uncommited, and delayed inserting in sql) on this line: 
    IF SalesOrderLine.GET(
    SalesOrderLine."Document Type"::Order,"Order No.","Order Line No.")
    EDIT: Ok i got now the difference, it happens only for invoices with foreign currency
    You can't get such an error at the GET line. Try a line before that.
    deV.ch wrote:
    It inserts a empty line between different lines from different shipments. This works fine on other invoices, but not on that particular one.
    Are you sure that those empty lines get all the right data they need (apart from the line number)?

    If I understand correctly, you are creating batch invoices. Did you check if the error occurs when there are multiple shipments for invoicing?
  • deV.chdeV.ch Member Posts: 543
    @rhpnt

    You CAN get this message at a get statement, try search for buffered insert (http://dynamicsuser.net/blogs/stryk/archive/2007/10/30/ms-dynamics-nav-5-0-service-pack-1.aspx) feature, this is because the record is not realy commited to sql until a read is performend on that table. This is a sql phenomena. I can step trough code, and after the REAL insert there is a bunch uf code, which executes well. Because insert happens when Get on salesline is performed... Have a look at the function in T111 and you will see the situation.
    My Blog
  • deV.chdeV.ch Member Posts: 543
    ajhvdb wrote:
    So the below code will bypass the problem? for now?

    IF NOT SalesLine.INSERT then SalesLine.MODIFY;

    Nope that doesn't solve the problem, because the error happens at the get statement (due tu buffered insert)

    But this stops the error from happening: 
    IF SalesLine.INSERT THEN
    SalesLine.GET(SalesLine."Document Type", SalesLine."Document No.", SalesLine."Line No.");

    But now the question still is, why does this help? Makes no sense to me honestly.
    My Blog
  • David_SingletonDavid_Singleton Member Posts: 5,479
    This isn't by any chance 4.00 is it? I had the exact same issue in a 4.00 database, and applying hot fixes sorted it out. (sp3CU9)
    David Singleton
  • rhpntrhpnt Member Posts: 688
    deV.ch wrote:
    You CAN get this message at a get statement, try search for buffered insert (http://dynamicsuser.net/blogs/stryk/archive/2007/10/30/ms-dynamics-nav-5-0-service-pack-1.aspx) feature, this is because the record is not realy commited to sql until a read is performend on that table..
    You should read more carefully - in the code snippets you provided there is no end of the current transaction visible.
    deV.ch wrote:
    Because insert happens when Get on salesline is performed... Have a look at the function in T111 and you will see the situation.
    You are wrong on that. A GET statement does not explicitly end the current transaction - a COMMIT does. Hence if you would (unintentionally) start a new one while the current is active, NAV would tell you that with a decent message. Look, until now you stated nearly half a dozen reasons why you could receive that error (that you altered a function in T111, brought up issues ranging from currency in documents to SQL/native database functionality, mentioned transaction issues in NAV versions,...), you should really narrow down your list of symptoms so we can start helping you.
  • deV.chdeV.ch Member Posts: 543
    edited 2012-02-09
    technicaly this is a 2009 R2 DB

    @rhpnt

    As i said take a look at T111 at this function, i provided only the code that is different from standard code.

    Still my question is not how to solve it right now (as you can read above i have a working solution with a get of the same record right after the insert) but a answer to the question why this is happening. <- I tought it's solved but instead the record (No. 80000) is now completly missing (see list at the end of this post)
    I never said a transaction is commited by a get, BUT the buffered insert is flushed by this this is the reason why the error happens on a get statement and not on the insert. BTW again on Bulk Insert (http://msdn.microsoft.com/en-us/library/dd355341.aspx)

    To clarify here again the list i know until now:
    - The error happens only when currency is foreign currency
    - The error happens only on sql
    - I can replace the code with the custom lines mentiond on my initial post with the standard code from a 2009 R2 Database Object, same error occurs!
    - If i get back the return value of insert (IF SalesLine.INSERT THEN;) the error does not occour, but the record isn't inserted eighter, i can see the gap in line no's :
    10000, 20000, 30000, 40000, 50000, 60000, 70000, <- -> 90000, ...
    My Blog
  • ajhvdbajhvdb Member Posts: 672
    Strange indeed..As you obviously have more knowledge about this im still guessing:

    Can you see what SQL does differently in the profiler with and without the currency?
    Create dynamic Excel or Word documents, ReportX
  • deV.chdeV.ch Member Posts: 543
    Unfortunalty SQL Profiler doesn't give me that much information, it's realy hard to tell if there is already such a record inserted. But i can only find one INSERT statement for record 80000, but maybe this is handled by the appplication rather then sql.

    I also tried to compare client monitor with and without foreign currency. There is no obvious difference between the two. With currency (which leads to the error) there are currency exchange calls and so on. but nothing about a additional record insert.

    Edit: Ok i just found a invoice with no foreign currency which generates the error too... ](*,) So Currency can't be the cause
    My Blog
  • deV.chdeV.ch Member Posts: 543 
    IF NOT SalesLine.Insert THEN
  SalesLine.Modify;
    Gives me error that the record not exists... so i realy think the error is wrong and there must be a serious problem somewhere inside nav?


    Next very strange thing is if I modify the code like this: 
        END ELSE
      ERROR(Text001);
  END;


  //SalesLine := SalesOrderLine; // <- Remove this
  SalesLine.init;  // <- Add this
  SalesLine."Line No." := NextLineNo;
  SalesLine."Document Type" := TempSalesLine."Document Type";
  SalesLine."Document No." := TempSalesLine."Document No.";
    Then i get perfectly all records, line no. 80000 is inserted like it should, obviously without the values from SalesOrderLine but that should'nt interest the insert logic at all!
    My Blog
  • deV.chdeV.ch Member Posts: 543
    Ok folks, fasten your seatbelts:

    I imported the fbk i did from this db into a new fresh sql db, and guess what? No error, everything works as expected!

    So now again, what the heck happend with this sql db?
    If possible i would not make this steps with production db (make fbk, import in fresh sql db).
    Any other ideas?
    My Blog
  • David_SingletonDavid_Singleton Member Posts: 5,479
    The bizarre thing is that I had this exact same error ages ago when I converted a client from 3.70 Native to 4.00 SQL. And it was also definitely a SQL specific error. I also discovered the work around of IF insert then. But eventually a hot fix came along that fixed it. I just wonder if maybe its the same bug back again.

    Did you try on a different version? A new hotfix, or maybe going back a version.
    David Singleton
  • ajhvdbajhvdb Member Posts: 672
    The only thing I can say is that strange things happen when running the debugger in local SQL.

    Yesterday, I updated some code and while debugging it was still was using the old code. Restart services didn't help. Removed the objects and imported .txt and compiling did help.
    Create dynamic Excel or Word documents, ReportX
  • deV.chdeV.ch Member Posts: 543
    @David, well i could try a newer hotfix, but i'am already using build 32900 from R2 so i don't expect too much of it.
    You just installed that hotfix and then everything was fine? any recompiling of objects done?
    My Blog
  • David_SingletonDavid_Singleton Member Posts: 5,479
    deV.ch wrote:
    @David, well i could try a newer hotfix, but I'am already using build 32900 from R2 so i don't expect too much of it.
    You just installed that hotfix and then everything was fine? any recompiling of objects done?
    <edit>

    thinking back now it was the other way around. The code form 3.70 NATIVE - 4.00SQL was fine, it was the upgrade to 5.00SQL (no service pack) that caused this error to happen. And we just left the code in there and never checked if it was fixed in later versions. It was about 5 years ago so I don't remember the details too well.
    David Singleton
  • deV.chdeV.ch Member Posts: 543
    Ok i finaly solved it, here are the steps that brought me to the solution.
    I compared the tweo db's i had (not working sql and working sql) with a tool named DBCompare (http://dbcomparer.com/ freeware).
    And found out that lot's of tables had different collation. Not working db had collation: Latin1_General_CI_AS , and the wokring one had: Latin1_General_100_CI_AS.
    So i went to NAV and checked under Database - Alter in register Collation and the collation was <....>[Compatibility].
    To change collation of a db you need to restrict acccess to single user, so i did that and changed collation in nav and now its working!

    puuuh... so no i'am happy at least i have a plausible explanation why this error happend. But imo this should have never happend in the first place, such an error message due to a collation issue is very bad. I hope as few as possible will face that error, but if they do, they will catch that thread, because i found myself a bit lost in such a problem, because most entries related to "record already exists" leads you to posts where people failed in their custom coding...
    My Blog
  • ajhvdbajhvdb Member Posts: 672
    deV.ch wrote:
    So i went to NAV and checked under Database - Alter in register Collation and the collation was <....>[Compatibility].

    Glad you found it..

    Could you give more detailes about the ..NAV Client>Database>Alter>TAB Collation:
    My settings are:
    - Windows Collation = ON
    - Validate Collation = ON
    - Collation Desc = Dutch
    - Accent Sensitive = ON
    Create dynamic Excel or Word documents, ReportX
  • deV.chdeV.ch Member Posts: 543
    Oh sorry, i meant the dropdown "Collation Description"

    There was the Value: "Afrikaans, Basque, Catalan, Dutch, English, Faeroese, German, Indonesian, Italian, Portuguese[Compatibility]"
    And i changed it to: "Afrikaans, Basque, Catalan, Dutch, English, Faeroese, German, Indonesian, Italian, Portuguese"
    My Blog
  • ajhvdbajhvdb Member Posts: 672
    ok, so my question should be, where does this setting with the extra ....[Compatibility] comes from? Don't have it. (was hoping I could repair my problems with debugging too..)
    Create dynamic Excel or Word documents, ReportX
  • deV.chdeV.ch Member Posts: 543
    The Compatibility Option only appears when your DB is set to this collation, you can't set this collation from wihin nav, only on sql. But it's the wrong option anyway!
    My Blog
  • deV.chdeV.ch Member Posts: 543
    Unfortunatly the problem is here again... i can't believe it... ](*,)

    maybe someone has another idea?
    My Blog
  • SkipieSkipie Member Posts: 3
    Hi !
    had the same error in a similar situation: trying to import SalesLines from a txt file into NAV2009 r2 with sql.
    in my case, i had 2x Inserts on the same line - fixing that helped. where the debugger placed the error was very misleading; this code works without error: 
    WITH SalesLine DO BEGIN
  INIT;
  "Document Type" := "Document Type"::Order;
  "Document No." := DocNo;
  "Line No." := LineNo;
  Type := SalesLine.Type::Item;
  INSERT;
  VALIDATE("No.",TempNumber);
  VALIDATE(Quantity,TempQuantity);
  VALIDATE("Unit Price",TempPrice);
  MODIFY;
END;
LineNo := LineNo + 10000;

    BUT, if there's a second salesline.INSERT somewhere (in my case, integer-dataitem, exectuted after the above), debugger will point to the error on the above INSERT
  • deV.chdeV.ch Member Posts: 543
    Sorry for my late respond, but the issue is not (hopefully) completly resloved.
    What we did, was a native backup (fbk) of the database and once again imported the backup in a fresh nav sql database. I still don't know what exactly caused the problem but it was clearly not code/logic related. There was some sort of inconsitent data in the sql database, still the fact that this is possible in nav fears me a lot!..
    But anyway at least i found a way to fix it and i hope nobody out there has to handle with this kind of problem...
    And if so, i hope they will find this post can follow my steps.
    My Blog
Sign In or Register to comment.