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 (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.
1 thought on “Deploy SQL View to All Databases”