Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server snapshot replication fails when importing data from the distributor


By:   |   Read Comments (3)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > 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


Last Update:






About the author


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

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

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

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!


Learn more about SQL Server tools