We have an inventory system on another SQL server that we want to pull customer information out of NAV for. How would we go about doing this? Would we create a Linked Server object on our other SQL server, query the NAV tables directly or something else?
No comments? Maybe just create a read-only user for the NAV tables and start guessing where the data is? Maybe use a NAV API or something? Anything?! :)
We utilize jobs calling xml ports to create xml files, put them on MSMQ then have biztalk pick them up and process them into the other system
Another thing to watch for with Linked Servers... Sometimes the query coming across the Linked Server may not be processed as efficiently as expected. Seen (and heard) of a few situations where the Optimizer on the target end of the Linked Server may have done an unnecessary scan. Rumored to be less of a problem in SQL2008. Your mileage may vary...