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:
/****** 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.
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::
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips