Can some one help me here. When running Navision on SQL it is straight forward to access a SQL table from Navision, when that Table is in on the same server in the same company. But… Can Navision access a table that is in a different SQL database, or preferably on another server? Obviously I mean without using just C/SIDE, and with out having to use C/PLEX. I would not have a problem with C/PLEX, except for the fact that it seems to exist only on paper.
Hi David, You just create a View in the Navision SQL DB that uses a table in another database. Then you create at table in navision C/SIDE with the same name as the view. In the Navision Table: Set then property LinkedObject=True an create the fields you need from the view.
Hi Claus, thanks for the help, it is most appreciated, but it does not address my actual problem, maybe I wasn’t clear enough with the question. I have no problem with accessing SQL tables that are in the same SQL database as the navision server, both common to all companies or unique to one company. Let me explain my problem in more detail, and maybe you can help. I Have two SQL servers, lets call them NAVISION and HISTORY. These exist on two physically seperate machines Say SQL_NAV and SQL_HIST within each of the Servers I have Databases say MyNavision and MyHistory within the Navision Database I have a company “NaviCo Inc.” On the SQL_HIST machine I have a backup of all the data from my old ERP system. Now the company no longer runs the old system, but uses the SQL database to reference any history that was not Dataported across to Navision. Navision on the SQL_NAV machine runs all the companies day to day business. Now what I want to do is within NAVISION Look at a table in the HISTORY database, (say) Customer_File. I could copy the data into a table in the NAVISION server, and then no problems, but how do I tell Navision that the data (Customer_File) is stored on a different database “HISTORY” on a different machine “SQL_HIST”). This data is accessed very rarely at the moment, but there is a lot of it, and I don’t want it in the NAVISION server if there is any way to avoid it. I hope this is clearer, and hope that soemone can assist. Thanks in advance.[:)]
Hi David. In MS SQL You can define a view against a linked server. Do as follows: - In Ent. manager You create a linked server (in the object tree under security). - Declare a view with this SQL statement: “Select * from HISTORY.MyDatabase.dbo.MyCompany$Customer”. The above should give You all customers form the server HISTORY. I haven’t tried using suvch a view from inside Navision, but i can’t see why it sholdn’t work. //Lars
It works perfectly… THANKYOU LARS I love simple solutions.
FYI: You can use this view in Navision as an almost normal table by using the property mentioned earlier along with the LinkedInTransaction property set to No. This is information in the ADG about this stuff.
Actually there is nothing in the ADG about using views, but in reality it makes sense. The only problem I see using views, is that on large tables without SQL keys its seems to be vvveerrrryyyy slow. I had a SQL table with about 10 million records, and looking at it through a Navision table linked to a SQL view was a very tough experience. But I may have been doing something wrong
It is in chapter 4.5 “Linked Objects” and runs to 3 or 4 pages. Yes, a view that large is probably not a good candidate!
[:I][:I][:I] Thanks Robert, you are correct. I always read the manuals before asking silly questions, and reread all of 4.5 three times to make sure, but as always, there it is.
quote:
Access to Objects in Other Databases or on Linked Servers You can access objects outside the current database or server from Navision Attain by linking to an appropriately defined view in the current database. You can create a view definition outside of Navision Attain that accesses data on SQL Server linked servers, which can access heterogeneous data sources.
Can’t believe I missed that. It just shows that the Navision manuals really are a great source of information. Well time to reread the manuals again. (Looks like I need to go back and change my song line vote to “why do I have to keep reading these technical Manuals”).
Hi, Has anyone an solution to speed-up this linked-views ? For me, it seems that everything is transfered to the temp-db before a user can work with this view. It this true ?? Is it possible to create a key on a view ?? Nico
There are several issues: 1. Since a view is (normally) just stored query text, you need to look at the query and optimize it as much as you can. 2. Navision will request as efficient a server cursor as possible for the view, but depending on the view it could give rise to a keyset cursor (result’s key values created in tempdb), or static cursor (all result values created in tempdb). When browsing the data, several such cursors can be created and this can be slow. The result must map to a unique index for an efficient cursor (dynamic or fast-forward) which many views aren’t, so not much you can do with this. There is a setting (see BOL) that causes keyset working sets to deliver results quickly which might help for a keyset cursor). 3. You can create indexs on a view (see Books On Line “Indexed Views”). There are limitations and requirements to how the view should look (and what edition of SQL Server you’re running), but most regular join style views should be satisfied. Effectivley, the view is no longer a normal view but a persisted index, more like a table.
Hi Robert, thanks for the tips. I will try this tips this weekend and will share my results with this community. Thanks Bye Nico
Hi, I think I would try: 1. Create indexed view (it’s possible starting from SQL Server 2000) 2. Use WHERE clause in sql statement.
Moved to SQL Forum…