Enterprise Manager Single Diagram Copy Script

,

This procedure will COPY an Enterprise Manager SINGLE DIAGRAM object between databases on a given SQL Server.
This procedure takes in a source DB name, a Dest DB name, and the Diagram name.

(Exec diagram_copy 'SOURCE_DB', 'DEST_DB', 'DIAGRAM NAME')

This procedure DOES NOT have to be placed in either of the databases, you may run it from a connection to any database on the server where the script exists.
You need to have rights to the DTPROPERTIES table and it must exist. If it currently does not exist on your destination DB, you should go into EM and save a simple diagram out to create the table.

To transfer information between servers, create a new DB and setup DTPROPERTIES and copy all the diagrams to this DB. Then backup this DB and restore it to your destination server and then run this procedure there.
It would be a simple task to front end this procedure to a process that supplied the diagram names for mass movement.

The following query will return the diagram names from the current DB.
select objectid,value from dtproperties where property='DtgSchemaNAME'

This can be placed into a procedure or view to make things easier.

GENERAL INFO: Each diagram object is composed of multiple rows in the dtproperties table. The following property values are used:
DtgSchemaOBJECT - Is the primary ID for the object. All rows use this record's ID for objectid.
DtgSchemaGUID - Not sure how this is used, All my diagrams have the same GUID.
DtgSchemaNAME - Actual name of the diagram
DtgDSRefBYTES
DtgDSRefDATA
DtgSchemaBYTES
DtgSchemaDATA
The Bytes record contains the datalength of the corresponding data records's lvalue image field.

IMPORTANT!
Keep in mind that tables must be common between the databases. If relationships are different, the links may not appear the same as the source diagram. Displayed Link Line positions may move.

Tables may have differences, but if named the same

create procedure dbo.diagram_copy (@source_db varchar(256)='',
                                   @dest_db varchar(256)='', 
                                   @diagram varchar(256)='')
 as
DECLARE @er integer
DECLARE @sourceid integer
DECLARE @objectid integer 
DECLARE @ds nvarchar(800)
DECLARE @pd nvarchar(500)
BEGIN
  SET NOCOUNT ON
  SET QUOTED_IDENTIFIER ON 
  SET ANSI_NULLS ON 
  BEGIN TRAN /* Opened a new transaction */
   /* Check passed DB variables, quote to prevent injection attack and add .. */
  set @source_db=quotename(ltrim(rtrim(isnull(@source_db,''))))+'..'
  set @dest_db=quotename(ltrim(rtrim(isnull(@dest_db,''))))+'..'
  if ltrim(rtrim(@source_db))='[]..' 
    begin
      set @source_db=''
    end
  if ltrim(rtrim(@dest_db))='[]..'
    begin
      set @dest_db=''
    end
  /* we now should have quoted source db commands, safety for injection code attempts */
  set @source_db=@source_db+'dtproperties'
  set @dest_db=@dest_db+'dtproperties'
  /* full table name has now been established */
  if ltrim(rtrim(isnull(@diagram,'')))='' 
    begin
      ROLLBACK
      print 'NO DIAGRAM NAME PASSED'
      GOTO ERRORBLOCK
    end
  set @sourceid=0
  /*  We need to id the primary object id from the schema name row for the diagram name*/
  set @ds='select @objid=isnull(objectid,0) from '+ @SOURCE_DB+' where value= '+CHAR(39)+@diagram+CHAR(39)+' and property='+CHAR(39)+'DtgSchemaNAME'+CHAR(39)
  set @pd=N'@objid integer OUTPUT'
  execute sp_executesql @ds,@pd,@objid=@sourceid OUTPUT        
  if @sourceid=0 
    begin
      ROLLBACK TRAN
      print 'NO DIAGRAM FOUND FOR DIAGRAM NAME = '+upper(@DIAGRAM)
      GOTO ERRORBLOCK
    end
  /* Now test to see if this objects is in the dest db */
  set @objectid=0
  set @ds='select @objid=isnull(objectid,0) from '+ @DEST_DB+' where value= '+CHAR(39)+@diagram+CHAR(39)+' and property='+CHAR(39)+'DtgSchemaNAME'+CHAR(39)
  set @pd='@objid integer OUTPUT'
  execute sp_executesql @ds,@pd,@objid=@objectid OUTPUT        
  if @objectid>0 
    begin
      ROLLBACK tran
      print 'DIAGRAM NAME '+upper(@DIAGRAM)+' ALREADY EXISTS IN THE DESTINATION DATABASE!'
      GOTO ERRORBLOCK
    end
  /* Now we get the primary object row from source db and insert it into dest db */  
  set @ds= 'insert into '+@dest_db +' (objectid,property,value,lvalue,version,uvalue) ' +
            ' select  objectid , property,value,lvalue,version,uvalue from '+ @source_db +
            ' where objectid='+convert(varchar(20),@sourceid)+' and id= '+convert(varchar(20),@sourceid)
  execute sp_executesql @ds      
  select @er=@@rowcount /* get the rowcount into a variable for safekeeping */
  if @er !=1 
    BEGIN
      print convert(varchar(20),@ER)
      ROLLBACK TRAN
      print 'PROBLEM ON INSERT DUPLICATING DIAGRAM '+upper(@DIAGRAM)
      GOTO ERRORBLOCK
    end
  /* Now we need the id from the row that was just inserted, as it will be the objectid on all the rows, including the row just inserted */
  select @objectid=convert(integer,ident_current(@dest_db))
  if @objectid<=0 
    begin
      ROLLBACK TRAN
      print 'PROBLEM GETTING NEW ID FOR DIAGRAM '+upper(@DIAGRAM)
      GOTO ERRORBLOCK
    END  
  set @ds='update '+@dest_db+' set objectid = '+convert(varchar(20),@objectid)+' where id='+convert(varchar(20),@objectid)
  execute sp_executesql @ds      
  /* Now update the row we just processed so that it shows the objectid=id. */
  select @er=@@rowcount /* get the rowcount into a variable for safekeeping */
  if @er !=1 or @objectid <=0
    BEGIN
      ROLLBACK TRAN
      print 'PROBLEM UPDATING DUPLICATE DIAGRAM '+upper(@DIAGRAM)
      GOTO ERRORBLOCK
    END
  /* Now duplicate the remaining rows from source db */
  set @ds= 'insert into '+@dest_db+'(objectid,property,value,lvalue,version,uvalue) '+
           'select objectid= '+convert(varchar(20),@objectid)+' , property,value,lvalue,version,uvalue from '+ @source_db+
           ' where objectid='+convert(varchar(20),@sourceid)+' and id!='+ convert(varchar(20),@sourceid)
  /* Now we need the id from the row that was just inserted, as it will be the objectid on all the rows, including the row just inserted */
  execute sp_executesql @ds
  select @er=@@rowcount /* get the rowcount into a variable for safekeeping */
  if @er <= 0 
    BEGIN
      ROLLBACK TRAN
      print 'PROBLEM DUPLICATING DETAILS FOR DIAGRAM '+upper(@DIAGRAM)
      GOTO ERRORBLOCK
    END
  print 'DIAGRAM '+upper(@diagram)+' WAS ADDED TO '+upper(@source_db)+' AS OBJECT ID '+convert(varchar(20),@objectid)
  COMMIT TRAN /* close the transaction out */
  SET QUOTED_IDENTIFIER OFF 
  SET ANSI_NULLS ON
  RETURN

  ERRORBLOCK:
   begin
     SET QUOTED_IDENTIFIER OFF 
     SET ANSI_NULLS ON
     RETURN
   end
END
END

Rate

Share

Share

Rate