Could not bulk insert. Bulk data stream was incorrectly specified as sorted.

[ocano ocano via SQLMonster.com]

hi, i am Oscar from Spain

I have a mistake in Transactional publication. The Error message is
"Could not bulk insert. Bulk data stream was incorrectly specified as
sorted"

I have checked in publisher and subscriber the command sp_helpsort and the
result is equal in both of them.

Can you help me? i can not to follow with replication configuration.

Thanks in advance

Best Regards,

Oscar Cano.

--
Message posted via http://www.sqlmonster.com

[Paul Ibison]

Is it exactly the same - perhaps one is SQL and the other
windows? Also, please check the collation of the actual
table you are replicating and compare that to the
subscriber - if they are different then there will be
issues, unless you take the collation using the article
properties, snapshot tab.
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

[ocano ocano via SQLMonster.com]

hi Paul

Thank you for answer so quickly.

How Could i check the collation in one table?
i do not know to do it this operation.

Can you help me?

Thanks and advance

Best Regards,
Oscar

[Paul Ibison]

Oscar,
unfortunately we don't know which table is causing the
problem, but possibly it is all of the tables with text
data if they have inherited the default database
collation. Anyway, right-click on a table in Enterprise
Manager and select to generate the script. Have a look
for the COLLATE part eg COLLATE COLLATE
SQL_Latin1_General_CP1_CI_AS and see if this is the
database collation on the subscriber. If not, then change
the database collation at the subscriber is the easiest
option, or alternatively on the article properties select
to take the article's collation.

[ocano ocano via SQLMonster.com]

hi Paul,

Well, i think that this property is not defined in this table.
please have a look at the following script:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[FK_CLIENTE_CONTRAT]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CONTRAT_GENRAL] DROP CONSTRAINT FK_CLIENTE_CONTRAT
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[FK_CLIENTE_CONTRAT_SEDE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CONTRAT_SEDE] DROP CONSTRAINT FK_CLIENTE_CONTRAT_SEDE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[CLIENTES_COD]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CLIENTES_COD]
GO

CREATE TABLE [dbo].[CLIENTES_COD] (
[Codigo] [numeric](7, 0) NOT NULL ,
[CIF] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Nombre] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

This is the script in Publisher Database.

[Paul Ibison]

Ocano,
you have the setting here:

[CIF] [nvarchar] (16) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Nombre] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AI NOT NULL ,

Please check the subscriber to see if the database
setting is the same or different. If it says server
default, please check the server setting.

[ocano ocano via SQLMonster.com]

hi Paul,

In the subscribe, I have dropped this table and then I have to execute the
same creation table script than publisher server.

Then I have tried to execute the Push subscription and it is failed in the
same table clientes_cod.

Thanks

[Paul Ibison]

No this won't work as the initial script on
initialization will drop the table you have created.
Please check my previous post.
Rgds,
Paul Ibison, SQL Server MVP

[sangeetha...@gmail.com]

do we have solution for this issue , I am also trying to setup transactional replication from sql 2000 to sql 2008r2 and getting this error while appliying on the subscriber .

Cannot bulk load. The bulk data stream was incorrectly specified as sorted or the data violates a uniqueness constraint imposed by the target table.
Sort order incorrect for the following two rows: primary key of first row