• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 873
  • Last Modified:

How to Export Database Diagrams to T-SQL script file

How can I reliably export a database diagram to a TSQL script file?  More specifically, how can I tell which rows in the dtproperties table are for which diagram?  I don't want to blindly copy the whole dtProperties table because I don't want to overwrite everything in the destination server.  Also I have tried this and it didn't work for me.  I see the diagram listed under the Diagrams node, but when I tried to open one of them I get an error about being out of memory.
0
fcx
Asked:
fcx
  • 4
  • 4
  • 3
2 Solutions
 
Anthony PerkinsCommented:
0
 
fcxAuthor Commented:
Thank you.  That script may prove to be valuable, but right now I cannot use it because I am trying to update a local dev server from a disconnected production server.  

I just using a DTS package to export the table to a flat file and then import the text file into the dev server, but this didn't work.  I got 'an unexpected error' message when I tried to open the diagram.

Do you know what is causing the failure?
0
 
Anthony PerkinsCommented:
No idea, I am afraid.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
arbertCommented:
"I just using a DTS package to export the table to a flat file and then import the text file into the dev server, but this didn't work.  I got 'an unexpected error' message when I tried to open the diagram."

Probably because it's binary data and you exported it to a text file...
0
 
fcxAuthor Commented:
How should I be doing it then?  
I assumed the DTS package would do something appropriate with the binary data.  Looking at the file, I see plain text and Hex, not binary jibberish.
0
 
arbertCommented:
"Looking at the file, I see plain text and Hex, not binary jibberish."

Don't you see the column that has the format of "image"?  This is definately a binary column.

The way diagrams should be handled is from a backup/restore or a detach/attach--they are an integral part of the database and shouldn't be moved on their own.
0
 
fcxAuthor Commented:
I understand the data is ultimately binary, but it looked to me like the dts package translated the binary data into plain text (by way of hex value) like you would do to send a picture to someone through email.

So you're telling me there's no way to export a diagram to a disconnected database containing the same table structure?
0
 
arbertCommented:
Not via a text file.

" I don't want to blindly copy the whole dtProperties table because I don't want to overwrite everything in the destination server. "

I'm not sure how you would ever get the entire diagram, plus, you'll have a mish-mash of stuff if you have existing records in there.  It's an undocumented system table you weren't meant to play with.
0
 
Anthony PerkinsCommented:
Here is another script that supposedly does exactly what you want to do:
sp_importdiagram.sql
http://www.databasejournal.com/scripts/article.php/1495561
0
 
Anthony PerkinsCommented:
But as arbert has mentioned, this is totally unsupported, so I recommend you backing up your server, prior to using it.
0
 
fcxAuthor Commented:
Thanks for your insight, arbert and acperkins.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now