Options

Insert data using SQL-Server stored procedure

datenkulturdatenkultur Member Posts: 14
hi all,

I created an interface for Dynamics NAV 2009 SP1. A stored procedure transfers data from one table to a NAV table. I can read and modify the values inside NAV without any errors. This far, this good.

Now I tried to do a NAV internal backup. Unfortunately the backup procedure exits with an error. It tells me that my table is buggy (I don't have the exact text at the moment). It also tells me, that this can happen if external procedures change something inside the table.

What is the reason for this message? What can I do to solve the error?

Thanks in advance

Andreas
Customizing + Training + Workshops + Development
Navision / Jet Reports / dkImport / dkSchema / COM /.NET

http://www.datenkultur.de

Answers

  • Options
    Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    When you change data directly via SQL Server the NAV metadata is not respected.

    For example, NAV has two datatypes Text and Code. Both are VarChar on SQL Server and technicaly accept uppercase and lowercase, however NAV metadata knows Code is uppercase only and does not allow lowercase in this field.

    In your case you have probably data in the table that matches SQL metadata but does not match NAV metadata.

    Good luck... :?
  • Options
    DenSterDenSter Member Posts: 8,304
    Inserting NULL values will do that too.
  • Options
    datenkulturdatenkultur Member Posts: 14
    Thanks for your fast response. I'll have a look at the values.

    @Mark: Thanks for your book. I read it some month ago. I learned a lot. You asked for suggestions for new books. What I need: Common Functions. Developing NAV is often searching the standard code. After some years of developing you'll know a lot, but starting with NAV is heavy. I made some internal function documentation the last year. I grouped the functions into categorys (Finance, Sales, Production, ...). I can remeber the functions faster and new colleagues will learn NAV faster. I think about somethink like a NAV function dictionary.
    Customizing + Training + Workshops + Development
    Navision / Jet Reports / dkImport / dkSchema / COM /.NET

    http://www.datenkultur.de
  • Options
    datenkulturdatenkultur Member Posts: 14
    I created the error again:

    Caution: There is an error in the database structure. This occurs if the database file is changed by another program or if a device driver does not function properly.

    You must correct this error before you can continue. The error (1190 in module 19) may have been caused by the computer or a program.

    Table: EOS Interface Header
    Company:
    Key Fields: Document Type

    Check the database. On the File menu, click Database, click Test, and then click Maximum. If you get the same error, you should:

    1. Back up the database by copying the database file or files to another location.

    2. Use the built-in functionality to make a backup.

    3. If this works, create a new database without deleting the database that contains the error or errors.Restore the backup that you made with the built-in backup functionality into the new database. To test it, on the File menu, click Database, click Test, and then click Maximum.

    If this procedure does not work, then any database changes that were made after the most recent backup will be lost. Restore the most recent backup that was made with the built-in backup functionality and test it.
    For security reasons, you should save the old database until you have used the new one for a period of time.

    Contact your system administrator for assistance.



    I am the system administrator, unfortunately :?
    Customizing + Training + Workshops + Development
    Navision / Jet Reports / dkImport / dkSchema / COM /.NET

    http://www.datenkultur.de
  • Options
    datenkulturdatenkultur Member Posts: 14
    The mainproblem: I inserted lowercase text into a code field. I changed it into Text.

    An additional problem: NAV does not convert implicit if you copy text values into a code field by C/AL. So you have to clear leading and following spaces and you have to use only upper case letter.

    Use the following statement to convert Text into code:
    "Code Field" := UPPERCASE(DELCHR("Text Field",'<>',''));
    

    Many thanks for your help.
    Customizing + Training + Workshops + Development
    Navision / Jet Reports / dkImport / dkSchema / COM /.NET

    http://www.datenkultur.de
  • Options
    DenSterDenSter Member Posts: 8,304
    Don't forget to modify your SP to do the same thing.
  • Options
    datenkulturdatenkultur Member Posts: 14
    Thanks for your suggestion.

    Normaly you are totaly right. In this case we like to see the inserted original value as well. We copy all values into corresponding fields. So we only insert defined empty values instead of NULL inside our stored procedure.
    Customizing + Training + Workshops + Development
    Navision / Jet Reports / dkImport / dkSchema / COM /.NET

    http://www.datenkultur.de
Sign In or Register to comment.