January 14, 2015 at 2:45 pm
Last week I had a to migrate a database from SQL2003 to SQL2008. The 2003 Server had several databases on it. I identifying database1, backed it up, copied backup file to the new server and then restored it on that SQL2008.
I then used the old legacy application to query some data on the SQL2003 server and looked at SQL2003 activity to determine what was being queried. This led me to a view in database1 that gave me all the information I needed.
I then went to the same view in SQL2008 and found it failed. On closer inspection I found that the view failed as one of the tables it was looking for was not present in database1. (Yet the select statement in the view did not point to another database)
After doing a bit of searching, I found database2 on the SQL2003 server that contained the data. I backed up this database, copied the backup file and restored this to SQL2008. Voila, the View now worked.
What I cannot figure out is how is the data in DATABASE2 able to be visible from a view in DATABASE1. I have not seen it done in this way before without specifying the DATABASE in the select statement.
Would appreciate if somebody could give me a few pointers on what to look for
Regards
Terry
January 14, 2015 at 2:48 pm
January 14, 2015 at 4:24 pm
Or even another view. A view could also explicitly reference objects in a different db.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply