Is it possible to export a database model from visio to Sql Server 2005?

Hi there,

I cannot seem to find a way to export a database model from Visio to Sql Server.

Can somebody provide a walkthrough how to export it?

Thanks a lot

Not sure if this is what you mean but you can't export the data model from Visio into SQL Server database diagrams.

-Sue

|||

What I mean is .

I use Visio to write technical specs. I have lots of them with data Model in them.When it comes to write the functionality I would like to generate the tables into SqlServer and avoid to rewrite them from scratch.

Is this possible?

|||

Yes you can. I have done this with Visio 2003 for Enterprise Architects (I think that's the edition at least), it has the ability to generate a sql script of your visio data model. I did not see this functionality in any other edition of visio 2003 (had professional before). Not sure about visio 2007.

Here's a link to how to do it in Visio 2003:

http://www.codeproject.com/gen/design/visioDatabase.asp?df=100&forumid=372789&exp=0&select=1840912

|||

Same for Visio 2007, you need the EA functionality in order to do this.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Is it possible to export a database model from visio to Sql Server 2005?

Hi there,

I cannot seem to find a way to export a database model from Visio to Sql Server.

Can somebody provide a walkthrough how to export it?

Thanks a lot

Not sure if this is what you mean but you can't export the data model from Visio into SQL Server database diagrams.

-Sue

|||

What I mean is .

I use Visio to write technical specs. I have lots of them with data Model in them.When it comes to write the functionality I would like to generate the tables into SqlServer and avoid to rewrite them from scratch.

Is this possible?

|||

Yes you can. I have done this with Visio 2003 for Enterprise Architects (I think that's the edition at least), it has the ability to generate a sql script of your visio data model. I did not see this functionality in any other edition of visio 2003 (had professional before). Not sure about visio 2007.

Here's a link to how to do it in Visio 2003:

http://www.codeproject.com/gen/design/visioDatabase.asp?df=100&forumid=372789&exp=0&select=1840912

|||

Same for Visio 2007, you need the EA functionality in order to do this.

Jens K. Suessmeyer

http://www.sqlserver2005.de

sql

is it a bug ? (relationships in report builder)

Hi friends
am having some weird problem.am using sql server 2005 standard edition.

i've a report model with tables (tab1,tab2,tab3). tab1 and tab2 (actually these are views) relate to same table but each has different columns from same table.
the third table i.e. tab3 is child of tab1.

in my report model project,i set cardinality of this role in tab3 as "one" and in tab1 as "Optionalmany".

when creating a report in reportbuilder.if select columns from either tab1 and/or tab2 i get to see 100 records which is correct.if add any column from tab3 i get to see only 1 record which also correct as i've only 1 row tab3 at the moment.

now ,fun begins , what should happen if i delete column(s) of tab3 from report designer ? i should see all 100 records ,right ? bcoz all my columns coming from either tab1 or tab3 but when i run report i get see only 1 record !!! why ? is it a bug or am i missing something.
Thanks for your help.no one ever faced this situation ?
any ideas on this one much appreciated.|||

The difference between the first report that returned 100 rows and the third report that returned 1 row is the primary entity, which changed when you added a column from tab3, but did not revert when you deleted that column.

The primary entity of the first report is tab1, which means that report is fundamentally about tab1 and the data related to it.

The primary entity of the third report is tab3, which means that report is fundamentally about tab3 and the data related to it. Basically, the third report is a summary report for the data in tab3, grouped by tab1, which explains why there is only one row.

|||Thanks for the reply Bob.
does it mean that even if some one adds a field from tab3 accidentally ,he has to create the whole report from scratch so that he can see data that matches its criteria ?|||Bob
I think ,i kind of achieved what i want. please advise if i go into any problems in future.

what i did was i set cardinality for the roles on parent table side as "optionalone" and child entity side as "Optionalmany". (normally its other way round ,right?)
now my reports work just fine. i mean i get data am expecting and top of it if i remove the field from tab3 still my report worked displaying all records from tab1,tab2.

BTW when i deploy (using BI) i get a warning like below

"The Relation property of the Role 'tab3 detail' refers to the Target end of the Relation 'dd_tab1-dd_tab3', which is not bound to a set of uniquely constrained columns for the Table 'dbo.dd__tab3'. Roles with Cardinality of One or OptionalOne require relations bound to uniquely constrained columns of the table."

is it a problem ?
i checked sql the report builder making (in sql profiler) and they look fine as its placing joins correctly on both parent and child tables.
any suggestions on this much appreciated.
Thank you very much|||

No, you should not swap the cardinality of your report model roles to get different joins. RB relies on this information in many ways to provide a consistent and appropriately constrained query design experience to the user.

You are right that there is currently no way to revert the primary entity other than rebuilding your report. This feature was slated for SQL 2005 at one point, but unfortunately did not make it into this release.

Is copy of database and log file enough for backup?

Hello,

i would like to copy the SQL Server Express database .mdf and .ldf files for backup. Is this ok?
Autoclose = true and recovery model = simple.

Must i detach the database before copy the 2 files or can i copy the 2 files without detach at any time? When connections are open (also remote connections).
Can i copy at any time even when transactions are active?

I would like to write a copy programm which copies the 2 files every 30 minuutes. Only 30 minutes of work could be lost.

This would be enough for me and i don't have to care for the the BACKUP and RESTORE stuff. In the past i used BACKUP and when i needed this BACKUP it did not run. Returns some error message..

Is copy ok? When is it possible? At any time or must all transactions be comitted? Must all connections (remotes too) be closed? Must the database be detached?

Is this enough to have a valid backup? Backup would be an attach of the .mdf file.

Or must i use the BACKUP and RESTORE stuff? Why?
If so, for what reason is the AUTO CLOSE property there?

Regards,

Markus

And in my opinion attach a database should be enough. ít is the users, the owners, wish to get the data stored in the database.

In the past, as i tried to use RESTORE stuff, i get an error message. From the sql server system point of view this was ok because something of the restore file did not match the STRICT criteria for restore. But i lost the data.

Therefore a attach should do it, to fullfill the wishes of the owner. To show him the data of that .mdf file. Even it this .mdf file does not meet the critierias of the current version. SQL Server should inform the owner of that, and ask if it is allowed to try to converte the file to the current format. If OK, it should do everything to save as much data as possible.

Sorry, if it sounds a bit curious, but i would like a way to do the obvious things without force the owner to take any learning effort.

Read in a blog:
http://www.sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/Default.aspx
This schould not be the case..

Markus

|||

I personaly would use the back up and restore options, this is what they are designed for. You can run these from the Management studio (Express Version) or from a raw query. If you need to schedule it you can either use the normal scheduler that is in windows or use a custom one. For one of my clients I created a windows service that copied the function on the unix cron system but on a windows machine.

The only time that I have used the attach and detach functions is when I need to move a database quickly. I have seen some people use it to install the database when the program is installed, but for this I prefer to code a solution that creates the database from scripts. Doing it this way I know that the structure and data is clean at the time of install.

|||

Thank you Glenn.

But the question was, is copy enough? And under what conditions?

In my opinion, if Sql Server Express should be a common datastore, it should be easy to backup.

Without knowing Sql Server Books online, without knowing what "scripts" are. This is stuff for a few freaks, who likes things like that. But most of the people don't like to read such stuff. Most people hate this stuff.

What is if someone use a Sql Server database (any older version) and want to sell his computer. He copies the .mdf and .ldf file to a cd. He buys a new computer. Installs a new download of Sql Server. Tries to attach the copied files. This should be the only thing he should know. And Sql Server should be the best it can do and not show an error message.

Or what is if someone send's the .mdf and .ldf file via email to another person. Who knows which version of Sql Server he is running`?

What i mean, if Sql Server want to be a datastore of everyone it should meet the needs of everyone. Don*t kow and don't need to know what BOL is or what scripts are. Perform the needs of the owners autmatically and explain him in a few simple sentences.

I think today the normal person is a bit confused.

Best regards,

Markus

|||

When you do use the attach and detach system you do not have to copy the ldf file as this is only the transaction log file, In that should only be open transactions... If you are copying the file to a new location you will need to make sure that al transactions are commited to the database. This is why I prefer to use the backup option as this makes sure that at the time of the backup all of the data is stored. If you do use the attach and detach method there are chances of loosing data.

|||

Hello Glenn,

you wrote:
> The only time that I have used the attach and detach functions is when I need to
> move a database quickly. I have seen some people use it to install the database
> when the program is installed, but for this I prefer to code a solution that creates
> the database from scripts. Doing it this way I know that the structure and data is
> clean at the time of install.

This is what i want to do when my program is installed. Install SQL Server Express with a named instance. Copy the database files and attach them. Because there ist allready data in the database files. What do you think is the risk of that way? Have you ever heard that his fails?

> When you do use the attach and detach system you do not have to copy the ldf
> file as this is only the transaction log file, In that should only be open transactions...

If i use attach and have only the .mdf file, is the .ldf file then new created?

> If you are copying the file to a new location you will need to make sure that al
> transactions are commited to the database.

Is this the case when i use Detach? When does a detach fail?

> This is why I prefer to use the backup option as this makes sure that at the time of
> the backup all of the data is stored. If you do use the attach and detach method
> there are chances of loosing data.

Are there limitations of restore and backup? When will a restore fail? What is of different version ofs sql server, differences betwen the system where the database was backed up and where it is to be restored? Is there allways compatibility or what must the user care for, that the restore will run?

Regards,
Markus

|||

>This is what i want to do when my program is installed. Install SQL Server Express with a named instance. Copy the database files and attach them. Because there ist allready data in the database files. What do you think is the risk of that way? Have you ever heard that his fails?

Well there are certain things that are not stored in the database itself. Logins, for example.

I have had problems doing exactly what you described in SQL 2000, especially when my original database had any users other than dbo.I've had problems with restore as well.

If you are shipping initial data with your product I would recommend that you do it all in code.INFORMATION_SCHEMA is your friend.I've done things using batch scripts and the command line tools.These work but are not flexible enough and don't provide sufficient error detection.

|||

>Well there are certain things that are not stored in the
>database itself. Logins, for example.
>I have had problems doing exactly what you described
>in SQL 2000, especially when my original database had
>any users other than dbo. I've had problems with
>restore as well.

But when you install a seperate named instance for your application? Do you see this problems in this case too?

|||

Yes you do see the same problems, a Named Instance is just like a completly new server install...