Aaron Berquist Rotating Header Image


Using SmartList Builder With A Linked Server – It Can Be Done!

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:

SELECT convert(varchar(5),'') as Column1, convert(tinyint,0) as Column2

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:

--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.

Post to Twitter

Easily Add New Databases to Global Dynamics GP Reports

Last post I explained how using the OPENQUERY SQL syntax can help skirt the 260 tables in a view limit of SQL Server. Today I’ll show how to combine that tip with additional logic to automatically add new Dynamics GP company databases to your custom global reports.

If you have a large number of global reports, or even if you only have a few, maintenance can be a hassle whenever a new Dynamics GP company is added to your system. I have devised a solution to this maintenance headache by combining a custom table which holds the name and code for my global reports, with a nightly procedure which will rebuild all of the global objects automatically. The items that make up the solution are:

1. A custom table, stored in DYNAMICS, with the following structure:

  • Report ID – a unique identifier for the report
  • Report Name – the name of the SQL object
  • Report Code – the report code I want to run against every Dynamics GP database
  • Report Type – whether the SQL object to build is a stored procedure or a view

An example of what a record in the table would look like is:

Report ID Report Name Report Code Report Type
1 usp_NonVoidedSOPDocs Select * from REPLACEME..SOP30200 where VOIDSTTS = 0 Stored Procedure

Note the “REPLACEME” line in the “Report Code” field. This value will be replaced with the database name when the item below is run.

2. A stored procedure, also stored in DYNAMICS, which is called every night from a SQL Job. This procedure does the following for each entry in the table above:

  • Gets a list of database from the SY01500 table in DYNAMICS. The field it returns is INTERID.
  • Dynamically builds a SQL statement which will DROP the existing SQL object, based on the Report Name and Report Type values from the table.
  • Replaces the value REPLACEME in the Report Code string with the INTERID value.
  • Dynamically builds a SQL statement to CREATE the SQL object, based on the Report name and Report Type values from the table.
  • Grants EXECUTE permissions to DYNGRP to the SQL Object.

As an example, thejob above would dynamically generate, then execute, the SQL String below:



IF EXISTS (SELECT name from sysobjects where name  = ‘usp_NonVoidedSOPDocs’)













By using this technique, new Dynamics GP company databases can be automatically added to your custom reports without any intervention on your part – freeing you up to work on other tasks.

Next week, I plan to talk about why I am such a fan of the Field Service module in Dynamics GP and how the way it has been designed is such a boon to analysts and developers who need to customize it’s behaviour.

Post to Twitter

Creating Global Dynamics GP SmartLists with OPENQUERY

From time to time, I have needed to create a SmartList which pulls in data from multiple Dynamics GP company databases, with multiple tables. Because of the number of databases involved, and the number of tables, I hit the “260 tables in a view” limit of SQL Server. Because of the specific needs of this particular business unit, the report needed to be a SmartList, and I could not deliver it via Reporting Services. 

After trying a number of different approaches, I stumbled upon using the SQL command OPENQUERY to call a stored procedure which obviously is not encumbered with the 260 table limit. 

The steps I took to utilize this feature are as follows: 

  1. Create a linked server in SQL which pointed back to itself. In my case, I named the linked server “SMARTLIST”
  2. Write the view in a single database.
  3. Deploy the view against all of the databases in Dynamics GP.
  4. Create a stored procedure in one database which unioned all of the views together.
  5. Create a view in one database which called the stored procedure using OPENQUERY, like so: SELECT * FROM OPENQUERY(SMARTLIST, ‘exec MyProcedure’) AS derivedtbl_1 
  6. Build a SmartList off of the view in question.

Voila! Now I have a view I can use in my SmartList which gets around the 260 table limit in SQL Server.

In an ideal world, this report would be delivered via another mechanism, such as Reporting Services, or a consolidated reporting server. However, sometimes a less-than-ideal solution is needed and this is one trick to keep in mind should your design constraints dictate going with a SQL View.

Next week I’ll expand on this item and show how you can dynamically generate a view which will automatically add new Dynamics GP company databases to your global reports.

Post to Twitter