August 1, 2002 at 8:32 am
Does anyone know of a way to DTS diagrams from one database to another on the same server (not a separate server)? THX
NOTE: I was provided with info (see below) which corrupted my diagram directory.
How can we create entity-relationship (ER) diagrams on our primary development SQL Server 7.0 system, then move the diagrams to another server?
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.
August 1, 2002 at 8:40 am
If I understand your question correctly you want to move one or more DTS diagrams from one database to another within the same sql server. DTS Diagrams reside on the server, not with a database as I understand it...the script you were given may very well work when moving them to another server, but would not apply if attempting to move them WITHIN the same server as they cannot be moved within a server...I hope this helps -
Michael
PS - If I am wrong about this, perhaps someone could set me straight as it would be good to know. Thank you!
Michael Weiss
Michael Weiss
August 1, 2002 at 8:42 am
Sorry! Please ignore my earlier response...I went back and re-read your question and realize that you were not asking about DTS Diagrams, but using DTS to move a database diagram...my aplogies!
quote:
If I understand your question correctly you want to move one or more DTS diagrams from one database to another within the same sql server. DTS Diagrams reside on the server, not with a database as I understand it...the script you were given may very well work when moving them to another server, but would not apply if attempting to move them WITHIN the same server as they cannot be moved within a server...I hope this helps -Michael
PS - If I am wrong about this, perhaps someone could set me straight as it would be good to know. Thank you!
Michael Weiss
Michael Weiss
Michael Weiss
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply