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.
