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
No comments:
Post a Comment