SQL snapshot replication fails when importing data from distributor

Problem

Snapshot replication fails when trying to import data from the distributor. You get an error message similiar to “Could not bulk load. The sorted column ‘rowversion’ is not valid. The ORDER hint is ignored.” In this tip I go over a solution to fix these issues.

Solution

Not long ago I was trying to create a snapshot replication between two SQL Server 2005 servers and I came across the following errors.

Message

2010-04-20 14:32:52.913 Category:NULL

Source:  Microsoft SQL Native Client

Number:

Message: Insert bulk failed

2010-04-20 14:32:52.913 Category:NULL

Source:  Microsoft SQL Native Client

Number:  4817

Message: Could not bulk load. The sorted column ‘rowversion’ is not valid. The ORDER hint

is ignored.

2010-04-20 14:32:52.913 Category:NULL

Source:

Number:  20253

Message: To obtain an error file with details on the errors encountered when initializing

the subscribing table, execute the bcp command that appears below.  Consult the BOL for

more information on the bcp utility and its supported options.

2010-04-20 14:32:52.913 Category:NULL

Source:

Number:  20253

Message:

bcp “DB001″.”USERAPP”.”MYTABLE” in “E:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\

MSSQL\ReplData\unc\DB001_PUB\20100419104329\MYTABLE#1.bcp” -e “errorfile” -t”\n\n” -r”\n

<,@g>\n” -m10000 -SMyTARGETSERVERNAME -T -w

The original table schema (on the publisher) is as follows:

/****** Table [USERAPP].[MYTABLE] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [USERAPP].[MYTABLE](
[rowversion] [timestamp] NOT NULL,
[MachineID] [int] NOT NULL,
[InstanceKey] [int] NOT NULL,
[DelDate] [smalldatetime] NOT NULL,
CONSTRAINT [PK__Add_Remove_Progr__7E7F0DF1] PRIMARY KEY CLUSTERED
(
[rowversion] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

This error message, “Message: Could not bulk load. The sorted column ‘rowversion’ is not valid. The ORDER hint is ignored.”, does not explain very well what the error is, so I tried to manually run (at the Subscriber) the following bcp command to see if the bcp command was able to capture a comprehensive error list inside the errorfile file. The bcp -e option redirects the error to a file. In my configuration the Distributor and Subscriber are on the same server.

bcp “DB001″.”USERAPP”.”MYTABLE” in “E:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\

MSSQL\ReplData\unc\DB001_PUB\20100419104329\MYTABLE#1.bcp” -e “errorfile” -t”\n\n” -r”\n

<,@g>\n” -m10000 -SMyTARGETSERVERNAME -T -w

Unfortunately, the bcp command failed right away creating an empty errorfile log file. I looked up the error on the internet and the only thing I was able to find was a suggestion to drop the primary key constraint from the source table. I dropped the primary key constraint from my source table and the snapshot replication worked fine, however as soon as I create the primary key constraint again, on my source table, the snapshot replication failed.

I was not able to find any other workaround to the issue, so I decided to engage Microsoft Support. Microsoft Support was able to make it work and the goal of this tip is to share with the community the trick that I have learned from Microsoft Support.

Basically, Microsoft Support made me add a parameter -UseInprocLoader to the import job in order to force SQL Server to use BULK INSERT instead of bcp.

This is how it works. On the distributer server right click on the SQL Job created during the creation of the new subscription.

trying to create a snapshot replication between two SQL Server 2005 servers and I came across the following errors

Select Step 2 “Run agent” and click on Edit and add the parameter -UseInprocLoader at the end of the configuration string as shown below.

"Run agent" and click on Edit and add the parameter -UseInprocLoader at the end of the configuration string

Once it is done, the publication needs to be reinitialized and everything works fine.

The only catch is that both SQL 2005 Servers need to be on Service Pack 3 or later, otherwise you may come across the following error.

both SQL 2005 Servers need to be on Service Pack 3

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *