May 27, 2004 at 8:05 pm
I am trying to copy a database using the export/import wizard and keep getting a 'Invalid object name' error.
The reason for this is DTS does not appear to create views in dependency order ie view A is dependent on view B and it attempts to create view A before it creates view B.
Any solutions? I just can't believe this is normal behaviour for DTS, so I must be doing something wrong.
May 27, 2004 at 9:14 pm
It is time to start believing. As far as I am aware, the tools supplied with SQL Server do not bother with managing dependency ordering in the manner you are after.
Normally, SQL is OK with objects being created out of order. It normally resolves such issues when the views are actually used. The only problem I have is with functions - for reasons I don't understand, as yet, all objects that a function uses must exist before you can create the function.
May 28, 2004 at 1:20 am
There are two ways that I would approach this, and both of them involve scripting.
1. Use the generate script option on your objects.
SQL Server will generate the usual MS verbosity and in the wrong order but the script will run with warning messages. You then need to loop through your views/procs and run sp_refreshview and sp_recompile respectively.
This is very quick to do.
2. Develop using scripts in the first place.
I always build scripts for my entire database. It is long winded I know, but you have an exact record or what objects were created where and how. The scripts can also be stored in source safe.
My developers tend to do a half way house between the two. They get SQL to generate the scripts then clean them up afterwards, and put them into dependency order.
May 28, 2004 at 1:25 am
An option to consider is to use a tool such as red-gate's SQL Compare to generate your scripts. This does a pretty good job.
We automatically stored all database objects in source safe - you can't really on developers to do this because there are simply too many ways to get to the database that don't have any integration with source safe or whatever your source control software may be.
May 28, 2004 at 1:42 am
I agree about keeping the original scripts to create the database objects. But the 3rd-party vendor who wrote the application does not provide them.
Also, the filegroup defs do not match between Development and UAT, otherwise I would have tried the Database Copy wizard or one of the 'restore' methods.
No budget for Admin tools, unfortunantly.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply