Results 1 to 7 of 7

Thread: Table Migration

  1. #1
    Join Date
    Jul 2003
    Posts
    37

    Angry Unanswered: Table Migration

    A whole bunch of questions!!!!! I'm trying to migrate data from one db to another on a different server... here is the stored procedure code that I have

    CREATE Procedure sp_TableDef @dbname varchar(100) = '',
    @Copy int = 0,
    @CopySrv varchar(50) = NULL,
    @CopyDB varchar(50) = NULL
    As
    Declare @Sql varchar(8000),
    @table varchar(500),
    @prevtable varchar(500),
    @coldef varchar(1000)

    If @dbname = '' Set @dbname = db_name()

    If @Copy <> 0 And @CopySrv Is Null
    Begin
    Print 'Incorrect Usage'
    Print 'A value for @CopySrv MUST be entered if copying files to a new database.'
    Return
    End
    Else If @Copy <> 0 And @CopySrv Is Not NULL And @CopyDB Is Null
    Begin
    Set @CopyDB = @dbname
    End

    Set @Sql = "Set NoCount ON "
    Set @Sql = @Sql + "Use " + @dbname + " "
    Set @Sql = @Sql + "Select left(table_name, 35)'Table Name' "
    Set @Sql = @Sql + ",column_name + ' ' + Upper(data_type) + "
    Set @Sql = @Sql + " Case When CharIndex('char', data_type) > 0 Or CharIndex('binary', data_type) > 0 Then "
    Set @Sql = @Sql + " '(' + Case When typ.scale Is Null Then Cast(character_maximum_length As Varchar(10)) "
    Set @Sql = @Sql + " Else Cast(typ.length As Varchar(10)) End + ') ' "
    Set @Sql = @Sql + " Else "
    Set @Sql = @Sql + " Case When typ.scale > 0 And CharIndex('money', data_type) <= 0 And CharIndex('datetime', data_type) <= 0 Then "
    Set @Sql = @Sql + " '(' + Cast(numeric_precision As Varchar(3)) + ', ' + Cast(numeric_scale As varchar(3)) + ') ' "
    Set @Sql = @Sql + " Else ' ' End "
    Set @Sql = @Sql + " End + "
    Set @Sql = @Sql + " Case When is_nullable = 'Yes' Then 'NOT NULL ' "
    Set @Sql = @Sql + " Else '' End + "
    Set @Sql = @Sql + " Case When column_default Is Not Null Then 'DEFAULT' + column_default + ' ' "
    Set @Sql = @Sql + " Else "
    Set @Sql = @Sql + " Case When ColumnProperty(object_id(table_name), column_name, 'IsIdentity') = 1 Then 'IDENTITY(' + Cast(Ident_Seed(table_name) As Varchar(50)) + ', ' + Cast(Ident_Incr(table_name) As Varchar(50)) + ') ' "
    Set @Sql = @Sql + " Else '' End "
    Set @Sql = @Sql + " End'ColumnDefinition' "
    Set @Sql = @Sql + ", ordinal_position "
    Set @Sql = @Sql + "Into ##TableDef "
    Set @Sql = @Sql + "From information_schema.columns col Inner Join master..systypes typ ON col.data_type = typ.[name] "
    Set @Sql = @Sql + "Where table_name In (Select distinct table_name From information_schema.tables Where table_type = 'Base Table') "
    Set @Sql = @Sql + " And table_name <> 'dtproperties' "
    Set @Sql = @Sql + "Order By table_name, ordinal_position "

    Exec (@sql)

    If @Copy = 0
    Begin
    Select *
    From ##TableDef
    Order By [Table Name], ordinal_position
    End
    Else
    Begin
    Declare Col_Cursor Cursor For
    Select [Table Name]
    , ColumnDefinition
    From ##TableDef
    Order By [Table Name], ordinal_position

    Open Col_Cursor

    If @@Cursor_Rows = 0
    Begin
    Close Col_Cursor
    Deallocate Col_Cursor
    Print 'Error Opening Cursor To Build Create Table statement'
    Drop Table ##TableDef
    Return
    End

    Set @prevtable = ''
    Set @sql = ''
    Fetch Next From Col_Cursor Into @table, @coldef

    While @@Fetch_Status = 0
    Begin
    If @prevtable <> @table
    Begin
    If @sql <> ''
    Begin
    Set @sql = Replace(Left(@Sql, Len(@Sql) - 2) + ")", ' , ', ', ')
    Exec (@sql)
    End
    Set @sql = "Create Table [" + @CopySrv + "]." + @CopyDB + ".dbo." + @table + " ("
    Set @prevtable = @table
    End

    Set @sql = @sql + @coldef + ', '
    Set @prevtable = @table

    Fetch Next From Col_Cursor Into @table, @coldef
    End
    Close Col_Cursor
    Deallocate Col_Cursor

    Set @sql = Replace(Left(@sql, Len(@sql) - 2) + ")", ' , ', ', ')
    Exec (@sql)
    End

    Drop Table ##TableDef

    ----------------------
    As for the parameters,
    @dbname - Defaults to the current database. This is the database containing the tables to copy.
    @copy - Defaults to 0. This determines whether or not to just view the column information, or create the tables on a different database. @CopySrv must also be populated if this is set to 1.
    @CopySrv - Defaults to NULL. SQL Server containing the database to copy the tables to.
    @CopyDB - Defaults to @dbname. Name of the database to copy the tables to.

    I get two errors upon executing this statement and entering values in the input parameters:
    1) the associated statement servername.dbname.dbo contains more than the maximum number of prefixes... maximum is 2.
    2) an error regarding batch 1 line 3 (referring to the input parameter @CopySrv varchar(50) = NULL)

    I am assuming that the second error is associated with the first (unless anyone has any other ideas why this is occuring). So I'm out to tackle the first one... I know WHY this is saying that I've exceeded the maximum number of prefixes however, I need to figure out a way around it so it. I am absolutely clueless... am I out of luck or does anyone have any ideas?????

    I need to figure out how to migrate other forms such as users and roles and sps but this is where I need to start

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why aren't you just using Enterprise manager to script the database? Or DTS to transfer the data?

    blindman

  3. #3
    Join Date
    Jul 2003
    Posts
    37
    That was my suggestion in the first place... we have a tool located here called DBArtisan (not sure if you are familiar with it) however it already has a migration tool and that is what we are currently using to migrate tables, indexes, views, sps etc from one db to another... they want a process which is faster which will execute everything all at one time... with dts and artisan, it doesn't help when we want to transfer tables, indexes, views, roles, sps all at the same time... the script in a stored procedure will migrate data by the hundreds not depending on type... the table sp above is a start and once I have that working its just a small piece of the puzzle... i understand what you are saying about dts, however it doesn't help us when we need to migrate sps, users, and roles. Artisan takes too much time.

    Any ideas to fix that create table statement?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Throw a select @SQL statement in just before your final Exec (@sql) statement to see what is actually being run. Show me what the results are, or paste it into query analyzer and run a syntax check on it.

    You can use Enterprise Manager to script out all the tables, procedures, functions, and other object definitions, including database roles. I'm sorry, but it looks to me like you're not just reinventing the wheel, you're reinventing the car.

    blindman

  5. #5
    Join Date
    Jan 2004
    Location
    North Carolina
    Posts
    6
    There is one problem I've seen so far with scripting from EM.

    Views and sprocs don't come out in dependency order.

    When I use EM to script views to a newly replicated database, I have to specificly choose don't create drop code then run the script over and over until all the "object not found" errors are turned into object already exists.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes. That is a minor pain.

    You can still have the drop code, though. Each drop should occur immediately before each create.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    Yes. That is a minor pain.

    The master of the under-statement...

    Why not just dump and restore?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •