SQL Error: 7405 & GPS Error: 58 When trying to create a Smartlist comprising tables / Views from other SQL Database server

(0) Share

Report

Posted on by Srinivasan Sundaram

765

I am trying to create a smartlist referencing a table from another SQL Database. I have created a database link using Management studio to SQL Database residing in another server. Using the database link, I have created a view within DYNAMICS database referencing the tables from the other SQL Database. The view is accessible without any issues within the management studio. I have granted "SELECT" access privelege on View to the DYNSA user and did all that is required SQL Table security within the GP Application as well. The created view is visible for selection within Smartlist "Add SQL Table" window used for selecting the tables. When I add the View and try to add a key field, the system throws an error message as listed below -:

GPS Error: 58
SQL Error: 7405 [Microsoft][SQL Server Native Client 10.0][SQL Server]Heterogeneous queries require the ANSI_NULLS 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

To address the error, I have set/ enabled the ANSI_NULLS and ANSI_WARNINGS to "ON" at the database levl on the both the databases and added them to the create view statements. Still it is continuing to give this error. I have tried some of the resolutions suggested in few forums and it DOES NOT help either. Has anyone been successful in resolving it and building a smartlist referencing tables from other databases. Please share the information.

Thanks

Srini

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

Srini,

Did you checked also the ODBC connection setttings from the GP client that will use that smartlist ? Those are normaly not checked by default in the GP client setup... Maybe not related, but worth a try...

Was this reply helpful? Yes No
Srinivasan Sundaram 765 on at

Like (0)

Report

Beat:

Thanks for your suggestion. I have set the ANSI NULLS, PADDINGS and WARNINGS on ALL ODBC connections strings and have tried out. It just doesn't work.

Srini

Was this reply helpful? Yes No
Suggested answer

Amit Thakkar on at

Like (0)

Report

While creating smart list using View Error in Smart list Builder

GPS Error: 58
SQL Error: 7405 [Microsoft][SQL Native Client][SQL Server]Heterogeneous queries require the ANSI_NULLS 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

Was this reply helpful? Yes No
Suggested answer

Almas Mahfooz 11,009 User Group Leader on at

Like (0)

Report

I have done this with stored procedures, don't think it will work with views as views don't create with SET ANSI_WARNINGS ON and SET ANSI_NULLS ON inside the view.

So some trick needs here, and I have found one for you.

http://www.aaronberquist.com/2011/07/using-smartlist-builder-with-a-linked-server-it-can-be-done/

Try it and let us know. :)

Was this reply helpful? Yes No
Amit Thakkar on at

Like (0)

Report

Thanks Almas

I have tried above solution of link but it doesnt work. But sure the link server is the catch it is not allowing to add column in smart list. May be this needs to change the view but other server data not available in local server.

Let me know, if you find any solution

Was this reply helpful? Yes No
Greg Wahl 20 on at

Like (0)

Report

Has anyone come up with a solution to this problem?

Was this reply helpful? Yes No
Suggested answer

Almas Mahfooz 11,009 User Group Leader on at

Like (0)

Report

Oboro3, did you try the method explained in the below mentioned link?

http://www.aaronberquist.com/2011/07/using-smartlist-builder-with-a-linked-server-it-can-be-done/

Another method is (a length one ) to create a table in your GP database and insert records each time in your GP table when it get inserted in Non GP Database table by applying a insert trigger on the table through a link server.

Almas

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report

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.

Was this reply helpful? Yes No