Order of Articles Replicated

Is there any way to specify the order of the articles replicated? What is happending is my merge replication is erroring because a table is being replicated that contains a computed column based on a user-defined function, but the user-defined fuction hasn't been replicated yet.

But I'm not sure replicating the user-defined functions first would solve the problem because they would probably blow up because the tables upon which they are based haven't been replicated yet.

So, how has anyone else solved this?

Thanks...

Scott

You can specify a script to run before or after the snapshot is applied. Create a script that checks to see if your function exists and create it if it does not. Then set that to run before the snapshot.

Go to the properties of your publication and select Subscription Options see section Run additional scripts.

I am not sure you can specify the order of the deployment but this would solve your probem.

Martin

|||Another option is to use sp_addscriptexec for in-flight scenarios.|||But to answer your original question, you can specify article ordering of tables but not other types of objects like views, functions, procs, etc. We're looking to fix this.

Order of Articles Replicated

Is there any way to specify the order of the articles replicated? What is happending is my merge replication is erroring because a table is being replicated that contains a computed column based on a user-defined function, but the user-defined fuction hasn't been replicated yet.

But I'm not sure replicating the user-defined functions first would solve the problem because they would probably blow up because the tables upon which they are based haven't been replicated yet.

So, how has anyone else solved this?

Thanks...

Scott

You can specify a script to run before or after the snapshot is applied. Create a script that checks to see if your function exists and create it if it does not. Then set that to run before the snapshot.

Go to the properties of your publication and select Subscription Options see section Run additional scripts.

I am not sure you can specify the order of the deployment but this would solve your probem.

Martin

|||Another option is to use sp_addscriptexec for in-flight scenarios.|||But to answer your original question, you can specify article ordering of tables but not other types of objects like views, functions, procs, etc. We're looking to fix this.sql

Oracle to SQL Server Transactional Replication Error

Hi,

I'd appreciate any help i can get here. I'm working with an MS SQL Server 2005 SP2 db on a Windows 2003 OS. I set up replication from an Oracle 10g instance a few months ago, and everything was working until i installed SP2 for SQL Server.

I get the following error:

2007-06-06 16:06:22.488 Status: 4096, code: 20024, text: 'Initializing'.

2007-06-06 16:06:22.488 The agent is running. Use Replication Monitor to view the details of this agent session.

2007-06-06 16:06:23.082 Status: 0, code: 1008, text: 'ORA-01008: not all variables bound
'.

2007-06-06 16:06:23.082 ORA-01008: not all variables bound

2007-06-06 16:06:23.082 Status: 0, code: 22037, text: 'Heterogeneous Logreader encountered an error in call to LoadReplCmds when processing state 'PROCESSSYNCDONE'.'.

I can get snapshot replication to work just fine, but transactional replication throws up the error above.

Anyone with any ideas on how i can proceed?

Thanks in advance!

Regards,

Rama.

Some additonal log info:

