SQL Server snapshot replication fails when importing data from the distributor

By:   |   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.

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Matteo Lorini 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



Comments For This Article




Thursday, August 28, 2014 - 9:54:49 AM - Nitish Srivastava Back To Top (34314)

Hi Matteo,

 

Many thanks for your post, I had the same set up same situation same error and using the -UseInprocLoader parameter worked as a silver bullet for me.

 

Also could you help me in understanding why this clause helped, why forcing sql server to use bulk insert instead of bcp worked for us.

Would really appreciate if you could help in understanding this.

 

Once again thanks.

Cheers

Nitish Srivastava


Wednesday, June 22, 2011 - 3:21:59 PM - Matteo Lorini Back To Top (14065)

Look at the followings:

FIX: Error message when you run the BULK INSERT statement in Microsoft SQL Server 2005: "The bulk load failed. The column is too long in the data file for row <n>, column <n>"

http://support.microsoft.com/kb/942660

Have we tried this fix on the Publisher server, I see that the publisher server is on SP2, infact the customer needs to move on SP3

FIX: Error message when you run the BULK INSERT statement in Microsoft SQL Server 2005: "The bulk load failed. The column is too long in the data file for row <n>, column <n>"

http://support.microsoft.com/kb/942660


Tuesday, June 21, 2011 - 11:58:00 AM - asm1212 Back To Top (14062)

I had this same problem and could not find a lot about it over the internet and then I stummbled across this article and thought my issue would be resolved...It is the exact same problem...I have set up Snapshot Replication and when it gets to one table, it says it cant bulk import the data b/c of a sorted column blah blah blah...So I disabled replication and set it back up and before I started running it, I went into the job and added that parameter...I started the replication process and it still failed with the same error!















get free sql tips
agree to terms