Using Linked servers in SmartList Builder
Wednesday, December 17, 2014If 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.