2007-06-06 17:46:26.492 Microsoft SQL Server Log Reader Agent 9.00.3042.00
2007-06-06 17:46:26.492 Copyright (c) 2005 Microsoft Corporation
2007-06-06 17:46:26.492
2007-06-06 17:46:26.492 The timestamps prepended to the output lines are expressed in terms of UTC time.
2007-06-06 17:46:26.492 User-specified agent parameter values:
-Publisher TCADEV
-Distributor ATDSEADSQL1
-DistributorSecurityMode 1
-Continuous
-OutputVerboseLevel 4
2007-06-06 17:46:26.571 Parameter values obtained from agent profile:
-pollinginterval 5000
-historyverboselevel 1
-logintimeout 15
-querytimeout 1800
-readbatchsize 500
-readbatchsize 500000
2007-06-06 17:46:26.571 Connecting to OLE DB Publisher at datasource: 'ATDSEADSQL1', location: '', catalog: 'distribution', providerstring: '' using provider 'SQLNCLI'
2007-06-06 17:46:26.571 OLE DB Publisher: ATDSEADSQL1
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: distribution
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-06-06 17:46:27.196 Status: 4, code: 22261, text: 'Request to publisher TCADEV: select DISTRIBUTOR_PUBLISHERNAME, DISTRIBUTOR_DISTRIBUTORNAME, DISTRIBUTOR_DISTRIBUTIONDB, DISTRIBUTOR_STATUS, DISTRIBUTOR_PRODUCTVERSION FROM MSSQLSERVERDISTRIBUTOR '.
2007-06-06 17:46:27.196 OLE DB Publisher: ATDSEADSQL1
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: distribution
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-06-06 17:46:27.196 OLE DB Publisher 'ATDSEADSQL1': select @.@.SERVERNAME
2007-06-06 17:46:27.196 Connecting to OLE DB DISTOLE at datasource: 'ATDSEADSQL1', location: '', catalog: 'distribution', providerstring: '' using provider 'SQLNCLI'
2007-06-06 17:46:27.211 OLE DB DISTOLE: ATDSEADSQL1
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: distribution
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-06-06 17:46:27.211 OLE DB DISTOLE: ATDSEADSQL1
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: distribution
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-06-06 17:46:27.211 OLE DB DISTOLE 'ATDSEADSQL1': select datasource, srvid from master..sysservers where upper(srvname) = upper(N'TCADEV')
2007-06-06 17:46:27.211 OLE DB Publisher 'ATDSEADSQL1': sp_MSgetversion
2007-06-06 17:46:27.211 Status: 4096, code: 20024, text: 'Initializing'.
2007-06-06 17:46:27.211 The agent is running. Use Replication Monitor to view the details of this agent session.
2007-06-06 17:46:27.211 Repl Agent Status: 3
2007-06-06 17:46:27.211 OLE DB DISTOLE 'ATDSEADSQL1': sp_MSget_last_transaction @.publisher_id = 2, @.publisher_db = N'distribution', @.for_truncate = 0x0
2007-06-06 17:46:27.211 OLE DB DISTOLE 'ATDSEADSQL1': sp_MSquery_syncstates 2, N'distribution'
2007-06-06 17:46:27.211 Status: 4, code: 22262, text: 'Request to distributor ATDSEADSQL1: begin declare @.match int, @.retcode int execute @.retcode = sys.sp_checkOraclepackageversion 'TCADEV', @.match OUTPUT if (@.retcode <> 0 or @.@.error <> 0 or @.match = 0) select 0 else select 1 end'.
2007-06-06 17:46:27.227 Status: 4, code: 22262, text: 'Request to distributor ATDSEADSQL1: begin SET DEADLOCK_PRIORITY LOW exec sys.sp_IH_LR_GetCacheData 'TCADEV' end'.
2007-06-06 17:46:27.242 Status: 4, code: 22261, text: 'Request to publisher TCADEV: select Publisher_Name, Publisher_ID, RAWTOHEX(Publisher_LSN), Publisher_HasChanges, Publisher_PollInProcess, Publisher_SyncDones, Publisher_InReconciliation, Publisher_SyncInits, Publisher_RowCntValidations, Publisher_TableChanges, Publisher_XactSetBeginTime, Publisher_Timestamp, Publisher_GUID, Publisher_TracerTokens, Publisher_CurrentPollid from HREPL_Publisher'.
2007-06-06 17:46:27.242 Status: 4, code: 22261, text: 'Request to publisher TCADEV: Select e.Event_Publication_ID, e.Event_Article_ID, e.Event_EntryTime from HREPL_Event e, HREPL_Poll p WHERE p.Poll_POLLID = : Pollid AND p.Poll_ROWID = CHARTOROWID(e.ROWID) AND e.Event_Operation = 6 ORDER BY e.Event_Seq'.
2007-06-06 17:46:27.242 Status: 0, code: 22261, text: 'Request to publisher TCADEV: Select e.Event_Publication_ID, e.Event_Article_ID, e.Event_EntryTime from HREPL_Event e, HREPL_Poll p WHERE p.Poll_POLLID = : Pollid AND p.Poll_ROWID = CHARTOROWID(e.ROWID) AND e.Event_Operation = 6 ORDER BY e.Event_Seq'.
2007-06-06 17:46:27.242 Request to publisher TCADEV: Select e.Event_Publication_ID, e.Event_Article_ID, e.Event_EntryTime from HREPL_Event e, HREPL_Poll p WHERE p.Poll_POLLID = : Pollid AND p.Poll_ROWID = CHARTOROWID(e.ROWID) AND e.Event_Operation = 6 ORDER BY e.Event_Seq
2007-06-06 17:46:27.242 Repl Agent Status: 6
2007-06-06 17:46:27.242 Status: 0, code: 1008, text: 'ORA-01008: not all variables bound
'.
2007-06-06 17:46:27.242 Status: 0, code: 22037, text: 'Heterogeneous Logreader encountered an error in call to LoadReplCmds when processing state 'PROCESSSYNCDONE'.'.
2007-06-06 17:46:27.242 Disconnecting from OLE DB DISTOLE 'ATDSEADSQL1'
2007-06-06 17:46:27.258 Disconnecting from OLE DB Publisher 'ATDSEADSQL1'

|||yeah, it's probably an upgrade bug somewhere, let me see if there are any easy workarounds, if not you might have to start from scratch or call CSS.|||

Before we say it's an upgrade bug can you tell us what happened up to the point you saw the failure? Can you also answer the following questions:

1. At any time, was there any failures during upgrade?

2. At any time immediately before or after upgrade, were there any other logreader agent errors?

3. Before you upgraded to SP2, did you stop Write activity at the Oracle publisher db? Or were there writes happening during the upgrade of the publisher?

|||

Hi Greg,

Thanks for your time. As for the sequence of events, I set up the Oracle Publisher, Distributor and Publication and the error popped up. No Subscriptions up yet. I noticed that the data is actually copied from Oracle into the replication folder. I can see the .bcp, .idx and .sch files and the .bcp file has records in it.

1. At any time, was there any failures during upgrade?

No failures, the upgrade went through smoothly.

2. At any time immediately before or after upgrade, were there any other logreader agent errors?

Well, i wouldnt know. i basically deleted the existing replication, upgraded and then attempted to get replication going. This means everything in the set up is new - the Oracle publisher, Distributor (plus db) and Publication.

3. Before you upgraded to SP2, did you stop Write activity at the Oracle publisher db? Or were there writes happening during the upgrade of the publisher?

I did not stop the writes, so there may have been writes happening. But the writes would have aborted when SP2 stopped the instance at some point. But that shouldnt be a factor - i'm setting replication up from scratch.

Regards,

Rama.

|||

Hi,

As a workaround, i've implemented an ETL job that periodically transfers data from Oracle into SQL Server. This works for the time being, but i dont think my tech lead will be happy without the real time replication between the databases.

I'd appreciate any help i can get.

Regards,

Rama.

|||I'm stumped with your issue especially since you started from scratch, it looks like you may have to call CSS and open a support case to dig deeper into the problem. If I find anything new, I'll post here.|||

Hi Greg,

Thanks. I may not have a solution, but i at least have a way forward. The problem's on my development box (I work for Intel by the way), so i'm not sure what kind of mileage i have. Is there a standard procedure i should follow in contacting CSS? I am sure Intel has some support arrangement with Microsoft, but how do i even begin?

Regards,

M. Ramanathan

Oracle to SQL Server Transactional Replication Error

Hi,

