MSSQLWIKI

Karthick P.K on SQL Server

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
About these ads

One Response to “Copy database wizard or replication setup might fail due to broken dependency”

  1. […] Copy database wizard or replication setup might fail due to broken dependency […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 2,127 other followers

%d bloggers like this: