Linked Server and Navision

ref

Our customer has an external SQL server on which they want to make a lookup into a table.

First of all I know (and it is working) that a view can be created on the Navision SQL server and I can access this view from the Object Designer (table).

I have also managed to setup a Linked server and retrieve data from the linked server. Example:

EXEC sp_addlinkedserver
@server = 'NPRSQL',
@srvproduct = 'SQLServer OLEDB Provider',
@provider = 'SQLOLEDB',
@datasrc = 'SQL01'
Go

SELECT *
FROM NPRSQL.DTS.dbo.ItemView

But the only way I can view this data inside Navision is via creating a View in the database. However I can’t figure out how to create this view.

David_Singleton

These threads can possibly help:

Dynamics User Group - SQL - Accessing ext. database AKA "Using C/PLEX".

Dynamics User Group - Linked Server Performance

Dynamics User Group - Linking a foreign table to a Navision table in SQL 2005

Dynamics User Group - [SOLVED] SQL user in linked table

Dynamics User Group - Slow view in linked SQL server

ref

Thanks a lot your comments proved that I was on the right track.

However it seems to be another problem. I can “create” the view, get the data, but when I try to same the view I get the following error:

ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation could
not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a
distributed transaction.[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB
provider returned message: En ny transaktion kan ikke tilmeldes i den angivne
transaktionskoordinator. ]

I have Googled a bit and it seems others have gotten this error but no one has solving solution.

ara3n

the reason you are getting this is because Navision assumes that you are going to modify the linked table. If you are just reading it. I would suggest to write the records at the beginning of your transaction into a temp table and then do a commit.

The other option is to do a commit before and after reading through the records.

BTW distributed transaction is used for rollback.

Also reading the view into a temp table improves performance.

ref

I am not sure I get this… until now I would say that I only have been working in SQL. I have not come to the point where I can start to use my work inside Navision.

Linked Server
On the linked server I have made a view with the following content:

SELECT dbo.NavDB$Item.No_ AS [No], dbo.NavDB$Item.Description, dbo.NavDB$Item.[Unit Price], SUM(dbo.[NavDB$Item Ledger Entry].Quantity)
AS Quantity
FROM dbo.NavDB$Item INNER JOIN
dbo.[NavDB$Item Ledger Entry] ON dbo.NavDB$Item.No_ = dbo.[NavDB$Item Ledger Entry].[Item No_]
GROUP BY dbo.NavDB$Item.No_, dbo.NavDB$Item.Description, dbo.NavDB$Item.[Unit Price]

This is just an example it could be whatever view.

Production Server
On my server (NavProdDB) I am running another Navision (same version 3.70). I also sat up a linked server which contains the view I want to use on NavProdDB. On this server I want to create a view which I can use inside Navision running on NavProdDB.

I then try to create the following view:

SELECT *
FROM NPRSQL.DTS.dbo.ItemView ItemView

But when I try to save this view I get the error mentioned above.

I have tried to make a similarly scenario on the Northwind database (create a view and the “call” this view via a view on NavProdDB with the same result. So this must be a pure SQL issue.

Truby

Looks like you have a collation conflict.

when you do your view, add in a collation constraint.
ie so the returned data is in a certain collation.

i cant read the error message, so this is a bit of a guess

sbranin1

In NAV 2013, I have set up a linked table to a view. I have LinkedObject = YES and LinkedInTransaction = NO. I only want to read data from the view. I can run the table from the Development Environment and see the data. I just can't figure a way to read the data from code. When I try, I get "The following SQL error was unexpected. The operation could not be performed because OLE DB provider "xxxxxxx" for linked server "yyyyyyyy" was unable to begin a distributed transaction."

This code is what I am using. I only added the COMMITs to see that they would not help.
COMMIT;
DealerTrx.GET(216123126) ;
MESSAGE('Variant %1', DealerTrx.VariantID);
COMMIT;

ara3n suggested reading into a temp table but I can't figure out how to do this if I can't read the records.

Any suggestions?

David_Singleton

Can you see the data if you create a page, or if you run the table direct from the Object designer?

sbranin1

Yes, I can see the data if I create a Page or a Query or run the Table directly from Object Designer.

sbranin1

If I run the table directly and then try to edit the data, I get the same error. But I don't need to be able to do this. I just need to read it with code.

mtlmonk

Any update on this? Having the same issue