Sql-server – Copying Partitioned table data to another table on different SQL Server

I did some more research and found a couple of useful articles with best practices regarding table partitioning and full-text searching on large tables. As I haven't received any answers to this question, I thought I'd post what I've found here for future reference.

I'm still reading those, but for what I've already read, I've found both of them useful and relevant to this particular situation:

SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned:

The observations in this paper are based on tests run in the SQL Server Customer Lab for a customer who needed to scale up full-text search to a much greater potential volume. The paper describes the customer scenario, provides an overview of SQL Server 2005 full-text concepts that bear on the results, and offers lessons learned and recommendations for using full-text queries on large catalogs.

Partitioned Tables and Indexes in SQL Server 2005

Summary: Table-based partitioning features in SQL Server 2005 provide flexibility and performance to simplify the creation and maintenance of partitioned tables. Trace the progression of capabilities from logically and manually partitioning tables to the latest partitioning features, and find out why, when, and how to design, implement, and maintain partitioned tables using SQL Server 2005. (41 printed pages)

Below is a complete script that works as expected without the error.

:setvar IsArchive ""
:setvar Suffix _Partitioned

CREATE SCHEMA Orders;
GO

CREATE SCHEMA ArchivedOrders;
GO

CREATE SEQUENCE Orders.NextOrderId;
GO

CREATE PARTITION FUNCTION OrderIdPartitionFunction(bigint) AS
    RANGE RIGHT FOR VALUES();
GO

CREATE PARTITION SCHEME OrderIdPartitionScheme AS 
    PARTITION OrderIdPartitionFunction ALL TO ([PRIMARY]);
GO

CREATE TABLE [$(IsArchive)Orders].[Order$(Suffix)](
    [OrderId] [BIGINT] NOT NULL DEFAULT(NEXT VALUE FOR Orders.NextOrderId),
    [OtherStuff] [VARCHAR](100) NOT NULL,   
 CONSTRAINT [PK_Order$(Suffix)] PRIMARY KEY CLUSTERED 
(
    [OrderId] desc
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
) ON OrderIdPartitionScheme(OrderId) WITH (DATA_COMPRESSION=PAGE);
GO

:setvar IsArchive Archived
:setvar Suffix _Archived

CREATE TABLE [$(IsArchive)Orders].[Order$(Suffix)](
    [OrderId] [BIGINT] NOT NULL DEFAULT(NEXT VALUE FOR Orders.NextOrderId),
    [OtherStuff] [VARCHAR](100) NOT NULL,   
 CONSTRAINT [PK_Order$(Suffix)] PRIMARY KEY CLUSTERED 
(
    [OrderId] desc
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
) ON OrderIdPartitionScheme(OrderId) WITH (DATA_COMPRESSION=PAGE);
GO

ALTER TABLE Orders.Order_Partitioned SWITCH PARTITION 1 
      TO ArchivedOrders.Order_Archived PARTITION 1;
GO