April 21, 2004 at 9:16 pm
Hi
I copied dtproperities table through dts from my old db to new db,All the old diagrams came but the problem is when i create new diagram in my new db i t throws an error of "id and value cant be null" bcz it is a composite key in this table and does not save the diagram but if i allow null to that column it saves but takes null in these columns and does not show the diagram although create all relationship .
Plz suggest .
thanks
sachin
April 23, 2004 at 6:43 pm
You may wish to try the following approach outlined in the article described below. Note I sourced the info from this site.
In SQL Server 7.0, diagrams are stored in the dtproperties system table in each database. This system table has an identity field in it, so to transfer over the information from the source database, we will need to turn IDENTITY_INSERT to ON. This process can't be done to a SQL Server system table however. What we need to do next is "trick" SQL Server to thinking that dtproperties is actually a user table. Connect to the database that you'd like to transfer the diagram to and make the dtproperties table a user table. You can do this by running the following query:
UPDATE sysobjects
SET xtype = 'U'
WHERE name = 'dtproperties'
This is a great time to take a break and look at something interesting in SQL Server. If you go to Enterprise Manager, and look at the list of tables in the NewNorthwind database, you will see that dtproperties is still flagged as a system table, even after the adjustment. The table's behaviour though is of a user table. The reason for this is that dtproperties is actually hard-coded as a system table in Enterprise Manager.
Next, you will need to set the IDENTITY_INSERT property to true. This can be done with the following syntax:
SET IDENTITY_INSERT dtproperties ON
Setting the identity to true allows you to transfer into the table the necessary information and override the ID field. You will now need to find out which diagrams you will want to transfer. Use the following query to list all the fields in the dtproperties table.
SELECT id, objectid, property, value, lvalue, version
FROM northwind.dbo.dtpropertiesEach diagram has seven rows assigned to it. Abbreviated results are below:
id objectid property value
----------- ----------- ------------------- ---------------
6 6 DtgSchemaOBJECT NULL
7 6 DtgSchemaNAME Relationships
8 6 DtgSchemaGUID {EA3E6.....}
9 6 DtgSchemaBYTES 14336
10 6 DtgSchemaDATA NULL
11 6 DtgDSRefBYTES 762
12 6 DtgDSRefDATA NULL
(7 row(s) affected)
To find which diagram you'd like to transfer, look in the property column for the DtgSchemaNAME value. Once you find that value, look one more column over and see that the name of diagram is Relationships. The objectid for relationships is 6 also. So, you will want to transfer over all records with the objectid of 6 to the destination database. Before doing this, make sure that the destination database does not have an object with the identifier of 6 and watch that id column as well. Once that is established, use the following syntax to transfer the diagram:
INSERT INTO northwindnew.dbo.dtproperties
(id, objectid, property, value, lvalue, version)
SELECT id, objectid, property, value, lvalue, version
FROM northwind.dbo.dtproperties
It's now time for cleanup. First check your results. You should now see the Relationships diagram in the northwindnew database. Set the INDENTITY_INSERT property back to false now:
SET IDENTITY_INSERT dtproperties ON
Don't forget to also set the dtproperties table back to a system table.
UPDATE sysobjects
SET xtype = 'S'
WHERE name = 'dtproperties'
Finally, change the server settings to disallow any system catalog modifications. As always, before you try any of this backup your database and never experiment on a production server. This example in the future will be obsolete once you upgrade to Service Pack 2 or SQL Server 2000.
April 27, 2004 at 3:18 am
thanks ,but that was the scenario for sql7.0 but now plz suggest how should i proceed the scnerio is
it is not showing any new diagram made by some developers bcz id is null and object id is null value is null and version is null,plz suggest how should i trouble shoot it,bcz i will have to recover those new diagrams ,also suggest should i convert dtproperties to user table and then identity insert into true ,plz suggest
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply