Copy database wizard or replication setup might fail due to broken dependency
Posted by Karthick P.K on May 4, 2012
Copy database wizard would fail with below error while creating views or user defined functions if the dependency lists of objects is broken.
Error:
failed with the following error: “Invalid object name ‘dbo. .”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
StackTrace: at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
Replication setup would also fail while applying the scripts if the dependency lists of objects is broken.
Error:
The schema script ‘XXX_4.sch’ could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL20164)
Get help: http://help/MSSQL_REPL20164
Invalid object name ‘. (Source: MSSQLServer, Error number: 208)
Get help: http://help/208
--Below script will fix the broken dependencies on all the objects
----------------------------------------------------------------------------
--List of objects for which referenced objects are missing.
--ex: View created on table XYZ and table XYZ is dropped
----------------------------------------------------------------------------
SELECT OBJECT_NAME (referencing_id),referenced_database_name, referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_entity_name not in (select name from sysobjects)
create table #t_excluded_modules (module_name sysname)
go
create table #t_modules_refreshed_in_end (module_name sysname)
go
------------------------------------------------------------------------------
--
-- get the list of modules whose dependencies have to be refreshed
--
-- Comment:
-- in the list we're not considering procedures or triggers because
-- because they can be created in any order, which means they can be refreshed
-- in any order
--
------------------------------------------------------------------------------
create table #t_user_views_or_tables (module_id int)
insert into #t_user_views_or_tables(module_id)
select object_id from sys.objects where
type in ('V', 'FN', 'IF', 'TF')
and name not like 'MSMerge%'
and is_ms_shipped <> 1
and name not in (select * from #t_modules_refreshed_in_end)
and name not in (select * from #t_excluded_modules)
insert into #t_user_views_or_tables(module_id)
select object_id from sys.objects where
name in (select * from #t_modules_refreshed_in_end)
----------------------------------------
--
-- get the dependency table
-- |---------------------------------|
-- | referencing_id | referenced_id |
-- | ----------------|---------------|
-- | XXX | XXX |
-- | ----------------|---------------|
--
----------------------------------------
Declare @module int, @message varchar(1000), @str nvarchar(1000)
create table #t_dependency_table (referencing_id int, referenced_id int)
DECLARE modules_cursor CURSOR FOR SELECT module_id FROM #t_user_views_or_tables
open modules_cursor
fetch next from modules_cursor into @module
IF @@FETCH_STATUS <> 0
PRINT ' <<None>> No module to refresh'
while @@FETCH_STATUS = 0
begin
select @str = quotename(schema_name(objectproperty(@module, 'schemaid'))) + '.' + quotename(object_name(@module))
select @message = ' trying to refresh ' + @str
print @message
exec sys.sp_refreshsqlmodule @str
select @message = ' ' + @str + ' was refreshed'
print @message
insert into #t_dependency_table (referencing_id, referenced_id)
select distinct object_id as referencing_id, referenced_major_id as referenced_id
from sys.sql_dependencies
where object_id <> referenced_major_id -- to avoid self recursion for functions
and object_id = @module
fetch next from modules_cursor into @module
end
close modules_cursor
deallocate modules_cursor
-------------------------------------------------------------------------------------------
--
-- get the the bottom of the dependency list i.e. independent modules
-- i.e.
-- get the list of referenced_ids in the dependency table which
-- don't occur in the referencing_ids column
--
-- Comment:
-- if there are circular dependencies then the few modules which form a circular dependency
-- would be ignored in the independent modules list
--
-------------------------------------------------------------------------------------------
create table #t_independent_modules (modules int)
insert into #t_independent_modules (modules)
select #t_dependency_table.referenced_id from
#t_dependency_table left outer join #t_dependency_table t2
on #t_dependency_table.referenced_id = t2.referencing_id
where
t2.referencing_id is NULL
-------------------------------------------------------------------------------------------
--
-- build the ordered list of dependencies starting with the independent modules
-- in the beginning first few rows, ones dependent on it in the following rows and so on...
--
-- there can be tricky cases of dependencies such as
-- V1 -> V2 -> V3
-- | ^
-- +-----------+
--
-- in the above example the refresh order would be V3, V1, V2, V1.
-- note that V1 is being refreshed twice, the last refresh of V1 after V2 is important.
--
-------------------------------------------------------------------------------------------
create table #t_final_dependency_list (id_num int IDENTITY(1,1), modules int)
while exists (select * from #t_independent_modules)
begin
-- append the set of independent modules into a list
insert into #t_final_dependency_list select * from #t_independent_modules
-- get the set of dependent modules
select distinct #t_dependency_table.referencing_id into #temp_table
from #t_dependency_table
where #t_dependency_table.referenced_id in (select * from #t_independent_modules)
-- clear up the list of independent modules
truncate table #t_independent_modules
-- the dependent modules now become the independent modules
insert into #t_independent_modules select * from #temp_table
-- delete the dependent modules list
drop table #temp_table
end
-----------------------------------------------------------
--
-- refresh the modules once more but in the right order now
--
-----------------------------------------------------------
declare modules_cursor_final cursor for
select modules from #t_final_dependency_list order by #t_final_dependency_list.id_num
open modules_cursor_final
fetch next from modules_cursor_final into @module
IF @@FETCH_STATUS <> 0
PRINT ' <<None>> No module to refresh'
while @@FETCH_STATUS = 0
begin
if (select type from sys.objects where object_id = @module) in ('V', 'FN', 'IF', 'TF')
and (select is_schema_bound from sys.sql_modules where object_id = @module) = 0
begin
select @str = quotename(schema_name(objectproperty(@module, 'schemaid'))) + '.' + quotename(object_name(@module))
select @message = ' trying to finally, once more, refresh ' + @str
print @message
exec sys.sp_refreshsqlmodule @str
select @message = ' ' + @str + ' was finally refreshed once again'
print @message
end
fetch next from modules_cursor_final into @module
end
close modules_cursor_final
DEALLOCATE modules_cursor_final
-----------
--
-- cleanup
--
-----------
drop table #t_excluded_modules
drop table #t_modules_refreshed_in_end
drop table #t_user_views_or_tables
drop table #t_dependency_table
drop table #t_independent_modules
drop table #t_final_dependency_list
MSSQLWIKI 
Top SQL Server blogs from MSSQLWIKI « MSSQLWIKI said
[…] Copy database wizard or replication setup might fail due to broken dependency […]
John "Z-Bo" Zabroski said
This script is wonderful. I made two bugfixes:
1) You don’t handle stored procedures or triggers.
2) You don’t handle the scenario where a schema-bound view is transitively referencing a broken view.
Let me know how I can send you my patches.
I am guessing you have an automated QA suite…?
Karthick P.K said
Hi John,
It was years back. Please post the fix in comments. It would be useful for other users.
Thanks
Karthick
John "Z-Bo" Zabroski said
— Small modification made to handle schemabound views “correctly”.
— Small modificationmade to handle stored procedures and triggers “correctly”.
— I say “correctly” because there are no unit tests to prove various scenarios are handled well.
–Below script will fix the broken dependencies on all the objects
—————————————————————————-
–List of objects for which referenced objects are missing.
–ex: View created on table XYZ and table XYZ is dropped
—————————————————————————-
SELECT OBJECT_NAME (referencing_id),referenced_database_name, referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_entity_name not in (select name from sysobjects)
create table #t_excluded_modules (module_name sysname)
go
create table #t_modules_refreshed_in_end (module_name sysname)
go
——————————————————————————
—
— get the list of modules whose dependencies have to be refreshed
—
— Comment:
— in the list we’re not considering procedures or triggers because
— because they can be created in any order, which means they can be refreshed
— in any order
—
——————————————————————————
create table #t_user_views_or_tables (module_id int)
insert into #t_user_views_or_tables(module_id)
select object_id from sys.objects where
— vvvvvvvvvvvvvvvvvvvv MODIFICATION TO SCRIPT vvvvvvvvvvvvvv
(
— ^^^^^^^^^^^^^^^^^^^^ MODIFICATION TO SCRIPT ^^^^^^^^^^^^^^
type in (‘V’, ‘FN’, ‘IF’, ‘TF’)
— vvvvvvvvvvvvvvvvvvvv MODIFICAITON TO SCRIPT vvvvvvvvvvvvvv
or TYPE in (‘P’, ‘TR’)
)
— ^^^^^^^^^^^^^^^^^^^^ MODIFICATION TO SCRIPT ^^^^^^^^^^^^^^
and name not like ‘MSMerge%’
and is_ms_shipped 1
and name not in (select * from #t_modules_refreshed_in_end)
and name not in (select * from #t_excluded_modules)
insert into #t_user_views_or_tables(module_id)
select object_id from sys.objects where
name in (select * from #t_modules_refreshed_in_end)
—————————————-
—
— get the dependency table
— |———————————|
— | referencing_id | referenced_id |
— | —————-|—————|
— | XXX | XXX |
— | —————-|—————|
—
—————————————-
Declare @module int, @message varchar(1000), @str nvarchar(1000)
create table #t_dependency_table (referencing_id int, referenced_id int)
DECLARE modules_cursor CURSOR FOR SELECT module_id FROM #t_user_views_or_tables
open modules_cursor
fetch next from modules_cursor into @module
IF @@FETCH_STATUS 0
PRINT ‘ <> No module to refresh’
while @@FETCH_STATUS = 0
begin
select @str = quotename(schema_name(objectproperty(@module, ‘schemaid’))) + ‘.’ + quotename(object_name(@module))
select @message = ‘ trying to refresh ‘ + @str
print @message
–vvvvvvvvv MODIFICATION TO ORIGINAL SCRIPT vvvvvvvvvvvv
if (objectproperty(object_id(@str),’IsSchemaBound’) = 0)
begin
–^^^^^^^^^ MODIFICAITON TO ORIGINAL SCRIPT ^^^^^^^^^^^^
exec sys.sp_refreshsqlmodule @str
–vvvvvvvvv MODIFICATION TO ORIGINAL SCRIPT vvvvvvvvvvvv
end
–^^^^^^^^^ MODIFICAITON TO ORIGINAL SCRIPT ^^^^^^^^^^^^
select @message = ‘ ‘ + @str + ‘ was refreshed’
print @message
insert into #t_dependency_table (referencing_id, referenced_id)
select distinct object_id as referencing_id, referenced_major_id as referenced_id
from sys.sql_dependencies
where object_id referenced_major_id — to avoid self recursion for functions
and object_id = @module
fetch next from modules_cursor into @module
end
close modules_cursor
deallocate modules_cursor
——————————————————————————————-
—
— get the the bottom of the dependency list i.e. independent modules
— i.e.
— get the list of referenced_ids in the dependency table which
— don’t occur in the referencing_ids column
—
— Comment:
— if there are circular dependencies then the few modules which form a circular dependency
— would be ignored in the independent modules list
—
——————————————————————————————-
create table #t_independent_modules (modules int)
insert into #t_independent_modules (modules)
select #t_dependency_table.referenced_id from
#t_dependency_table left outer join #t_dependency_table t2
on #t_dependency_table.referenced_id = t2.referencing_id
where
t2.referencing_id is NULL
——————————————————————————————-
—
— build the ordered list of dependencies starting with the independent modules
— in the beginning first few rows, ones dependent on it in the following rows and so on…
—
— there can be tricky cases of dependencies such as
— V1 -> V2 -> V3
— | ^
— +———–+
—
— in the above example the refresh order would be V3, V1, V2, V1.
— note that V1 is being refreshed twice, the last refresh of V1 after V2 is important.
—
——————————————————————————————-
create table #t_final_dependency_list (id_num int IDENTITY(1,1), modules int)
while exists (select * from #t_independent_modules)
begin
— append the set of independent modules into a list
insert into #t_final_dependency_list select * from #t_independent_modules
— get the set of dependent modules
select distinct #t_dependency_table.referencing_id into #temp_table
from #t_dependency_table
where #t_dependency_table.referenced_id in (select * from #t_independent_modules)
— clear up the list of independent modules
truncate table #t_independent_modules
— the dependent modules now become the independent modules
insert into #t_independent_modules select * from #temp_table
— delete the dependent modules list
drop table #temp_table
end
———————————————————–
—
— refresh the modules once more but in the right order now
—
———————————————————–
declare modules_cursor_final cursor for
select modules from #t_final_dependency_list order by #t_final_dependency_list.id_num
open modules_cursor_final
fetch next from modules_cursor_final into @module
IF @@FETCH_STATUS 0
PRINT ‘ <> No module to refresh’
while @@FETCH_STATUS = 0
begin
if (select type from sys.objects where object_id = @module) in (‘V’, ‘FN’, ‘IF’, ‘TF’)
and (select is_schema_bound from sys.sql_modules where object_id = @module) = 0
and not (objectproperty(object_id(‘viewname’), ‘IsSchemaBound’) = 1)
begin
select @str = quotename(schema_name(objectproperty(@module, ‘schemaid’))) + ‘.’ + quotename(object_name(@module))
select @message = ‘ trying to finally, once more, refresh ‘ + @str
print @message
exec sys.sp_refreshsqlmodule @str
select @message = ‘ ‘ + @str + ‘ was finally refreshed once again’
print @message
end
fetch next from modules_cursor_final into @module
end
close modules_cursor_final
DEALLOCATE modules_cursor_final
———–
—
— cleanup
—
———–
drop table #t_excluded_modules
drop table #t_modules_refreshed_in_end
drop table #t_user_views_or_tables
drop table #t_dependency_table
drop table #t_independent_modules
drop table #t_final_dependency_list
John "Z-Bo" Zabroski said
To clarify my first bugfix, by the way, you have a comment that says stored procedures can be refreshed in any order.
This is only true for trivial stored procedures that don’t reference objects that can’t be refreshed in any order. My fix handles that scenario.
John Zabroski said
Hi Karthik,
Can I have your permission to host a newer version of your script on my GitHub? There are a lot of inferior solutions to this problem, and almost everyone uses a script inferior to the one you wrote (which is genius). In this way, there would be a standard place for people to contribute future bug fixes and test cases for your genius script.