I'd appreciate any help i can get here. I'm working with an MS SQL Server 2005 SP2 db on a Windows 2003 OS. I set up replication from an Oracle 10g instance a few months ago, and everything was working until i installed SP2 for SQL Server.

I get the following error:

2007-06-06 16:06:22.488 Status: 4096, code: 20024, text: 'Initializing'.

2007-06-06 16:06:22.488 The agent is running. Use Replication Monitor to view the details of this agent session.

2007-06-06 16:06:23.082 Status: 0, code: 1008, text: 'ORA-01008: not all variables bound
'.

2007-06-06 16:06:23.082 ORA-01008: not all variables bound

2007-06-06 16:06:23.082 Status: 0, code: 22037, text: 'Heterogeneous Logreader encountered an error in call to LoadReplCmds when processing state 'PROCESSSYNCDONE'.'.

I can get snapshot replication to work just fine, but transactional replication throws up the error above.

Anyone with any ideas on how i can proceed?

Thanks in advance!

Regards,

Rama.

Some additonal log info:

2007-06-06 17:46:26.492 Microsoft SQL Server Log Reader Agent 9.00.3042.00
2007-06-06 17:46:26.492 Copyright (c) 2005 Microsoft Corporation
2007-06-06 17:46:26.492
2007-06-06 17:46:26.492 The timestamps prepended to the output lines are expressed in terms of UTC time.
2007-06-06 17:46:26.492 User-specified agent parameter values:
-Publisher TCADEV
-Distributor ATDSEADSQL1
-DistributorSecurityMode 1
-Continuous
-OutputVerboseLevel 4
2007-06-06 17:46:26.571 Parameter values obtained from agent profile:
-pollinginterval 5000
-historyverboselevel 1
-logintimeout 15
-querytimeout 1800
-readbatchsize 500
-readbatchsize 500000
2007-06-06 17:46:26.571 Connecting to OLE DB Publisher at datasource: 'ATDSEADSQL1', location: '', catalog: 'distribution', providerstring: '' using provider 'SQLNCLI'
2007-06-06 17:46:26.571 OLE DB Publisher: ATDSEADSQL1
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: distribution
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-06-06 17:46:27.196 Status: 4, code: 22261, text: 'Request to publisher TCADEV: select DISTRIBUTOR_PUBLISHERNAME, DISTRIBUTOR_DISTRIBUTORNAME, DISTRIBUTOR_DISTRIBUTIONDB, DISTRIBUTOR_STATUS, DISTRIBUTOR_PRODUCTVERSION FROM MSSQLSERVERDISTRIBUTOR '.
2007-06-06 17:46:27.196 OLE DB Publisher: ATDSEADSQL1
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: distribution
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-06-06 17:46:27.196 OLE DB Publisher 'ATDSEADSQL1': select @.@.SERVERNAME
2007-06-06 17:46:27.196 Connecting to OLE DB DISTOLE at datasource: 'ATDSEADSQL1', location: '', catalog: 'distribution', providerstring: '' using provider 'SQLNCLI'
2007-06-06 17:46:27.211 OLE DB DISTOLE: ATDSEADSQL1
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: distribution
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-06-06 17:46:27.211 OLE DB DISTOLE: ATDSEADSQL1
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: distribution
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2007-06-06 17:46:27.211 OLE DB DISTOLE 'ATDSEADSQL1': select datasource, srvid from master..sysservers where upper(srvname) = upper(N'TCADEV')
2007-06-06 17:46:27.211 OLE DB Publisher 'ATDSEADSQL1': sp_MSgetversion
2007-06-06 17:46:27.211 Status: 4096, code: 20024, text: 'Initializing'.
2007-06-06 17:46:27.211 The agent is running. Use Replication Monitor to view the details of this agent session.
2007-06-06 17:46:27.211 Repl Agent Status: 3
2007-06-06 17:46:27.211 OLE DB DISTOLE 'ATDSEADSQL1': sp_MSget_last_transaction @.publisher_id = 2, @.publisher_db = N'distribution', @.for_truncate = 0x0
2007-06-06 17:46:27.211 OLE DB DISTOLE 'ATDSEADSQL1': sp_MSquery_syncstates 2, N'distribution'
2007-06-06 17:46:27.211 Status: 4, code: 22262, text: 'Request to distributor ATDSEADSQL1: begin declare @.match int, @.retcode int execute @.retcode = sys.sp_checkOraclepackageversion 'TCADEV', @.match OUTPUT if (@.retcode <> 0 or @.@.error <> 0 or @.match = 0) select 0 else select 1 end'.
2007-06-06 17:46:27.227 Status: 4, code: 22262, text: 'Request to distributor ATDSEADSQL1: begin SET DEADLOCK_PRIORITY LOW exec sys.sp_IH_LR_GetCacheData 'TCADEV' end'.
2007-06-06 17:46:27.242 Status: 4, code: 22261, text: 'Request to publisher TCADEV: select Publisher_Name, Publisher_ID, RAWTOHEX(Publisher_LSN), Publisher_HasChanges, Publisher_PollInProcess, Publisher_SyncDones, Publisher_InReconciliation, Publisher_SyncInits, Publisher_RowCntValidations, Publisher_TableChanges, Publisher_XactSetBeginTime, Publisher_Timestamp, Publisher_GUID, Publisher_TracerTokens, Publisher_CurrentPollid from HREPL_Publisher'.
2007-06-06 17:46:27.242 Status: 4, code: 22261, text: 'Request to publisher TCADEV: Select e.Event_Publication_ID, e.Event_Article_ID, e.Event_EntryTime from HREPL_Event e, HREPL_Poll p WHERE p.Poll_POLLID = : Pollid AND p.Poll_ROWID = CHARTOROWID(e.ROWID) AND e.Event_Operation = 6 ORDER BY e.Event_Seq'.
2007-06-06 17:46:27.242 Status: 0, code: 22261, text: 'Request to publisher TCADEV: Select e.Event_Publication_ID, e.Event_Article_ID, e.Event_EntryTime from HREPL_Event e, HREPL_Poll p WHERE p.Poll_POLLID = : Pollid AND p.Poll_ROWID = CHARTOROWID(e.ROWID) AND e.Event_Operation = 6 ORDER BY e.Event_Seq'.
2007-06-06 17:46:27.242 Request to publisher TCADEV: Select e.Event_Publication_ID, e.Event_Article_ID, e.Event_EntryTime from HREPL_Event e, HREPL_Poll p WHERE p.Poll_POLLID = : Pollid AND p.Poll_ROWID = CHARTOROWID(e.ROWID) AND e.Event_Operation = 6 ORDER BY e.Event_Seq
2007-06-06 17:46:27.242 Repl Agent Status: 6
2007-06-06 17:46:27.242 Status: 0, code: 1008, text: 'ORA-01008: not all variables bound
'.
2007-06-06 17:46:27.242 Status: 0, code: 22037, text: 'Heterogeneous Logreader encountered an error in call to LoadReplCmds when processing state 'PROCESSSYNCDONE'.'.
2007-06-06 17:46:27.242 Disconnecting from OLE DB DISTOLE 'ATDSEADSQL1'
2007-06-06 17:46:27.258 Disconnecting from OLE DB Publisher 'ATDSEADSQL1'

