MSDTC XACT_ABORT - how to stop distributed transctions from starting

SteveKnott

Hi,

Any way of stopping a distributed transaction from starting when reading from Linked server via Linked object ? What is differnet from running the table.. vs access in code ?

I can run my view fine... from NAV.. but cannot access in code - same error as if I try and edit the view... which is acceptable - I only need read access.

GET , FINDSET , FINDFIRSET all try ans start a transaction .. which fails.. LinkedIntransaction is No..

I can't setup MSTDC as the SQL is clustered and I don't have access - the Network guys are not helping.

Any thoughts ?
Thanks,
Steve

parm

Hi,
I think you can't.
Maybe you can test a different approach.
Since the linked server is only used to read, try to create a store procedure or DTS job to bring the data from linked server to Nav database.
Instead of using a view in NAV database, use a table and a job in sql to populate that table.

Using the linked server with a view in NAV have this issue. If linked server is down for maintenance NAV will fail (linked server down).
With a table in NAV that is populated this will not happen. If the linked server is down the job doesn't run.

Think about the number of rows to copy from linked server to NAV table to find the best solution to copy that rows.

regards and good luck,
parm

SteveKnott

Thanks for the suggestion.. maybe will have to try that... just its about 25 views that I have all working... and this only happened now we migrated to a SQL Cluster.

Other thing I see is that you can have "ExteranalSQL" as a table type now from NAV2016... wonder if that has the same "ditstrib" requirements... I hope not... but needs some code chnages to register the connections etc... before I do that.