Joe/Homer,
Thanks for taking the time to put all of that response together. Most of that I already knew, and moving changes to and from the model is pretty simple (if you know about the quirks). The biggest gripe I have is that I have to create a SP in the global code section manually, where I don't have to for the other objects in the physical db that are added directly.
The way our Dev group works is that they add various tables and SPs to their dev copy of the database, test them against the websites and then forward the object names to me. I the run the model against the DB and pull those changes in. Having to manually create 4 SPs for each new table (Get,Update,Select,Delete) leaves lots of room for error. Especially so if a whole new chunk of functionality is coming in, that has dozens of tables....this happens regularly.
I think I will probably dump Visio in favor of keeping a "model" physical database on a machine nobdy else can get to (except the other dba), and use SQL Compare from Red Gate Software to verify differences. SQL Compare doesn't miss anything as far as I can tell, and I already own a copy.
Thanks again!
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
I just happened to be trying this myself. My first impression of the tool is that it has a lot of capability but it is a bit quirky. Particularly the synchronization part. You should probably get the Halpin book, "Database Modeling with Microsoft Visio for Enterprise Architects" if you intend on using this tool.
I recommend experimenting with this using a copy of your source and database model files and a scratch database. Otherwise you might really get yourself badly out of sync before you know it. You can screw yourself up royally if you're not careful. Don't ever make changes to both the model and the database at the same time (unless you know what you're doing). Either make changes to the model and incorporate them in the database or make changes to the database and incorporate them in the model, never both at the same time (unless you know what you're doing). Your best bet is to forget about making direct changes to the database until you get some more experience.
Here is a procedure you may find useful:
Go the your database logical (not ORM source) model.
You should be able to do a "Database\Project\Build" before making other changes. This will ensure that any changes from the ORM source model are incorporated into the database logical model. Ideally, you wouldn't want to find any changes.
Select "Database\Model\Refresh...". If your physical database and the logical model are in sync you should be able to make it all the way through the wizard without encountering any screens that suggest you need to make changes to either the model or the database. If you get any tree view listings of changes that need to be made I would get those resolved before messing with the stored procedures. The Halpin Visio book can help you figure out how to resolve the discrepancies.
Assuming the model and database are in sych...
Select Database\View\Code. This should cause a "Code" tab to appear on the bottom window.
Listed under the "Global Code" section are the stored procedures that are incorporated in your logical model. If you have a stored procedure in your database called MyStoredProc, create a new entry in the global code section called 'MyStoredProc'. The names have to match (near as I can tell). Set the type to stored proc, don't worry about the mirror file name or body section at this time.
Use the "Database\Model\Refresh..." command again. This time you should see a tree view entry that says something like "The following code already exist in the database: MyStoredProc" (assuming that MyStoredProc was an existing stored procedure). For resolution select "Refresh model". This will pull the code for the stored procedure from the database down into the model. You will probably see a window requesting you to "Migrate Changes" to the source model. Most of the time when these pop up you want to say no. The only time you want to say yes is if you are explicitly making changes to the logical model or the physical database. Since we have made changes to the logical model and everything is in synch we will say 'Yes'. Otherwise, the next time you "Build Project", your changes may be lost in the database model.
Now if you open the MyStoredProc global code item that you created earlier you should see that the stored procedure code has been sucked down from the database. At this point you may want to investigate the "Mirror file" properties that we skipped earlier as well as the "Database\Code\Synchronize". These basically allow you to move the stored procedure body between the file system and the model, such as if you needed to keep the stored procedure code under source control.
Good luck. That's about the extent of my limited knowledge on the subject.
Joe Herr
Anyone? I could really use some advice here. I am contemplating throwing Visio out for the reasons below
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
Visio 2002? (10.0.2705) for EA...
I inherited this tool at a new position and so far am less than impressed...
I have a data model for a SQL Server 2000 database, which does not pick up on a new stored procedure when it has been added to the database and the model compared. Iwipick up new tables, new views and any changed objects, but not new SPs. Am I missing something?
I have a table in the model with a bit, non-nullable field, set to default to 0, but Visio wants to try to update the target database with a default of 0 AND a default of 1. Where is the "1" coming from?
In my Code window, Tables and Views are listed alphabetically, but code (stored procedures) is listed in order added to the model.
Those are the big issues....perhaps I just need to RTFM, but this seems like basic stuff that ought to work better/different.
All advice is appreciated..
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com