|||yeah, it's probably an upgrade bug somewhere, let me see if there are any easy workarounds, if not you might have to start from scratch or call CSS.|||

Before we say it's an upgrade bug can you tell us what happened up to the point you saw the failure? Can you also answer the following questions:

1. At any time, was there any failures during upgrade?

2. At any time immediately before or after upgrade, were there any other logreader agent errors?

3. Before you upgraded to SP2, did you stop Write activity at the Oracle publisher db? Or were there writes happening during the upgrade of the publisher?

|||

Hi Greg,

Thanks for your time. As for the sequence of events, I set up the Oracle Publisher, Distributor and Publication and the error popped up. No Subscriptions up yet. I noticed that the data is actually copied from Oracle into the replication folder. I can see the .bcp, .idx and .sch files and the .bcp file has records in it.

1. At any time, was there any failures during upgrade?

No failures, the upgrade went through smoothly.

2. At any time immediately before or after upgrade, were there any other logreader agent errors?

Well, i wouldnt know. i basically deleted the existing replication, upgraded and then attempted to get replication going. This means everything in the set up is new - the Oracle publisher, Distributor (plus db) and Publication.

3. Before you upgraded to SP2, did you stop Write activity at the Oracle publisher db? Or were there writes happening during the upgrade of the publisher?

I did not stop the writes, so there may have been writes happening. But the writes would have aborted when SP2 stopped the instance at some point. But that shouldnt be a factor - i'm setting replication up from scratch.

Regards,

Rama.

|||

Hi,

As a workaround, i've implemented an ETL job that periodically transfers data from Oracle into SQL Server. This works for the time being, but i dont think my tech lead will be happy without the real time replication between the databases.

I'd appreciate any help i can get.

Regards,

Rama.

|||I'm stumped with your issue especially since you started from scratch, it looks like you may have to call CSS and open a support case to dig deeper into the problem. If I find anything new, I'll post here.|||

Hi Greg,

Thanks. I may not have a solution, but i at least have a way forward. The problem's on my development box (I work for Intel by the way), so i'm not sure what kind of mileage i have. Is there a standard procedure i should follow in contacting CSS? I am sure Intel has some support arrangement with Microsoft, but how do i even begin?

Regards,

M. Ramanathan

Oracle to SQL 2005 Replication

Greetings,
I am looking for a tool that i can use to replicate oracle tables into Sql
2005 database. Or if i can pull the data out of oracle using a sql tool that
would be perfect. The database tables are only 2GB or so. Can someone share
some insight on whether this is feasible or not.
Thanks In Advance
Deon
Transactional replication for Oracle Publishers (v 8.0.5 upwards) is built on
the Microsoft SQL Server transactional replication publishing architecture
for SQL 2005.
Pls have a look in BOL for more details.
Cheers,
Paul Ibison
|||Thanks Paul.
What is BOL?
"Paul Ibison" wrote:

> Transactional replication for Oracle Publishers (v 8.0.5 upwards) is built on
> the Microsoft SQL Server transactional replication publishing architecture
> for SQL 2005.
> Pls have a look in BOL for more details.
> Cheers,
> Paul Ibison

Oracle replication failed

Some replication jobs failed and it shows 'data conversion failed' error MSSQL_REPL20037. Any idea on it ? I can't find the error in online book. THanks in advance!

Where did you encounter this error? Command line interface or GUI?

Can you provide more information such as the repro steps, environment setting, and the table schema?

Regards,

Gary Chen|||I found the error 'The process could not bulk copy into table '"dbo"."CCB_CLINM2"'.' in view sysnchronization status. and i checked the history in job agent that it shows
"Message
2005-12-14 03:35:33.805 Connecting to Subscriber 'CCBHK08D'
2005-12-14 03:35:33.977 Skipping file 'CCB_CLINM2_2.sch' because it has already been delivered for a previous article or by a previously interrupted snapshot.
2005-12-14 03:35:33.993 Bulk copying data into table 'CCB_CLINM2'
2005-12-14 03:35:34.259 Agent message code 20037. The process could not bulk copy into table '"dbo"."CCB_CLINM2"'.
2005-12-14 03:35:34.321 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed
2005-12-14 03:35:34.337 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed"

