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.