Deploy SQL View to All Databases

Microsoft Dynamics GPI 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:

  • Delete any existing view with the same name (this allows for an easy redeployment).
  • Create the view.
  • Grant the SELECT permission to DYNGRP.
  • 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 (https://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

    What should we write about next?

    If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

    Your Name

    Your Email

    Suggested Topic

    Suggestion Details

    Looking for support or consultancy with Microsoft Dynamics GP?

    I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.

    1 thought on “Deploy SQL View to All Databases

    Leave a Reply

    Your email address will not be published. Required fields are marked *