A few small tables are replicated to the MSSQL2005 but some tables got the same error. The machine is running in 64-bits. And the replication testing in 32-bits is fine. Also, I found that some ODBC drivers are missing in MSSQL 2005 even installed with MDAC2.8. Is it support 32-bits only? Thanks in advance|||also, I found the error in replication monitor
"Error messages:
The process could not bulk copy into table '"dbo"."SYSTWODB_CABL"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
Get help: http://help/MSSQL_REPL20037
Data conversion failed"|||

Below is the ddl of table (in both mssql and oracle) which failed in replication
USE [dev]
GO
/****** Object: Table [dbo].[SYSTWODB_EXCHR] Script Date: 12/14/2005 14:22:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SYSTWODB_EXCHR](
[GLB_DTIME] [numeric](16, 0) NOT NULL,
[CCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[XCCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[EXCH_GRP] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[XEXCH_GRP] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[REL_DAY] [numeric](5, 0) NOT NULL,
[XREL_DAY] [numeric](5, 0) NOT NULL,
[BASEX_CCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[BSE_RATEID] [varchar](7) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[INP_DATE] [numeric](5, 0) NOT NULL,
[INP_TIME] [numeric](6, 0) NOT NULL,
[MID_MKT_LN] [numeric](14, 10) NOT NULL,
[MID_MKT_TY] [numeric](14, 10) NOT NULL,
[MKT_BUY] [numeric](14, 10) NOT NULL,
[MKT_RATEID] [varchar](7) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[MKT_SELL] [numeric](14, 10) NOT NULL,
[RATE_USAGE] [varchar](1) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[SOFTLOCK] [numeric](2, 0) NOT NULL,
[WIDTH] [varchar](1) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
CONSTRAINT [MSHREPL_49_PK] PRIMARY KEY CLUSTERED
(
[GLB_DTIME] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
*******************************************

CREATE TABLE "LINC"."SYSTWODB_EXCHR"("GLB_DTIME" NUMBER(16) NOT
NULL, "CCY" VARCHAR2(4) NOT NULL, "XCCY" VARCHAR2(4) NOT NULL,
"EXCH_GRP" VARCHAR2(4) NOT NULL, "XEXCH_GRP" VARCHAR2(4) NOT
NULL, "REL_DAY" NUMBER(5) NOT NULL, "XREL_DAY" NUMBER(5) NOT
NULL, "BASEX_CCY" VARCHAR2(4) NOT NULL, "BSE_RATEID" VARCHAR2(7)
NOT NULL, "INP_DATE" NUMBER(5) NOT NULL, "INP_TIME" NUMBER(6)
NOT NULL, "MID_MKT_LN" NUMBER(14, 10) NOT NULL, "MID_MKT_TY"
NUMBER(14, 10) NOT NULL, "MKT_BUY" NUMBER(14, 10) NOT NULL,
"MKT_RATEID" VARCHAR2(7) NOT NULL, "MKT_SELL" NUMBER(14, 10) NOT
NULL, "RATE_USAGE" VARCHAR2(1) NOT NULL, "SOFTLOCK" NUMBER(2)
NOT NULL, "WIDTH" VARCHAR2(1) NOT NULL,
CONSTRAINT "SYSTWODB_EXCHR_PK" PRIMARY KEY("GLB_DTIME"),
CONSTRAINT "SYS_C0022019" CHECK("GLB_DTIME" IS NOT NULL),
CONSTRAINT "SYS_C0022020" CHECK("CCY" IS NOT NULL),
CONSTRAINT "SYS_C0022021" CHECK("XCCY" IS NOT NULL),
CONSTRAINT "SYS_C0022022" CHECK("EXCH_GRP" IS NOT NULL),
CONSTRAINT "SYS_C0022023" CHECK("XEXCH_GRP" IS NOT NULL),
CONSTRAINT "SYS_C0022024" CHECK("REL_DAY" IS NOT NULL),
CONSTRAINT "SYS_C0022025" CHECK("XREL_DAY" IS NOT NULL),
CONSTRAINT "SYS_C0022026" CHECK("BASEX_CCY" IS NOT NULL),
CONSTRAINT "SYS_C0022027" CHECK("BSE_RATEID" IS NOT NULL),
CONSTRAINT "SYS_C0022028" CHECK("INP_DATE" IS NOT NULL),
CONSTRAINT "SYS_C0022029" CHECK("INP_TIME" IS NOT NULL),
CONSTRAINT "SYS_C0022030" CHECK("MID_MKT_LN" IS NOT NULL),
CONSTRAINT "SYS_C0022031" CHECK("MID_MKT_TY" IS NOT NULL),
CONSTRAINT "SYS_C0022032" CHECK("MKT_BUY" IS NOT NULL),
CONSTRAINT "SYS_C0022033" CHECK("MKT_RATEID" IS NOT NULL),
CONSTRAINT "SYS_C0022034" CHECK("MKT_SELL" IS NOT NULL),
CONSTRAINT "SYS_C0022035" CHECK("RATE_USAGE" IS NOT NULL),
CONSTRAINT "SYS_C0022036" CHECK("SOFTLOCK" IS NOT NULL),
CONSTRAINT "SYS_C0022037" CHECK("WIDTH" IS NOT NULL))
TABLESPACE "URBIS" PCTFREE 10 PCTUSED 70 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 2604K NEXT 352K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING|||

Besides, I found a message regarding to the failed replication in error log

'Error: 14151, Severity: 18, State: 1'

Any Hints on it? Thanks in advance

|||

Let's try to narrow it down a bit further.

So, the replication works fine on 32bit server and all tables copied fine, right? Only 64bit SQL server is having problem? Has the replication ever work in your 64bit environment at all? Can you tell me what your environment, OS, topology, and platform?

A minimum series of steps that can help to reproduce the error.

What type of Oracle publisher do you use? Gateway or Complete?

I also notice that you uses "Chinese_Hong_Kong_Stroke_90_CI_AS" when you create your table. What is the character set that you have install on your SQL server, OS, and Oracle server?

Regards,

Gary

|||

1. same set of tables and same source DB (with vchar and number as data type)

2. source: oracle 9.2 running in AIX platform.

3, publisher is gateway

4. SQL collation is Chinese _hong_kong_stroke_90_CI_AS, OS is 'Chinese (Hong Kong SAR)'

5. transactional publication with push subscription

6. Destination: Win2003 standard x64 edition sp1 with MSSQL2005 EVAL edition (x64) while another machine is Win2003 standard edition sp1 MSSQL2005 eval edition

7. All tables can be replicated to MSSQL2005(32bits) but some of them failed in the 64 bits one.

points 1-5 are the same in both 32 and 64 bits machine

many many Thanks for your help

|||

Hi,

This may or may not help you but I came accross this "data conversion" error when i was trying to reinitialize a subscriber when we migrated the publisher from 2000 to 2005. Our distributor also migrated to a SQL 2005 instance and it was a push subscription.

The issue in this instance was that the underlying data type length for a UDF created at the subscriber originally by SQL 2000 was smaller than what SQL 2005 needed. During the initialization the UDF was not being created at the subscriber by the SQL 2005 as it was already there so the solution was to drop the UDF and let the initialization process create it. Luckily the UDF was not being used anywhere else so we were able to drop it.

Cheers,

Priyanga

Oracle replication failed

We are using MSSQL2005 (64-bits) and replicating data from oracle to
mssql2005 DB. Some replication jobs failed and it shows 'data conversion
failed' error MSSQL_REPL20037. Any idea on it ? I can't find the error in
online book. THanks in advance!
Which agent is this failing on? Can you enable logging to see the problem
statement?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:25858670-C644-4B8F-8B2A-288E1460AC3F@.microsoft.com...
> We are using MSSQL2005 (64-bits) and replicating data from oracle to
> mssql2005 DB. Some replication jobs failed and it shows 'data conversion
> failed' error MSSQL_REPL20037. Any idea on it ? I can't find the error
> in
> online book. THanks in advance!
>
|||Below is the trace info on the failed replication. Thanks a lot!
EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id =
0x0564E4D45972EC48A051BBA5B6CB9F5D, @.step_id = 2, @.sql_message_id = 0,
@.sql_severity = 0, @.run_status = 4, @.run_date = 20051216, @.run_time = 93014,
@.run_duration = 2220,
@.operator_id_emailed = 0, @.operator_id_netsent = 0, @.operator_id_paged = 0,
@.retries_attempted = 0, @.session_id = 5, @.message = N'
2005-12-16 01:52:05.319 Bulk copying data into table ''SYSTWODB_CNCOM''
2005-12-16 01:52:29.413 Bulk copied data into table ''SYSTWODB_CNCOM''
(90546 rows)
2005-12-16 01:52:29.648 Applied script ''SYSTWODB_CNEOD_4.sch''
2005-12-16 01:52:29.663 Bulk copying data into table ''SYSTWODB_CNEOD''
2005-12-16 01:52:34.038 Agent message code 20037. The process could not bulk
copy into table ''"dbo"."SYSTWODB_CNEOD"''.
2005-12-16 01:52:34.101 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed
2005-12-16 01:52:34.116 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed
2005-12-16 01:52:34.116 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed
2005-12-16 01:52:34.132 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed
2005-12-16 01:52:34.132 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed
'
"Hilary Cotter" wrote:

> Which agent is this failing on? Can you enable logging to see the problem
> statement?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:25858670-C644-4B8F-8B2A-288E1460AC3F@.microsoft.com...
>
>
|||can you post the schema of SYSTWODB_CNEOD
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:6181D00E-B82E-4EA2-AE13-9EBE37337F2F@.microsoft.com...[vbcol=seagreen]
> Below is the trace info on the failed replication. Thanks a lot!
> EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id =
> 0x0564E4D45972EC48A051BBA5B6CB9F5D, @.step_id = 2, @.sql_message_id = 0,
> @.sql_severity = 0, @.run_status = 4, @.run_date = 20051216, @.run_time =
> 93014,
> @.run_duration = 2220,
> @.operator_id_emailed = 0, @.operator_id_netsent = 0, @.operator_id_paged =
> 0,
> @.retries_attempted = 0, @.session_id = 5, @.message = N'
> 2005-12-16 01:52:05.319 Bulk copying data into table ''SYSTWODB_CNCOM''
> 2005-12-16 01:52:29.413 Bulk copied data into table ''SYSTWODB_CNCOM''
> (90546 rows)
> 2005-12-16 01:52:29.648 Applied script ''SYSTWODB_CNEOD_4.sch''
> 2005-12-16 01:52:29.663 Bulk copying data into table ''SYSTWODB_CNEOD''
> 2005-12-16 01:52:34.038 Agent message code 20037. The process could not
> bulk
> copy into table ''"dbo"."SYSTWODB_CNEOD"''.
> 2005-12-16 01:52:34.101 Category:NULL
> Source: Microsoft SQL Native Client
> Number:
> Message: Data conversion failed
> 2005-12-16 01:52:34.116 Category:NULL
> Source: Microsoft SQL Native Client
> Number:
> Message: Data conversion failed
> 2005-12-16 01:52:34.116 Category:NULL
> Source: Microsoft SQL Native Client
> Number:
> Message: Data conversion failed
> 2005-12-16 01:52:34.132 Category:NULL
> Source: Microsoft SQL Native Client
> Number:
> Message: Data conversion failed
> 2005-12-16 01:52:34.132 Category:NULL
> Source: Microsoft SQL Native Client
> Number:
> Message: Data conversion failed
> '
> "Hilary Cotter" wrote:
|||Below is the ddl of table (in both mssql and oracle) which failed in
replication
USE [dev]
GO
/****** Object: Table [dbo].[SYSTWODB_EXCHR] Script Date: 12/14/2005
14:22:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SYSTWODB_EXCHR](
[GLB_DTIME] [numeric](16, 0) NOT NULL,
[CCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[XCCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[EXCH_GRP] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[XEXCH_GRP] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[REL_DAY] [numeric](5, 0) NOT NULL,
[XREL_DAY] [numeric](5, 0) NOT NULL,
[BASEX_CCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[BSE_RATEID] [varchar](7) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[INP_DATE] [numeric](5, 0) NOT NULL,
[INP_TIME] [numeric](6, 0) NOT NULL,
[MID_MKT_LN] [numeric](14, 10) NOT NULL,
[MID_MKT_TY] [numeric](14, 10) NOT NULL,
[MKT_BUY] [numeric](14, 10) NOT NULL,
[MKT_RATEID] [varchar](7) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[MKT_SELL] [numeric](14, 10) NOT NULL,
[RATE_USAGE] [varchar](1) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[SOFTLOCK] [numeric](2, 0) NOT NULL,
[WIDTH] [varchar](1) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
CONSTRAINT [MSHREPL_49_PK] PRIMARY KEY CLUSTERED
(
[GLB_DTIME] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
*******************************************
CREATE TABLE "LINC"."SYSTWODB_EXCHR"("GLB_DTIME" NUMBER(16) NOT
NULL, "CCY" VARCHAR2(4) NOT NULL, "XCCY" VARCHAR2(4) NOT NULL,
"EXCH_GRP" VARCHAR2(4) NOT NULL, "XEXCH_GRP" VARCHAR2(4) NOT
NULL, "REL_DAY" NUMBER(5) NOT NULL, "XREL_DAY" NUMBER(5) NOT
NULL, "BASEX_CCY" VARCHAR2(4) NOT NULL, "BSE_RATEID" VARCHAR2(7)
NOT NULL, "INP_DATE" NUMBER(5) NOT NULL, "INP_TIME" NUMBER(6)
NOT NULL, "MID_MKT_LN" NUMBER(14, 10) NOT NULL, "MID_MKT_TY"
NUMBER(14, 10) NOT NULL, "MKT_BUY" NUMBER(14, 10) NOT NULL,
"MKT_RATEID" VARCHAR2(7) NOT NULL, "MKT_SELL" NUMBER(14, 10) NOT
NULL, "RATE_USAGE" VARCHAR2(1) NOT NULL, "SOFTLOCK" NUMBER(2)
NOT NULL, "WIDTH" VARCHAR2(1) NOT NULL,
CONSTRAINT "SYSTWODB_EXCHR_PK" PRIMARY KEY("GLB_DTIME"),
CONSTRAINT "SYS_C0022019" CHECK("GLB_DTIME" IS NOT NULL),
CONSTRAINT "SYS_C0022020" CHECK("CCY" IS NOT NULL),
CONSTRAINT "SYS_C0022021" CHECK("XCCY" IS NOT NULL),
CONSTRAINT "SYS_C0022022" CHECK("EXCH_GRP" IS NOT NULL),
CONSTRAINT "SYS_C0022023" CHECK("XEXCH_GRP" IS NOT NULL),
CONSTRAINT "SYS_C0022024" CHECK("REL_DAY" IS NOT NULL),
CONSTRAINT "SYS_C0022025" CHECK("XREL_DAY" IS NOT NULL),
CONSTRAINT "SYS_C0022026" CHECK("BASEX_CCY" IS NOT NULL),
CONSTRAINT "SYS_C0022027" CHECK("BSE_RATEID" IS NOT NULL),
CONSTRAINT "SYS_C0022028" CHECK("INP_DATE" IS NOT NULL),
CONSTRAINT "SYS_C0022029" CHECK("INP_TIME" IS NOT NULL),
CONSTRAINT "SYS_C0022030" CHECK("MID_MKT_LN" IS NOT NULL),
CONSTRAINT "SYS_C0022031" CHECK("MID_MKT_TY" IS NOT NULL),
CONSTRAINT "SYS_C0022032" CHECK("MKT_BUY" IS NOT NULL),
CONSTRAINT "SYS_C0022033" CHECK("MKT_RATEID" IS NOT NULL),
CONSTRAINT "SYS_C0022034" CHECK("MKT_SELL" IS NOT NULL),
CONSTRAINT "SYS_C0022035" CHECK("RATE_USAGE" IS NOT NULL),
CONSTRAINT "SYS_C0022036" CHECK("SOFTLOCK" IS NOT NULL),
CONSTRAINT "SYS_C0022037" CHECK("WIDTH" IS NOT NULL))
TABLESPACE "URBIS" PCTFREE 10 PCTUSED 70 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 2604K NEXT 352K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING
Thanks a lot!
"Hilary Cotter" wrote:

> can you post the schema of SYSTWODB_CNEOD
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:6181D00E-B82E-4EA2-AE13-9EBE37337F2F@.microsoft.com...
>
>

Oracle Replication

I have two databases one is in head office and other is at factory site.
I want to share information between these two sites.
I have tried to complete setup of Oracle Advance replication but failed
to complete with errors
If Any body can help please go fast I am in need ...
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
try the oracle newsgroups.
"Amir Sajjad" <amir_sajjad@.hotmail.com> wrote in message
news:eCtICzIHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I have two databases one is in head office and other is at factory site.
> I want to share information between these two sites.
> I have tried to complete setup of Oracle Advance replication but failed
> to complete with errors
> If Any body can help please go fast I am in need ...
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Oracle authentication in the linked server

Hi everyone.
Two days ago I tried to replicate between the SQL 2000 Server and the Oracle
8i.
Before the transaction replication, I had configured linked server for
replication.
(I suppose linked server's configuration is OK. When I queried using the
Openquery, the result was no problem.)
But I failed in the authentication to the Oracle.
So I tested replication between SQL 2000 servers. and then there is no
problem.
What should I check for the Oracle authentication ?
Please, let me know
Thanks for any help
Kelly Seo
check out the link in my sig. There is a sample chapter which explains how
to configure Oracle subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"kelly seo" <jyseo@.tricom.co.kr> wrote in message
news:eOxAymbhFHA.3164@.TK2MSFTNGP15.phx.gbl...
> Hi everyone.
> Two days ago I tried to replicate between the SQL 2000 Server and the
> Oracle 8i.
> Before the transaction replication, I had configured linked server for
> replication.
> (I suppose linked server's configuration is OK. When I queried using the
> Openquery, the result was no problem.)
> But I failed in the authentication to the Oracle.
> So I tested replication between SQL 2000 servers. and then there is no
> problem.
> What should I check for the Oracle authentication ?
> Please, let me know
> Thanks for any help
> Kelly Seo
>
>
|||Thanks Hilary Cotter,
At first, I'm so sorry my question is not clear.
My linked server setting is correct. when I query using the openquery, the
result is no problem.
but when I try to regist 'Register New SQL Server' from Oracle is connected
using the linked server, the error message is showed up like that.
Error message : SQL Server doesnn't exist or cann't
access(ConnectionOpen(Connect())
Because of that error, I couldn't regist Oracle connected using the linked
server and then couldn't do the replication.
What should I do check more?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23Yfd2IchFHA.3568@.TK2MSFTNGP10.phx.gbl...
> check out the link in my sig. There is a sample chapter which explains how
> to configure Oracle subscribers.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "kelly seo" <jyseo@.tricom.co.kr> wrote in message
> news:eOxAymbhFHA.3164@.TK2MSFTNGP15.phx.gbl...
>

Oracle 9i -> SQL Server 2005: Initialization of table failes

Hi there,

I'm trying to initialize a subscription for a transactional replication between Oracle9i and MS SQL Server 2005.

The snapshot agent runs succesfully, but I get an error when the distribution agent runs to apply the snapshots at the subscriber. I've pastet the job history from the Log File Viewer below.

Message
2006-06-08 09:27:25.516 Applied script 'ADDRESS_8.sch'
2006-06-08 09:27:25.516 Applied script 'ADDRESS_8.idx'
2006-06-08 09:27:25.516 Bulk copying data into table 'ADDRESS'
2006-06-08 09:27:25.641 Agent message code 20037. The process could not bulk copy into table '"ADDRESS"'.
2006-06-08 09:27:25.704 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed
2006-06-08 09:27:25.704 Category:NULL

Every column contaning null-values at the table in Oracle9i is allowing null-values at the destination table in the SQL Server.

I'm pretty lost here - does anyone know how to solve this problem?

Best regards,

JB Plum

Hi,

This error occurs when there are some incompatible data being inserted at subscriber. Oracle and SQL server offer different boundary conditions for some data type - for example, date on Oracle and datetime on SQL server offers different date range. If Oracle has a date before January 1, 1753, it will error out in SQL server.

To debug - you can try getting the distribution agent command line (you can use distribution agent property in UI to get it) and run it using -OutputVerboseLevel 2, i.e. distrib.exe <cmd line> -OutputVerboseLevel 2. This will give you a detail error message with a command line for running the bcp. Hopefully this will help you in getting the piece of data which is causing problem.

HTH – Deepak

|||

Hi Deepak!

Thanks for your answer.

I actually got a command line for running bcp (see below).

"bcp "import".."BILL_INVOICE_HEADER" in "[directory path to snapshot]" -e "errorfile" -t"\n<x$3>\n" -r"\n<,@.g>\n" -m10000 -S[Server Instance]] -T -w"

The problem is, that when I run this command line, I get no errors - all the data get's copied to the destination table, and the errorfile is empty. Therefore, I don't have any useful debug information.

Do you, or does anyone else, have an idea of what is needed to solve this problem?

Best Regards,

JB Plum

|||

Hi JB,

SQL2005 replication uses the oledb bcp API as oppose to the odbc bcp API that bcp.exe uses to bulk load data into the subscriber. As such, it is quite possible that oledb bcp API is performing more data validation than odbc bcp API thereby causing the error you have seen from the distribution agent. I have asked whether it is possible to give you the bcpoledb.exe utility which uses oledb bcp API under the cover in order to check whether this is indeed the case. Regardless of the outcome of that, this does look like something that we can try to handle better, and so it would be great if you can open a case with product support with the data and schema that is causing the problem so we can investigate the issue in greater detail.

Thanks for reporting this.

-Raymond

|||

Hi JB,

It would appear that you have to give us the bcp file somehow as we can't give you the bcpoledb.exe utility. Or, you can try to isolate which data value at the Oracle publisher was giving you the conversion error as Deepak has already suggested in another posting.

-Raymond

|||

This is how I solved the problem.

I ran the distribution agent with the parameters "-BcpBatchSize 1" and "CommitBatchSize 1". This way I could see, what rownumber was causing the error and by looking at data in that row, I found out, that there was a "?" in one of the varchar2 fields. This character is incompatible with the varchar datatype in SQL Server, and I therefore changed the default datatype mapping for varchar2 to nvarchar in SQL Server. That solved the problem.

Thanks for your help

Best regards,

JB Plum