Post reply

Diagrams

  • March 9, 2006 at 9:49 am

    #167325

    Hi all,

    how can I transfer diagrams from a source database to destination database ?

    I have tried throught DTS but it doesn't work and throught a backup I lose the diagrams in the destinatation database.

    Thanks

  • March 9, 2006 at 10:44 am

    #625762

    See this MS KB Article: How To Move a Database Diagram

    http://support.microsoft.com/kb/320125/en-us



    Mark

  • March 10, 2006 at 9:02 am

    #626009

    If you want to import just ONE diagram by name.. do the following using Query Analyzer..

    /* Create this table if it doesn't already exist in your target DataBase

    CREATE TABLE [dtproperties] (

    [id] [int] IDENTITY (1, 1) NOT NULL ,

    [objectid] [int] NULL ,

    [property] [varchar] (64) COLLATE database_default NOT NULL ,

    [value] [varchar] (255) COLLATE database_default NULL ,

    [uvalue] [nvarchar] (255) COLLATE database_default NULL ,

    [lvalue] [image] NULL ,

    [version] [int] NOT NULL DEFAULT (0),

    CONSTRAINT [pk_dtproperties] PRIMARY KEY CLUSTERED

    (

    [id],

    [property]

    ) ON [PRIMARY]

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    */

    -- Now, from the NEW Server.. in the TARGET DB..

    -- and you will need a linked Server to the SourceServerName.DbName

    INSERT

    INTO dbo.dtproperties

    (

    objectid, property, uvalue, value, lvalue, version)

    SELECT

    objectid

    , property, uvalue, value, lvalue, version

    FROM

    SourceServerName.DbName.dbo.dtproperties

    WHERE

    objectid =

    (SELECT objectid FROM SourceServerName.DbName.dbo.dtproperties

    WHERE property = 'DtgSchemaName'AND value = 'MyDiagramName')

     

    - hth

    Mark

  • March 13, 2006 at 1:40 am

    #626165

    Thanks for your help.

    I moved the diagrams perfectly.

    Regards

    UPDATE:

    I have just tried to move another diagram to another database but in the "Select Source Table and Views" page I can not select the dtproperties table.

    If I go to EM I can see this table (dtproperties) as system table in the destination databse but in the Export Data proccess I can not select it when I do click in the Results destination

    Must I delete the table and to create it again?

    I do the DTS proccess by sa.

    Any ideas for this problem?

    Thanks

  • March 13, 2006 at 7:42 am

    #626208

    I always do this in Query Analyzer.. I just keep the script handy in my Tips_n_Tricks folder and use it when I need it.

    Doing this in DTS.. I dunno.. it *should work the same... however if you are running the DTS from a JOB, that will change things somewhat as the DTS is then run under whatever credentials SqlServer Agent is running under, so you could have a permissions issue there but you should also be getting some kind of error message.

    Once I have the dtProperties table, I never have to create it again, as the rows must exist there for the the diagram to exist.  Can you try running the script in QA?

     

    - Mark

  • March 14, 2006 at 2:24 am

    #626432

    I ran the script in QA but I had the result 0 rows affected when I inserted the values into dtproperties table in the destination database.

    Firstly, I created the dtproperties table in the destination database and it was created without problems. Later, I tried to insert into the table one diagram (I have three in the source database) but I receive always the result '0 rows affected'.

    I don't understand that because when I return all rows from this table in the source database I can see the diagrams.

    On the other hand I utilized the DTS because I saw the microsoft article about 'How to move a database diagram' and I moved all diagrams at the same time (in another database), but your script is good because I can only move one diagram.

    I have tested with new databases that I have created but the results are the same in QA

    Thanks for your help

     

  • March 14, 2006 at 7:30 am

    #626497

    Try running just the SELECT portion of the script.  Do you get any rows returned?  You're going to have to troubleshoot this one step at a time.  The script always works for me.  Be sure of the EXACT spelling of the diagram name that you are specifying in your WHERE clause.  Other than that, I don't know what could be wrong.

    - Mark

  • March 15, 2006 at 2:51 am

    #626705

    Hi Mark,

    I've just seen the error to move my diagrams one per one with your script.

    Firstly, I ran just de SELECT portion as you told me and the result was always '0 rows affected' but if I ran select * from dtproperties I could see all rows with my diagrams.

    The problem was in the WHERE clause "'property = 'DtgSchemaName'" instead of diagram name because of my server is case sensitive then I saw in the result of QA that property value was DtgSchemaNAME.

    That was the problem.

    Thanks for your help.

    Regards

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply