SQL Server snapshot replication fails when importing data from the distributor

By: Matteo Lorini   |   Comments (3)   |   Related: 1 | 2 | 3 | 4 | 5 | > Replication

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:

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.

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

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.

Next Steps

• I hope this tip is helpful if you run into the same issue that I experienced.
• Read these additional articles related to replication::
  • SQL Server Replication Scripts to get Replication Configuration Information
  • Getting additional error messaging information for SQL Server replication
  • Building a SQL Server 2005 Updatable Subscription Replication Topology
  • Transactional Replication Snapshot Issues in SQL Server

About the author

Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips