SQL Server – 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](
    [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, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Linked server definition

EXEC master.dbo.sp_addlinkedserver @server = N'<SERVERNAME>', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'<SERVERNAME>',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_serveroption @server=N'<SERVERNAME>', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'<SERVERNAME>', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'<SERVERNAME>', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'<SERVERNAME>', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'<SERVERNAME>', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'<SERVERNAME>', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'<SERVERNAME>', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'<SERVERNAME>', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'<SERVERNAME>', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'<SERVERNAME>', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'<SERVERNAME>', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'<SERVERNAME>', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'<SERVERNAME>', @optname=N'remote proc transaction promotion', @optvalue=N'true'

The link seems fine, I was able to copy multiple other tables data over this link, so I am pretty sure the problem is confined to the table, or maybe the data in it.

I have two copies of the database that I tried copying the data from, one production (SQL Server 2005), one a restored copy on a dev server (2008). I am trying to write the data to a SQL Server 2012 server. The table definition is application generated, I can't change it on either end.

Collations:

Both databases have a collation of Chinese_PRC_CI_AS and the columns also have Chinese_PRC_CI_AS collation in both databases.

Everything should match between the two databases, the new DB was created by restoring a backup of the source database. I am moving data over the dblink now, because the source database is now out of date because the restore was done several months ago. I am just moving data as needed to get the environments back in sync.

Windows

The servers are not running the same version of windows. The source is running windows 2003 (32bit) the destination is running windows 2012. I am not familiar enough with the data to slice it up into separate queries, for the migration all the data needs to be moved, that is all I have tried to do.

Test Queries

The following queries all worked correctly without an errors

SELECT TOP (1) [GDTXVC] from <SERVER>.<DATABASE>.<SCHEMA>.F00165;
SELECT TOP (1) [GDTXKY] FROM <SERVER>.<DATABASE>.<SCHEMA>.F00165;
INSERT INTO <SCHEMA>.F00165 ([GDOBNM], [GDTXKY], [GDLNGP], [GDTXVC]) SELECT TOP (1) [GDOBNM], [GDTXKY], [GDLNGP], [GDTXVC] FROM <SERVER>.<DATABASE>.<SCHEMA>.F00165;

I have also opened a case with Microsoft Premier support, and will try to keep this question up to date with the troubleshooting steps I take with microsoft as well as any solution that they may have/suggest.

Best Answer

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.

Local Table – Destination

Remote Server – Source Table

Linked server definition

Collations:

Windows

Test Queries

Best Answer

Related Solutions

Sql-server – Connecting Linked Server

SQL Server Hierarchical Order – Parent-Child Tree Hierarchical ORDER BY

Related Question