Linked object and SQL View

rocatis · 2008-12-30

I'm trying to do some object linking (NAV 5.01, SQL 2005) and I'm not doing very well...

Setup:

1) SQL Server A has a table and a view belonging to another (non-NAV) application.
2) SQL Server B (which is running the NAV database) has a (non-NAV) view which references the view on Server A. This works (i.e. opening the view on Server A shows me the data on Server

.

The problem I'm facing is accessing the data from within NAV. I simply cannot figure out how to create a table that links to the view - and judging by the skimpy documentation I've been able to dig up it should be possible to do (it just doesn't say how...)

Anybody got a clue? Am I trying to do the impossible?

garak · 2008-12-30

Do you get some error messages? Is the view for one company or for all compaies?

Mean:
for every company one view -> the view must have the name Companyname$Viewname
in NAV the "table" with Porperty LinkedObject YES must have DataPerCompany YES and the Name = ViewName

is the View for all companies the same, the Name of the View is without the Companyname$.
The NAV "Table" has the property DataPerCompany = No and LinkedObject.

Benno · 2008-12-30

Some issues to keep in mind:
1. The view must be in the same database as Navision.
2. The Navision table must have the same name as the view
3. In the table property you must have to set LinkObject to yes
4. Delete the Navision table from SQL Server if the table still exists
5. Set LinkedInTransaction to No if the view connects to a database outside the Navision database

As mentioned in my topic (viewtopic.php?f=23&t=30763&p=149755#p149755) I have to set the SQL Server permissions of the user to db_owner to have this all work but I want the minize the permissions as low as possible. This must be by setting the quest account but this doesn't work for me.

rocatis · 2008-12-30

It turned out to be a rookie mistake: I had only defined the View for the company in which I am going to use it. No can do. The View needs to be defined for each company.

Thanks for your help!

garak · 2008-12-30

no prob and welcome

Waldo · 2008-12-30

I couldn't resist 8) :
How to display an SQL Server View in Microsoft Dynamics NAV

rocatis · 2008-12-30

Waldo wrote:

I couldn't resist 8) :
How to display an SQL Server View in Microsoft Dynamics NAV

I actually found your blog while googling, and it's very nice. However, it also fails to mention that you need to create the view for each company in the database :?

ara3n · 2008-12-31

There is a property for the table called. DataPerCompany. If you set it to no, NAV will not add the prefix of company Name, So you only need one view.

Waldo · 2008-12-31

rocatis wrote:

Waldo wrote:

I couldn't resist 8) :
How to display an SQL Server View in Microsoft Dynamics NAV

I actually found your blog while googling, and it's very nice. However, it also fails to mention that you need to create the view for each company in the database :?

Well, I did mention it shortly as "If you would like this view to work for each company, you have to create a separate view for each company".

anyway, when you're compiling in one company, it's only going to check if the view exists in that company. If you're USING the view in another company, it's obvious you have to foresee that seperate view as well... .

rocatis · 2009-01-01

Waldo wrote:

rocatis wrote:

Waldo wrote:

I couldn't resist 8) :
How to display an SQL Server View in Microsoft Dynamics NAV

I actually found your blog while googling, and it's very nice. However, it also fails to mention that you need to create the view for each company in the database :?

Well, I did mention it shortly as "If you would like this view to work for each company, you have to create a separate view for each company".

Well, thing is, it's not an option, it's a requirement.

Linked object and SQL View

Comments

Categories