Inserting data in Navision Tables through SQL Server Query

write2sourav

Hi All,
Is it possible to write query in SQL server to insert, modify or delete data from Navision tables. If it is possible, what permissions do the person require in order to successfully execute a query. Plz help as this question was asked by one of our clients.

kine

1) I highly recommend not to insert or modify the data directly in standard tables. You can do that freely in special custom tables created for that. The modifications will not call the application logic within NAV and it can lead to big problems.

2) User under which the data are modified needs to have permissions to insert, modify, delete the data directly on MS SQL. This process is not covered with permissions in NAV. The user must be defined in NAV, else the permission set through SQL tools will be lost after synchronization of permissions in NAV. But this user does not need any role to be assigned to him.

jjanauskas

I agree with kine.

Despite, it is not recommended to change the data directly from SQL, but if you are doing that, just know, that SIFT tables are automatically updated at SQL level (using triggers), so you should not have problems with that. Of course, internal Navision triggers are not executed.

In order to be able to change the data directly from SQL, you just simply need the sql user with certain permissions to change the table. You can create/setup such user using SQL Enterprise Manager. No setup is needed at navision side for such user.

davmac1

One earlier tip I recommend - alter database - integration tab to maintain defaults. Avoids inserting null values from SQL inserts.

Maintain defaults: This setting determines whether or not SQL Server will create and maintain default constraints for each field in a Navision table. If you select this option, external tools can use the defaults when inserting data into or modifying data in Navision tables.

kine

jjanauskas wrote:

No setup is needed at navision side for such user.

Setup is needed, else the permissions will be lost when some other user is inserted or changed in Navision. You need to add the user into Navi without any role. It is enough to prevent Navision from droping permissions for this user. Navision drops permissions for all users which have access to the Navision DB but are not DB_OWNER or another system role and have not account in Navision.

Alex_Chow

write2sourav wrote:

Hi All,
Is it possible to write query in SQL server to insert, modify or delete data from Navision tables. If it is possible, what permissions do the person require in order to successfully execute a query. Plz help as this question was asked by one of our clients.

Don't do it unless you like to have a corrupt database.

Technicals aside, it's also not recommended business wise since there's no good audit trail of what was changed.

I wouldn't just say it's not recommended, I'd say that you should not. Period.

DenSter

I would qualify that to include this warning only for existing base NAV tables. If you have staging tables that you populate for a specific purpose, and you have a NAV process that processes this information into base tables, then it is perfectly fine to do this, as long as you consider all the things that have been said here.

Modifying existing data in base NAV tables is a no, don't start on it, it only leads to trouble.

bbrown

SIFT tables are automatically updated at SQL level (using triggers)

Keep in mind that any bulk inserts will not fire triggers.

If you must update Navision from SQL one option to consider is using the Message Queue and NAS.

Waldo

Indeed.

That's most commonly used.

1) Insert your data into a "temp" table
2) start a NAS process that interpretes this data and insert it into the right standard tables into navision

Why? NAS executes NAV's business logic, SQL Query does not.

bougeac

Hi, I had MAJOR problems trying to insert/update data from sqlserver into a navision 3.70 database. I was using the linked server logic from within sqlserver and using the C/ODBC driver to talk to navision.

Pulling data OUT of the navision db using the OPEN_QUERY built in within sqlserver 2000 was no problem (although rather slow). However, pushing the data into NAVISION using the same route just wasnt consistent and was very buggy.

The solution is very very simple, just use a dataport from within navision to import the required data straight into your navision table.

What you could do perhaps is simply write you sqlserver data to a txt file then use this to drive your import into navision using the dataport.

it works very well for exporting data also.

Hope this helps,

chris

Chowdary

jjanauskas wrote:

I agree with kine.

Despite, it is not recommended to change the data directly from SQL, but if you are doing that, just know, that SIFT tables are automatically updated at SQL level (using triggers), so you should not have problems with that. Of course, internal Navision triggers are not executed.

In order to be able to change the data directly from SQL, you just simply need the sql user with certain permissions to change the table. You can create/setup such user using SQL Enterprise Manager. No setup is needed at navision side for such user.

Hi,

Is there any way executing Navision triggers when data is modified from SQL Query

Thank You