Error while copying data over linked server

I am trying to copy the contents of 1 table from one database to another over a linked server.

I am using the following query to do so

insert into [Schema].[Table] select * from [Server].[Database].[SCHEMA].[Table]

The query is failing with the following error.


OLE DB provider 'SQLNCLI11' for linked server 'ServerName' returned invalid data for column '[Server].[DATABASE].[SCHEMA].[TABLE].GDTXVC'.

Same error if I list the column names, I didn't try the select * on its own, because its a 3 million + row table.

I have checked and verified that the table is the same in both databases, the column that is being reported in the error is a TEXT data type in both tables, and the column accepts NULL in both as well.

Just to rule it out, I ran DBCC CHECKTABLE() with data_purity against the table in question, and it returned no errors either. I am not sure what I am missing, or where else I should be looking.

Local Table - Destination

CREATE TABLE [CRPDTA].[F00165](
    [GDOBNM] [char](10) NOT NULL,
    [GDTXKY] [varchar](254) NOT NULL,
    [GDLNGP] [char](2) NOT NULL,
    [GDTXPO] [varchar](254) NULL,
    [GDCRTU] [char](10) NULL,
    [GDDQE] [numeric](18, 0) NULL,
    [GDTENT] [float] NULL,
    [GDMUSE] [char](10) NULL,
    [GDUPMJ] [numeric](18, 0) NULL,
    [GDTDAY] [float] NULL,
    [GDEFTJ] [numeric](18, 0) NULL,
    [GDEXDJ] [numeric](18, 0) NULL,
    [GDPNTC] [char](1) NULL,
    [GDISTM] [char](1) NULL,
    [GDISFL] [char](1) NULL,
    [GDTXTL] [float] NULL,
    [GDIMGL] [float] NULL,
    [GDOLEL] [float] NULL,
    [GDMSCL] [float] NULL,
    [GDFUTL] [float] NULL,
    [GDTXVC] [text] NULL,
 CONSTRAINT [F00165_PK] PRIMARY KEY CLUSTERED 
(
    [GDOBNM] ASC,
    [GDTXKY] ASC,
    [GDLNGP] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Remote Server - Source Table

```
CREATE TABLE [PRODDTA].[F00165](
[

It is likely your problems stem from different implementations of Code Page 936 in the different Windows OS versions involved.

I suggest using the bcp utility with the -N option to bulk export and import the data via a file.

The -N option:


Performs the bulk-copy operation using the native (database) data types of the data for noncharacter data, and Unicode characters for character data.

See Use Unicode Native Format to Import or Export Data (SQL Server) in the product documentation.


Unicode native format is helpful when information must be copied from one Microsoft SQL Server installation to another. The use of native format for noncharacter data saves time, eliminating unnecessary conversion of data types to and from character format. The use of Unicode character format for all character data prevents loss of any extended characters during bulk transfer of data between servers using different code pages. A data file in Unicode native format can be read by any bulk-import method.

Example command lines I used to test this method on local 2008 and 2012 instances:

-- Export data to file
bcp Sandpit.dbo.Test out c:\temp\test.bcp -N -S .\SQL2K8 -T

-- Import data from file
bcp Sandpit.dbo.Test in c:\temp\test.bcp -N -S .\SQL2012 -T


Ensure you meet the Prerequisites for Minimal Logging in Bulk Import for best performance.

StackExchange Database Administrators Q#198696, answer score: 3