Keep up to date with the latest information about Microsfot Dynamics GP through articles from the Perfect Image and azurecurve blog.
Deploy SQL View to All Databases
May 22nd, 2017 by
I have a few clients who have quite a few company databases in Microsoft Dynamics GP. One of them has well over a hundred live companies. This can make deploying reports somewhat long winded when you need to deploy an SQL view to all of the databases.
Fortunately, Microsoft SQL Server has ways and means which you can use to make the process a lot easier. In this case, I am using a SQL cursor to select all of the databases from the Company Master (SY01500) and loop through them to deploy the view; the deployment is in three phases:
The script is posted below with a simplified PO report being created; the view name is set in the highlighted parameter near the top of the script.
The large highlighted section is where you please the content of the view which is to be deployed.
/* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */ DECLARE @IntercompanyID AS VARCHAR(5) DECLARE @SQLStatement AS VARCHAR(8000) DECLARE @SQLViewName AS VARCHAR(100) = 'uv_AZRCRV_POReport' DECLARE cursor_InterID Cursor FOR SELECT INTERID FROM SY01500 INNER JOIN master..sysdatabases ON name = INTERID OPEN cursor_InterID FETCH NEXT FROM cursor_InterID INTO @IntercompanyID WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) -- drop view if it exists SET @SQLStatement = 'EXEC ' + @IntercompanyID + '..sp_executesql N''IF OBJECT_ID (N''''' + @SQLViewName + ''''', N''''V'''') IS NOT NULL DROP VIEW ' + @SQLViewName + '''' EXEC (@SQLStatement) -- create view SET @SQLStatement = 'EXEC ' + @IntercompanyID + '..sp_executesql N''CREATE VIEW ' + @SQLViewName + ' AS SELECT DB_NAME() AS DataBaseName ,SY.CMPNYNAM ,PONUMBER ,CASE WHEN POSTATUS = 1 THEN ''''New'''' WHEN POSTATUS = 2 THEN ''''Released'''' WHEN POSTATUS = 3 THEN ''''Change Order'''' WHEN POSTATUS = 4 THEN ''''Received'''' WHEN POSTATUS = 5 THEN ''''Closed'''' WHEN POSTATUS = 6 THEN ''''Cancelled'''' ELSE ''''Unknown'''' END AS POSTATUS ,CASE WHEN POTYPE = 1 THEN ''''Standard'''' WHEN POTYPE = 2 THEN ''''Drop Ship'''' WHEN POTYPE = 3 THEN ''''Blanket'''' WHEN POTYPE = 4 THEN ''''Blanket Drop Ship'''' ELSE ''''Unknown'''' END AS POTYPE ,DOCDATE ,SUBTOTAL ,REMSUBTO ,VENDORID ,VENDNAME FROM POP10100 WITH (NOLOCK) INNER JOIN ' + DB_NAME() + '..SY01500 AS SY WITH (NOLOCK) ON INTERID = DB_NAME()''' EXEC (@SQLStatement) -- grant select permissions to DYNGRP SET @SQLStatement = 'EXEC ' + @IntercompanyID + '..sp_executesql N''GRANT SELECT ON ' + @SQLViewName + ' TO DYNGRP''' EXEC (@SQLStatement) FETCH NEXT FROM cursor_InterID INTO @IntercompanyID END CLOSE cursor_InterID DEALLOCATE cursor_InterID GO