How to transfer diagrams 1

[fractorr]

Anyone know how to transfer the diagram models when doing a export from one database to another? I have tried a script called sp_ImportDiagram but it did not work for me. Any ideas or suggestions would be very appreciated.

 

[JayKusch]

This was pulled from SQLmag ... may give you some ideas. Is based on SS7 though!

SQL Server 7.0 stores the information associated with Enterprise Manager's ER diagrams in the dtproperties table in each database. To move a diagram from one server to another, you simply move the associated rows in the dtproperties table. For example, the following command discovers which diagrams a database contains:

SELECT value, objectid FROM
dtproperties WHERE property =
"DtgSchemaNAME"
Running this query results in the following output:

value objectid
TestDiagram 8
TestDiagram 215

The value column contains a diagram's descriptive name, and the objectid column contains the internal ID number that SQL Server uses to track the diagram. You can then use a command similar to the following example to move the diagrams from one server to another:

SET IDENTITY_INSERT dtproperties
ON
INSERT
dtproperties
([id],objectid,property,value,
lvalue,version)
SELECT
[id],objectid,property,value,
lvalue,version
FROM
pubs..dtproperties
WHERE
pubs..dtproperties.objectid = 8
SET IDENTITY_INSERT dtproperties
OFF

You must use the SET IDENTITY_INSERT command because the dtproperties.id column uses the identity property. The WHERE clause lets you move only the TestDiagram diagram. To move all the diagrams, you can replace the WHERE clause with a command similar to the following command:

WHERE
pubs..dtproperties.objectid in
(
SELECT objectid FROM
pubs..dtproperties WHERE
property = "DtgSchemaNAME"
Be careful if you use this solution because neither Books Online (BOL) nor Microsoft TechNet document it and Microsoft doesn't officially support it. However, \mssql7\install\instnwnd.sql shows that Microsoft uses this technique to build the RELATIONSHIP diagram that SQL Server installs as part of the Northwind database.

Thanks

J. Kusch