Entity Relationship Diagram - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Entity Relationship Diagram

Author

Topic

alphaone
Starting Member

4 Posts

Posted - 2005-12-18 : 23:06:11

Hi folks

I'am using a set of scripts to distribute a SQL 2000 database. The script queries the database and generates one sql script defining objects and one for populating data.

The only trouble being that the relationship diagram is getting lost. Replication aside, I also need to access these entries programmatically so that the client is aware of relationships.

Presumably each relationship is stored in a system table somewhere...

Any which one it is? I cant find it!

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-18 : 23:13:46

Hi,
Check for entries in dtproperties table. its the dtproperties table which carries information about diagrams.

alphaone
Starting Member

4 Posts

Posted - 2005-12-18 : 23:17:17

quote:
Originally posted by shallu1_gupta

Hi,
Check for entries in dtproperties table. its the dtproperties table which carries information about diagrams.

Nice one, thanks. I new it must be in there somewhere!

alphaone
Starting Member

4 Posts

Posted - 2005-12-18 : 23:34:24

Ohh this looks nice (simple one 2 many rel):

1 1 DtgSchemaOBJECT <Binary> 0
2 1 DtgSchemaGUID {EA3E6268-D998-11CE-9454-00AA00A3F36E} {EA3E6268-D998-11CE-9454-00AA00A3F36E} <Binary> 0
3 1 DtgSchemaNAME DIAGRAM1 DIAGRAM1 <Binary> 0
4 1 DtgDSRefBYTES 404 404 <Binary> 0
5 1 DtgDSRefDATA <Binary> 0
6 1 DtgSchemaBYTES 6656 6656 <Binary> 0
7 1 DtgSchemaDATA <Binary> 0

At least copying it shouldn't be too tricky!

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-18 : 23:43:52

you can go thru this link as well..
it tells about moving diag from sql 7.0 to 2000 same applies for 2000 to 2000 as well
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q320125

alphaone
Starting Member

4 Posts

Posted - 2005-12-19 : 00:21:55

quote:
Originally posted by shallu1_gupta

you can go thru this link as well..
it tells about moving diag from sql 7.0 to 2000 same applies for 2000 to 2000 as well
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q320125

thanks... though i think i may need to do it via a stored procedure so that it can be distributed using SQL scripts.

This is how northwinds does it:

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

I think the main problem is going to be parsing the relationships. I was hoping to make the client backend independant, but OLE DB doesnt appear to support relationships, therefore i assume that any soulution that relies on querying dtproperties will restrict the client to SQL Server only. I suppose one option might be to create my own table that defines the relationships, though it might make maintainance a bit awkward!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources