Updating data on a external SQL DB

rfernandes

hello all,

i'm using Navision 4 SP1 and i'm using automation successfully to get data from an external SQL DB Table. but I have doubts in the part to make update of data in that table.
i will appreciate some aid in this step.
tanks to all.

Yuri

You can create new table and link it with your external SQL DB Table. Use property LinkedObject for it.

Good Luck!

Marije_Brummel

Yes, the easiest way to do it is to create a view.

Otherwise you may have to think of something to find out what records you already have and what records to update/delete.

Can you explain more about the functionality?

kine

Or you can create SQL query and execute it through ADO Automation...

DenSter

I don't agree with that . I used to agree, but now I don't anymore. I've worked with views and I found them needlessly limited. You have to be db_owner to update, you can't link to an external server unless you have a view into a view, stuff like that. It's too complicated. It's another example of how Navision exposes only parts of external functionality, and when you want to step a milimeter outside of what they exposed you are in big trouble and you're programming workarounds instead of real solutions.

You need to download Miklos' ADO example and use that as a starting point. I have found that with ADO automation you can access external data sources on many platforms, SQL Server, MS Access, Exchange, Excel, as long as that platform allows you to access it using ADO. You can google ADO and get a thousand code examples that you can program into C/AL.

Good luck

Yuri

If you use SQL Server you should perform the following preparation:
Open SQL Server Enterprise Manager.
Add New Linked Server (in 'Security' folder) with the parameters:
General tab ->
Provider name = Microsoft OLE DB Provider for SQL Server,
Data source = your external SQL DB name,
Catalog = database name on your external SQL DB
Security tab ->
Fill in Remote login and password fields

Then create view for your external SQL DB table.
For example:
CREATE VIEW dbo.[company$'linked server name']
AS
SELECT 1field, 2field
FROM

And after this - create a table in your own navision database, LinkedObject property=Yes, LinkedInTransaction=No. Name it like your Linked Server

That is all! :whistle:

Good luck!
_____________
Yuri

DenSter

Exactly my point. You need to link an external server, create a view, make sure it is compatible, create a linked table in Navision, you have 3 things to maintain. With ADO automation all you need to maintain is the code in one object within Navision.

rfernandes

tank you ppl for the replys, but another question.....

how can i associate a value from a navision valiable to the respective SQL variable?