Invalid objects in DNN SQL database #5552
Comments
|
Find Invalid Objects in Your Databases SET NOCOUNT ON;
IF OBJECT_ID('tempdb.dbo.#objects') IS NOT NULL
DROP TABLE #objects
CREATE TABLE #objects (
obj_id INT PRIMARY KEY
, obj_name NVARCHAR(1000)
, err_message NVARCHAR(3000) NOT NULL
, obj_type CHAR(2) NOT NULL
)
INSERT INTO #objects (obj_id, obj_name, err_message, obj_type)
SELECT
t.referencing_id
, obj_name = QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name)
, 'Invalid object name ''' + t.obj_name + ''''
, o.[type]
FROM (
SELECT
d.referencing_id
, obj_name = MAX(COALESCE(d.referenced_database_name + '.', '')
+ COALESCE(d.referenced_schema_name + '.', '')
+ d.referenced_entity_name)
FROM sys.sql_expression_dependencies d
WHERE d.is_ambiguous = 0
AND d.referenced_id IS NULL
AND d.referenced_server_name IS NULL -- ignore objects from Linked server
AND CASE d.referenced_class -- if does not exist
WHEN 1 -- object
THEN OBJECT_ID(
ISNULL(QUOTENAME(d.referenced_database_name), DB_NAME()) + '.' +
ISNULL(QUOTENAME(d.referenced_schema_name), SCHEMA_NAME()) + '.' +
QUOTENAME(d.referenced_entity_name))
WHEN 6 -- or user datatype
THEN TYPE_ID(
ISNULL(d.referenced_schema_name, SCHEMA_NAME()) + '.' + d.referenced_entity_name)
WHEN 10 -- or XML schema
THEN (
SELECT 1 FROM sys.xml_schema_collections x
WHERE x.name = d.referenced_entity_name
AND x.[schema_id] = ISNULL(SCHEMA_ID(d.referenced_schema_name), SCHEMA_ID())
)
END IS NULL
GROUP BY d.referencing_id
) t
JOIN sys.objects o ON t.referencing_id = o.[object_id]
WHERE LEN(t.obj_name) > 4 -- hide valid aliases
DECLARE
@obj_id INT
, @obj_name NVARCHAR(1000)
, @obj_type CHAR(2)
DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
SELECT
sm.[object_id]
, QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name)
, o.[type]
FROM sys.sql_modules sm
JOIN sys.objects o ON sm.[object_id] = o.[object_id]
LEFT JOIN (
SELECT s.referenced_id
FROM sys.sql_expression_dependencies s
JOIN sys.objects o ON o.object_id = s.referencing_id
WHERE s.is_ambiguous = 0
AND s.referenced_server_name IS NULL
AND o.[type] IN ('C', 'D', 'U')
GROUP BY s.referenced_id
) sed ON sed.referenced_id = sm.[object_id]
WHERE sm.is_schema_bound = 0 -- objects without SCHEMABINDING
AND sm.[object_id] NOT IN (SELECT o2.obj_id FROM #objects o2)
AND OBJECTPROPERTY(sm.[object_id], 'IsEncrypted') = 0
AND (
o.[type] IN ('IF', 'TF', 'V', 'TR') --OR o.[type] = 'P' /* Microsoft Connect #656863 */
OR (
o.[type] = 'FN'
AND
-- ignore scalar functions, which are used in DEFAULT/CHECK constraints and COMPUTED columns
sed.referenced_id IS NULL
)
)
OPEN cur
FETCH NEXT FROM cur INTO @obj_id, @obj_name, @obj_type
WHILE @@FETCH_STATUS = 0 BEGIN
BEGIN TRY
BEGIN TRANSACTION
EXEC sys.sp_refreshsqlmodule @name = @obj_name, @namespace = N'OBJECT'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF XACT_STATE() != 0
ROLLBACK TRANSACTION
INSERT INTO #objects (obj_id, obj_name, err_message, obj_type)
SELECT @obj_id, @obj_name, ERROR_MESSAGE(), @obj_type
END CATCH
FETCH NEXT FROM cur INTO @obj_id, @obj_name, @obj_type
END
CLOSE cur
DEALLOCATE cur
SELECT obj_name, err_message, obj_type
FROM #objects |
|
Thanks for this report, this should be an easy item to address in an upcoming release. I'll look at making the necessary changes |
|
We have detected this issue has not had any activity during the last 90 days. That could mean this issue is no longer relevant and/or nobody has found the necessary time to address the issue. We are trying to keep the list of open issues limited to those issues that are relevant to the majority and to close the ones that have become 'stale' (inactive). If no further activity is detected within the next 14 days, the issue will be closed automatically. |
|
@skarpik what is the impact of these invalid objects in your situation? I do not see any issues with backup/restore/moving of DB's to either .bak or .bacpac formats. etc. |
|
For notes.
I'm submitting a PR for |
…hat should have been gone already.
Partial solution for #5552 to remove a stored procedure
Description of bug
In the course of hunting down the source of a problem upgrading DNN Action Forms to the current version, I discovered that the likely cause was some invalid SQL objects. I found a list of invalid objects but I couldn't say for sure which were related to DNN Sharp and which were associated with the base DNN installation. So I repeated my investigation on an out-of-the-box standard installation of DNN and found 7 instances of invalid objects.
Steps to reproduce
Current behavior
Here are the list of invalid objects:
obj name err_message obj_type
[dbo].[FitsExtendedPropertyPermission] Invalid object name 'dbo.vw_RelatedUsers' FN
[dbo].[aspnet_AnyDataInTables] Invalid object name 'dbo.aspnet_WebEvent_Events' P
[dbo].[aspnet_Users_DeleteUser] Invalid object name 'dbo.aspnet_UsersInRoles' P
[dbo].[DeleteSearchWord] Invalid object name 'dbo.SearchWord' P
[dbo].[ExportImport_AddUpdateUsersBulk] Invalid object name 'Membership' P
[dbo].[ImportDocumentLibraryCategories] Invalid object name 'dbo.dlfp_Category' P
[dbo].[ImportDocumentLibraryCategoryAssoc] Invalid object name 'dbo.dlfp_DocumentCategoryAssoc' P
The screenshot below is probably easier to read.
Expected behavior
In general, undefined objects are not a good thing. If these are functions/stored procedures that are not used, then these invalid objects are not a big concern. However, if these functions/stored procedures get referenced, there will be trouble. Not knowing what any of these do, I can't say whether this is an issue that needs to get fixed.
Screenshots
Error information
See screenshot above for script output.
Additional context
URL for SQL Script:
Find Invalid Objects in Your Databases
https://blog.devart.com/find-invalid-objects-in-your-databases.html
Affected version
I also found issue was also found in DNN v9.11.0 and DNN v9.10.2, so this maybe something that cropped up a number of DNN versions in the past.
Affected browser
n/a
The text was updated successfully, but these errors were encountered: