Insert data using SQL-Server stored procedure

datenkultur

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

Marije_Brummel

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... :?

DenSter

Inserting NULL values will do that too.

datenkultur

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.

datenkultur

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 :?

datenkultur

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.

DenSter

Don't forget to modify your SP to do the same thing.

datenkultur

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.