Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 2 seperate SQL databases on the same SQL instance. Database1 references tables on Database2 in its views and stored procedures. Everything was working fine until we had to create a fresh version of Database2 and rename the current Database2 to Database3.

The DBA did the following:

  1. Took Database2 offline, then reattached it as Database3. Database2 was then deleted from SQL.

  2. A new version of Database2 was created.

The problem:

The view and stored procedures in Database1 are still referencing the tables on Database3 unless we add the server name to the FROM clause in any queries.

The following view on Database1 goes to Database3 in error:

select * from Database2.dbo.Projects

For the correct behaviour we specify the server name:

select * from ProductionDB.Database2.dbo.Projects

Why are the views and stored procs of Database1 still referencing Database3 (unless we specify server name)?

Is it because we renamed the database by re-attaching instead of backup and restore?

Are there internal references in Database1 that need updating?

Are there hidden database synonyms I can't find?

sql sql-server backup database-administration cross

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

You might want to run sp_refreshview, it is recommended to run this if the underlying tables change at all (this case seems to be a good candidate for that case).

[Edit]: I found the underlying cause. The parse trees of views are generated and cached

select * from sys.syscacheobjects
where objtype = 'View'

This is the output of the tokenizer, so an attach detach should invalidate cached tokens. An sp_refreshview rebuilds the parse tree.

share|improve this answer
    
Please add some basic explanation why this happens, and maybe a link to an elaborated article for reference. that way your answer will be of use for many others in the future who will search for the same thing. –  Justicator May 7 '13 at 13:52
    
It would be great if someone could share why this would happen in the first place. We probably won't use the re-attach method in future, but it would be nice to know what is happening at a technical level. Thanks. –  Andrew May 7 '13 at 14:05
    
@Andrew: I was actually not able to reproduce the issue you are facing.. –  Rohan May 8 '13 at 6:30
    
@Andrew : I have explained the cause in an edit to the answer. –  Rohan May 13 '13 at 11:54
    
@Rohan +1 for finding the cause, thanks for sharing! –  Justicator May 20 '13 at 6:58
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.