We are looking to deploy SmartList Builder + SmartView as an enhanced reporting solution for our internal customers. One of the things I have seen floating around the net is that you can’t use SmartList Builder with a SQL Linked Server. In fact, a number of Dynamics MVP’s alluded to this in a support thread at http://www.ms-news.net/f1610/smartlist-builder-odbc-error-8692687.html. The actual error message will look something like this:
GPS Error 58
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
I’ll show a little further into the article that yes, you can work around this, and pretty easily too – it just takes a little sleight-of-hand with your SmartLists.
A little background – we have 150+ Dynamics GP companies, all on one database server. We also frequently have requests to produce reports which consolidate data from all of these entities onto one SmartList so that, in one report, users can quickly see data from every entity. Up until now, we have been using the technique I outlined here to create reports which use a linked server to execute a stored procedure inside a view using OPENQUERY. In order to make this work, I had to come up with a workaround to the very ANSI_NULLs issue other people have reported as stopping them in their tracks.
The secret boils down to this – you have to trick AnyView, or SmartList Builder into accepting the view the first time you add it. Once you do that, and build your SmartList, you can change what the view is actually doing after the fact. I know that it works with AnyView, and have done a test with SmartList builder and it works there as well.
For the steps below, assume the final query you want to use looks something like the code below. Column1 is varchar(5) and Column2 is TINYINT:
select Column1, Column2 from LINKEDSERVER.SAMPLEDB.dbo.SAMPLETABLE |
Here are the steps you need to follow:
Create a SQL view with the columns and field lengths that match the “final” report you want to produce:
CREATE VIEW SampleView AS SELECT convert(varchar(5),'') as Column1, convert(tinyint,0) as Column2 GO GRANT SELECT ON SampleView TO DYNGRP |
Add that view to SmartList Builder / AnyView and build your SmartList off that view.
Once you have successfully built the object, ALTER the view to call the code from the linked server:
ALTER VIEW SampleView AS --SELECT convert(varchar(5),'') as Column1, convert(tinyint,0) as Column2 select Column1, Column2 from LINKEDSERVER.SAMPLEDB.dbo.SAMPLETABLE |
Voila! Your SmartList will run the linked server code, with no ANSI_NULL warnings!
I have used this approach for most of the SmartLists we have in Production today, with great success. Of course, using a linked server is not, in the end, an ideal solution, but for cases when you have to, the method above will allow you to use SmartLists with SQL Linked Servers successfully.
Note: I have updated the sample code above to include the “GRANT SELECT” permissions to “DYNGRP” on the view. If you don’t do this, SmartView will spin forever on the “Loading…” screen when you try to run a SmartList.
[…] the article here: Using SmartList Builder With A Linked Server – It Can Be Done! VN:F [1.9.10_1130]Please wait while your rating is cast…Rating: 0.0/10 (0 votes cast)VN:F […]
Nice, Aaron – we’ve run into this several times and always seemed to come up short. I’ll pass this on to our other consultants and be sure to give this a try the next time it comes up.
Thanks Andy – let me know how it goes for you guys. I love to hear that someone is actually making use of some of these tips!
Aaron
[…] Comments 0 Aaron Berquist demonstrates Using SmartList Builder With A Linked Server – It Can Be Done! […]
Hi Aaron,
I tried to follow your steps to set up the SmartList with a Linked Server. I got the initial error message, changed the view to be generic, created the SmartList and proved that it worked with the generic view. When I changed the SQL view to use the linked Server, I get no results in my SmartList. I can see results from the view in my SQL Query window, so I know that the Linked Server view returns values, but can’t get SmartList to display them. I am logged in as ‘sa’ in both SQL and GP, so would not expect security to be an issue…Any suggestions?
Thanks
Hi Jerry,
When you created your view, did you GRANT SELECT ON DYNGRP to the view? I found that if I did not perform that step, SmartVieq would not display any results.
Are you using SmartList Builder to create the object? And are you using SmartList or SmartView to access it?
Hi Aaron,
I have tried granting access to DYNGRP and still have the same issue. I have used SmartList Builder to create the ‘report’ and SmartList to view it. I can see the results in SmartList when the SQL view is generic (that is looking at a local table), so its only when I change the view to make use of the Linked Server that SmartList stops giving me results.
Thanks
When you set up the linked server, what Driver did you use? I use the SQL Native Client 10.0 driver. Is that the one you’re using? Maybe that has something to do with it. are you sure the generic and linked server result sets are the same in terms of field definitions?
I originally set up the Linked Server using Microsoft OLE DB Provider for SQL server. I deleted that Linked Server and created a new one using the SQL Native Client 10.0 driver. I refreshed the View using the linked server and was able to select on it and get data back. I re-created the SmartList and am still not getting anything back. hmmmm. Yes, the field definitions are the same – my generic view is looking at a historical version of the same database as is on the linked server.
Hmm indeed.
I assume your currenct view calls the linked server this way:
SELECT * FROM LINKEDSERVER.LINKEDDB.DBO.LINKEDTABLE
Try changing your view to call the linked server this way:
SELECT * FROM OPENQUERY(LINKEDSERVER, ‘SELECT * FROM LINKEDDB.DBO.LINKEDTABLE’) AS derivedtbl_1
Hi Aaron,
Do you have any other suggestions on how to get the linked server data to show in a SmartList created with SmartList Builder?
I’ve used your suggestions to create a linked server query for Excel Report Builder and I get data. It works very well!
But the same query in SmartList Builder produces no results when the linked server columns are included. No errors just no results.
Any further suggestions?
I too am having the same issue as JerryB, created the SmartList using a “dummy” view with the same datatypes. Replaced the View with the linked server select and the SmartList in GP shows no data. If I query the view directly I get data returned. Select is Granted to DYNGRP. This linked server happens to be connecting to DB2 so I also explicitly converted the data to sql server data types in the view thinking it was a type conversion issue.
Anyone else come up with a solution?
Thanks,
Jeremie
I really had high hopes for this but it really doesn’t work. My only guess is that there are decencies on the SQL server (2005 vs 2008) or maybe how the linked server connection is set up
I’m having the same issues as everyone mentioned above. It’s been two years since the last post. Has anyone figured this out or has everyone just moved on?
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
SQL view use Link server.
While creating smart list getting error
Above method
Any update on solution to this problem?
I tried all of the above with no success so I created a table in the company database with SELECT permission to DYNGRP then setup a SQL Job with a Truncate then Insert statement that pulled the data from the linked server into this company table (6 seconds to complete insert). Scheduled it every hour during work hours. All is working well in Smartlist (Smartlist Builder). We are a small company so the data transfer uses very little of the servers resource.
[…] Aaron Berquist demonstrates Using SmartList Builder With A Linked Server – It Can Be Done! […]