Visio and SQL Server 2005

[ERUM]

I have Visio in which i draw ERD and i want to export that ERD in SQL SERVER 2005 (so that all DB object Maps perfectly). Is there nay option which made me Perfectly to do so .

Any Urgent help would highly appreciated.........

[RhinoBull]

I don't believe Visio is designed for database modeling - not yet at least.
You need something like CA ERwin® Data Modeler or ER/Studio®.
But those are very expensive tools.
You may need to research on some least expensive or maybe even free utilities however if you are building enterprise solution your company better get a copy of one of those I mentioned.

[techgnome]

Actually Visio does support it.... we've been using it to document our database for years.... problem is though..... I think it's one way... ie, it can read a database and diagram it, but I don't think there's a way to send changes back to the server.

-tg

[RhinoBull]

Well, I'm somewhat aware of what you mentioned TG, but as I said I don't believe that Visio was designed to do data modeling - it's a diagraming tool.
They did expand its functionality over years though.

[mendhak]

Do you not have the Database > Generate menu option? What version of Visio do you have?

[RhinoBull]

You probably asked OP but I have Visio Pro 2003 and it doesn't that option. Am I missing something?
I can do reverse engineering and some other stuff but no generate db.

[mendhak]

I have Visio Enterprise Architect, do you think it's also related to the current VS version?

[mendhak]

OK, found something. You may not believe this but I used yahoo search for this one.


http://office.microsoft.com/en-us/vi...496961033.aspx

[RhinoBull]

Ah, that explains! Thanks mendhak.
I did search within MS site but to no avail... Strange things do happen I some times.
Thanks again for the effort.

[ERUM]

I m using Visio 2007 .and as far as http://office.microsoft.com/en-us/vi...496961033.aspx is in concerned it is written over there that u should apply to 2002,2003

[RhinoBull]

I don't think anyone's mentioned Visio 2007 - to save your db model to database you need Visio 2003/2005 for Enterprise Architects edition.
It only comes with Visual Studio 2003/2005 Architect. You cannot just buy it.
You may also download it from MS only if you have special MSDN subscription (perhaps Visual Studio Team Suite, etc).

[Shaggy Hiker]

Visio is the goofiest piece of software I have ever seen from MS, and that's saying something. For some time, the version of Visio that had the generate option was not the newest, greatest, version. However, the EA version does have it, as noted earlier. Now if it would only work well.....

Actually, it does work fine...once you recognize the maddening design decisions that were made for this option. I just love the fact that generation will fail so quietly that you won't know that it failed until you find that parts of your database simply don't exist. The first time I generated one particularly complex database I had set some string fields to VarChar MaxSize. Now, to me that means that the data type for the field will be a VarChar of the maximum size allowed by that DB engine. I tried generating it for Access, and ended up with only about half the tables, because the max size of a string field in Access is 255 characters, and Visio tried to generate strings with the maximum size for SQL Server. Therefore, the Max Size is not based on what the target DB engine allows, but is based on whatever whim Visio has decided on. I didn't get any error messages, either. In fact, Visio reported no problems, it was only upon reviewing the script that I realized what had happened (when the CREATE TABLE command failed, it wrote one mild message to the output and went on to the next table, all keys related to the now-missing table also failed without messages).

Since I ultimately wanted a SQL Server DB, I went with that (for some of our stuff, testing in Access is just easier, you don't want to know why.....I don't want to know why.....it's driving me slowly mad). However, Visio didn't create the DB on the first attempt. It said it did, it just didn't. That was a good thing, because it was trying to overwrite master or some such, by default. Why would they choose a default, that if it worked, would potentially screw up SQL Server.

Eventually, after much searching online (the help files in Visio are worse than useless, they are often simply wrong) I was able to piece together the steps I needed and generate a DB. Once you get it set up right, then Visio becomes pretty slick, because you can alter the design in pretty much any way (except for setting a data type to a larger type than the target DB Engine will accept, which will wreck your DB as the altered table gets dropped) and the changes take only about half a dozen button clicks, though even one of those clicks is baffling, since when you choose to regenerate a db, the default action is to not actually generate the db.

That program is sooooo close to being a well-designed tool.