June 4, 2009 at 7:01 am
Hi
I am trying to create a copy of an existing MSSQL2005 database using the copy database wizard (using the SQL management object method, not detach/attach). When I run the job it fails. The log reports that an error occurs because it is trying to create a view based on a non existing view. A quick look at the relevant view in the source database reveals that the dependency information is out of date. When I rebuild the referencing view the job stops at another view with outdated dependency info etc.
I can of course fix this by manually rebuilding each view/sp until all dependency information is up to date, but I figured someone may have encountered this problem before.
Any tips on how ensure that all the dependency information is up to date?
Below some code to show how the dependency information may become outdated.
USE tempdb
GO
--Create a table
CREATE table mytable (id int)
GO
--Create a view based on mytable
CREATE view myview1
AS
SELECT id FROM mytable
GO
--Create another view based on myview1
CREATE view myview2
AS
SELECT id FROM myview1
GO
PRINT('Display the objects referenced by myview2')
SELECT OBJECT_NAME(referenced_major_id) AS referenced_object_name
FROM sys.sql_dependencies
WHERE object_id = OBJECT_ID('myview2')
--Rebuild myview1 (using ALTER VIEW or simply sp_refreshsqlmodule)
EXEC sys.sp_refreshsqlmodule @name = N'myview1';
PRINT('Display the objects referenced by myview2 after rebuild of myview1')
SELECT OBJECT_NAME(referenced_major_id) AS referenced_object_name
FROM sys.sql_dependencies
WHERE object_id = OBJECT_ID('myview2')
DROP view myview2
GO
DROP view myview1
GO
DROP table mytable
GO
June 4, 2009 at 8:05 am
The problem of copying the database itself I solved by restoring the original database to a new location, but the problem of outdated dependency info still remain. This also affects the ordering of objects when scripting the database (or parts of it) from SSMS.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply