In article <O4zxuysWDHA.572 (AT) TK2MSFTNGP11 (DOT) phx.gbl>, George Padvorac
<georgep (AT) nwis (DOT) net> writes
Q. How can I transfer database diagrams?

A. Database diagrams are stored in the dtproperties table, with each
diagram being made up of several related rows. The primary row has a
property of DtgSchemaOBJECT. The component rows can be identified as
having the primary row's id value in their objectid column.

When the two databases are exactly the same, and the destination does
not have contain any diagrams use the following statement:

SET IDENTITY_INSERT DestinationDB..dtproperties ON

INSERT DestinationDB..dtproperties
SELECT id, objectid, property, value, lvalue, version
FROM SourceDB..dtproperties T1
WHERE EXISTS(SELECT * FROM SourceDB..dtproperties T2 WHERE T2.property =
'DtgSchemaOBJECT' AND T1.objectid = T2.id)

SET IDENTITY_Insert DestinationDB..dtproperties OFF

For SQL 2000 amend the above SELECT statement to include the new uvalue
column.

To transfer between servers use DTS and the DataPump Task, with the
SELECT statement above as your source, and the dtproperties table as
your destination. Remember to set the "Enable Identity Insert" property
of the DataPump to maintain integrity of the data.

For databases that have existing diagrams you will have to transform the
data to ensure you do not try and insert duplicate id values as this is
an IDENTITY column, as well as maintaining consistency of the objectid
values as outlined above.

A slightly different bit of SQL code designed for copying the diagrams
to another server is below :-

-- View to simplify diagrams import & export.
-- This can be created in the model database so that it will
-- present in all newly created databases.
CREATE VIEW dbDiagrams
AS
SELECT id, objectid, property, value, lvalue, version
FROM dtproperties d1
WHERE EXISTS(SELECT * FROM dtproperties d2
WHERE d2.property = 'DtgSchemaOBJECT' AND
d1.objectid = d2.id)
go

In order to move database diagrams from one server to another, the data
from 'dtproperties' needs to be copied over. This will work only if the
database is identical. The data can be exported using a
DataDrivenQueryTask in DTS, BCP or using a linked server setup. The code
sample below can used to move diagrams using linked servers. Please
create the 'dbDiagrams' view in the databases on both servers.

-- [SourceServer] is the name of the linked server &
-- [SourceDb] is the name of the database.
-- Insert only rows with conflicting identity values first.
INSERT dtproperties
SELECT dg2.newobjectid, dg1.property, dg1.value, dg1.lvalue, dg1.version
FROM [SourceServer].[SourceDb].dbo.dbDiagrams dg1 JOIN
(
SELECT d1.objectid, (SELECT MAX(id) FROM dtproperties d3) +
(SELECT COUNT(*) FROM dbDiagrams d4
WHERE d4.objectid <= d2.id And
d4.property = 'DtgSchemaOBJECT') AS newobjectid
FROM [SourceServer].[SourceDb].dbo.dbDiagrams d1 JOIN dbDiagrams d2
ON d1.objectid = d2.id
WHERE d1.property = 'DtgSchemaOBJECT' And d2.property =
'DtgSchemaOBJECT'
) AS dg2
ON dg1.objectid = dg2.objectid

-- Insert the rest now using explicit identity values.
SET IDENTITY_INSERT dtproperties ON
INSERT dtproperties ( id, objectid, property, value, lvalue, version)
SELECT id, objectid, property, value, lvalue, version
FROM [SourceServer].[SourceDb].dbo.dbDiagrams d1
WHERE NOT EXISTS(SELECT * FROM dbDiagrams d2
WHERE d2.property = 'DtgSchemaOBJECT' AND
d1.objectid = d2.id)
SET IDENTITY_INSERT dtproperties OFF

http://www.sqlserverfaq.com

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

Wow, thats a lot of great info. Thanks. Can you kindly tell me where i can
fiind the DataPump task in DTS? I am running sql server 2k.

--
George Padvorac
georgep (AT) dontspamme-nwis (DOT) net

"Darren Green" <darren.green (AT) reply-to-newsgroup-only (DOT) uk.com> wrote in
message news:gXLscYB1GtL$EwJd (AT) sqldts (DOT) com...

Thanks. thats a great site.

--
George Padvorac
georgep (AT) dontspamme-nwis (DOT) net

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote