Due to scheduled maintenance, some areas of our site are currently inaccessible. Thank you for your patience.

Please reach out to sales@eonesolutions.com with any questions.

Back

Using Linked servers in SmartList Builder

Published: Dec 17, 2014
Post Author Written by David Youngquist

If you try to use a linked server in SmartList builder you will see that you cannot access any databases from the linked server.  If you have a SQL view that references a linked server you will receive an error saying:

SQL Error 7405 ODBC SQL Server Driver Heterogeneous queries require the ANSI_NULL and ANSI_Warnings options to be set for the connection.
This ensures consistent query semantics. Enable these options and then reissue your query. ODBC error 37000

Linked Servers do not work within SmartList Builder. The reason for this is the way that Dynamics GP is retrieves SQL data. It sets multiple parameters in every Dexterity script that is run, these parameters do not permit you to call data from other SQL servers.
The workaround would be create the databases on your Dynamics GP server and use SQL replication to keep the database up to date from the data on your other server. Then build your SmartList based on the replicated database on the Dynamics GP server.

Feeling stuck? Get the support and guidance you need to help you power through any data challenge

We're on your integration team. Connect with our people and let us know how we can help you.