Database Migrator

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

Are you trying to contact a linked server from MsSQL?

That wasn't my original intention, but would that work if I created a linked server connection? I don't know much about that however. Basically I'm trying to take tables from serverA in DatabaseA and my intention is to recreate those same tables (without the data just with column names data types etc) in ServerB in DatabaseB. Would a linked server allow me to do this without exceeding the maximum number of prefixes as stated before? And if so, how do I approach creating a linked server with those specs in mind as I haven't ever done so in the past.