Migrating every table to a new linked server has too many prefixes Unfortunately, I can't backup and restore, since the new database is on an older version of SQL.

When I run the following script, I get this error:

    Msg 117, Level 15, State 1, Line 1
    The object name 'eih-dr01.eih.ehs.org.DBA.dbo.fp_monthly' contains more than the maximum number of prefixes. The maximum is 2.




    declare @table varchar(255),
    @sql nvarchar(max)

    declare c cursor local for


    select st.name from sys.tables st

    open c

    fetch next from c into @table

    set @sql = 'select * into [eih-dr01.eih.ehs.org].DBA.dbo.' + @table + ' from ' + @table

    print @sql
    exec sp_executesql @sql

    fetch next from c into @table

    close c
    deallocate c


Question: Which prefix is superfluous?

Actual Question Why aren't my '[',']' charachters showing up in my dynamic sql statement?
According to SELECT - INTO Clause


  You cannot create new_table on a remote server using SELECT INTO


In other words, the superfluous prefix is the remote server

You could probably achieve your goal by creating empty tables on the remote server and alter your dynamic SQL to 

INSERT INTO server.database.schema.table select from source