Month: May 2019

I did a few posts back in 2016 on finding objects in SQL (a column in all tables, all custom sql objects in a database and trigger in all databases).

I needed to find if a function existed in all databases, so I produced the following script which searches in all Microsoft Dynamics GP databases:

-- drop stored proc if it exists
IF OBJECT_ID (N'usp_AZRCRV_FindFunctionInAllDatabases', N'P') IS NOT NULL
    DROP PROCEDURE usp_AZRCRV_FindFunctionInAllDatabases
GO

-- create stored proc
CREATE PROCEDURE usp_AZRCRV_FindFunctionInAllDatabases
	@Function VARCHAR(50) = 'uf_AZRCRV'
AS
	/*
	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 @command nvarchar(max)

	SELECT @command = 'IF EXISTS ( SELECT 1 FROM SY01500 AS [''Company Master''] INNER JOIN sys.databases [''Sys Databases''] ON [''Company Master''].INTERID = [''Sys Databases''].name WHERE name = ''?'')   
						BEGIN
							USE [?];
							DECLARE @stmt nvarchar(max) DECLARE @n char(1) SET @n = char(10)
							SELECT DB_NAME() AS ''DB_NAME'',t.name FROM sys.objects AS t
							WHERE t.type_desc LIKE ''%FUNCTION%'' 
							AND t.name LIKE '''+ @Function + '%''
						END'

	EXEC sp_MSforeachdb @command
GO

-- grant execute permission on stored proc to DYNGRP
GRANT EXECUTE ON usp_AZRCRV_FindFunctionInAllDatabases TO DYNGRP
GO

-- execute stored proc
EXEC usp_AZRCRV_FindFunctionInAllDatabases 'dgpp'
GO

This post is part of the series on Implementing SmartList Builder from eOne Solutions.

Of all of the ISV products I have used with Microsoft Dynamics GP, SmartList Builder is the one I have worked with the longest, and is the one I like the most (looking back I’m surprised it’s taken me this long to do a series like this).

It is easy to use, flexible and far better at creating new SmartLists than the in-built SmartList Designer product which Micro created when they passed SmartList Builder back to eOne Solutions.

If you like SmartLists, then I would encourage you to take a look at SmartList Builder; it does take a somewhat technical user to create and maintain SmartLists through it, but they are easy to create and much more flexible than those possible through SmartList Designer. The hardest job is sometimes discovering how the data hangs together.

There are resources such as the GP Tables section of Victoria Yudin’s website or my azurecurve|Microsoft Dynamics GP Table Reference which you can use to find the tables you need.

This post is part of the series on Implementing SmartList Builder from eOne Solutions.

The final element to readying SmartList Builder for use is to configure security so that users without the POWERUSER* role can access it. While this step isn’t absolutely necessary, I would encourage people not to over use the POWERUSER* role.

A better approach would be to create a Super User role instead. Both this role and standard user roles will need to have access to the SmartList Builder windows granting.

This is easy to do, as SmartList Builder ships with some default security tasks and a role:

Assign this role to the relevant user or assign the tasks to existing roles and user will, next time they login, be able to access the SmartList Builder window.