Linked Server and Navision

ref
ref Member Posts: 20
edited 2007-09-23 in NAV/Navision Classic Client
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.
/René

Comments

  • David_Singleton
    David_Singleton Member Posts: 5,479
    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
    David Singleton
  • ref
    ref Member Posts: 20
    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.
    /René
  • ara3n
    ara3n Member Posts: 9,258
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • ref
    ref Member Posts: 20
    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.
    /René
  • Truby
    Truby Member Posts: 5
    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 :)
    Truby Gravatt
    Knutsford Enterprises Ltd
  • sbranin1
    sbranin1 Member Posts: 8
    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
    David_Singleton Member Posts: 5,479
    Can you see the data if you create a page, or if you run the table direct from the Object designer?
    David Singleton
  • sbranin1
    sbranin1 Member Posts: 8
    Yes, I can see the data if I create a Page or a Query or run the Table directly from Object Designer.
  • sbranin1
    sbranin1 Member Posts: 8
    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
    mtlmonk Member Posts: 48
    Any update on this? Having the same issue

Categories