Find SQL Function In All Databases

Microsoft SQL ServerI 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

Management Reporter Error: An Error Occurred While Configuring ‘Management Reporter Application Service’ Part 4 – Log Space

Microsoft Dynamics GPAs mentioned in the last post, I’ve been having some fun with Management Reporter recently. Unfortunately, Integration Manager the errors it produces are often less than useful. After resolving the issues with the schema owner, SQL Timeout and SQL Login, the attempt to upgrade Management Reporter produced another generic error message:

Management Reporter 2012: An error occurred while configuring 'Management Reporter Application Service'. Check the deployment log for details.

Management Reporter 2012

An error occurred while configuring 'Management Reporter Application Service'. Check the deployment log for details.

Continue reading “Management Reporter Error: An Error Occurred While Configuring ‘Management Reporter Application Service’ Part 4 – Log Space”