Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

error replicating to DB2

53 views
Skip to first unread message

MV

unread,
Jul 25, 2002, 12:13:20 PM7/25/02
to
Hello,

I'm getting this error when trying to sync and replicate a
simple two-column (both char (1)) table from SQL 2000 (no
my desktop) to a DB2 server (on an NT server):

SQL0401N The data types of the operands for the
operation "=" are not compatible. SQLSTATE=42818

The DB2 server is set up as a linked server using the "MS
OLEDB for ODBC" provider and I have a working ODBC data
source pointing to the DB2 server. I can't quite get
the "MS OLEDB for DB2" provider to work (keeps squawking
about invalid arguments).

Any ideas (please don't suggest "use DataMirror")?

Thanks in advance.

Martin V.

M.V.

unread,
Jul 26, 2002, 3:19:09 PM7/26/02
to
Responding to my own post...

I managed to get a DB2 linked server up and running using
the MS OLE DB provider for DB2. I can now select, insert,
and update a table on the linked server but replication
still will not work. The error now is "The process could
not connect to Subscriber 'GAME2'."

Also, I tried to set up a trigger to "replicate" INSERT's
on the SQL Server over to the DB2 linked server (ie.
INSERT GAME2.GAME.WEBADMIN.TESTTABLE SELECT * FROM
inserted) but I get error 7391: "Procedure testtrigger,
Line 6 The operation could not be performed because the
OLE DB provider 'DB2OLEDB' was unable to begin a
distributed transaction."

Datamirror is starting to sound more interesting...

M.V.

>.
>

M.V.

unread,
Jul 26, 2002, 3:51:19 PM7/26/02
to
By disabling the distributed transaction parameter in my
provider string, the error from the trigger is
now "Server: Msg 8524, Level 16, State 1, Line 1
The current transaction could not be exported to the
remote provider. It has been rolled back."
and the replication error is "Internal Netlib Error".

Not sure if this represents progress or not...

>.
>

Nick Schueler MS

unread,
Jul 26, 2002, 6:27:27 PM7/26/02
to
Hi M.V.
It is possible to setup Transactional replication from SQL 2000
Standard/Enterprise (Not Desktop) to a DB2 Subscriber.

I recommend creating a Link server that uses an OLEDB provider. Not the
OLEDB for ODBC driver but an actually OLEDB provider. Microsoft Host
Integration Server comes with an OLEDB provider.
http://www.microsoft.com/hiserver/default.asp

You can also acquire an OLEDB provider from other vendors.

You mentioned you are setting up a Distributed Transaction. This is not
supported with transactional replication. I am not sure that it is
supported with the HIS provider. You might want to check this on their web
site.

When setting up the DB2 subscriber, the Publisher and distributor
properties. You can reach this within enterprise manager by clicking
tools/replication/configure publisher, subscriber...

Choose the Subscriber and click New. Then choose OLE DB Data Source and
setup a connection using the OLEDB Provider.

Nick Schueler

--------------------
| Content-Class: urn:content-classes:message
| From: "M.V." <mvi...@olgc.on.ca>
| Sender: "M.V." <mvi...@olgc.on.ca>
| References: <0fbf01c233f6$31903160$19ef2ecf@tkmsftngxa01>
<194c01c234d9$50f498f0$3bef2ecf@TKMSFTNGXA10>
| Subject: error replicating to DB2
| Date: Fri, 26 Jul 2002 12:51:19 -0700
| Lines: 59
| Message-ID: <218201c234dd$cf6625b0$2ae2...@phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcI03c9mRmrFICzbQrmk2mW57xS8XQ==
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.sqlserver.replication
| Path: cpmsftngxa07
| Xref: cpmsftngxa07 microsoft.public.sqlserver.replication:31704
| NNTP-Posting-Host: TKMSFTNGXA14 10.201.226.42
| X-Tomcat-NG: microsoft.public.sqlserver.replication

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

M.V.

unread,
Jul 29, 2002, 3:50:41 PM7/29/02
to

>-----Original Message-----
>Hi M.V.
>It is possible to setup Transactional replication from
SQL 2000
>Standard/Enterprise (Not Desktop) to a DB2 Subscriber.
>

Well, I guess that's important to know. I'll try another,
more conventional, server-based SQL Server.

>I recommend creating a Link server that uses an OLEDB
provider. Not the
>OLEDB for ODBC driver but an actually OLEDB provider.
Microsoft Host
>Integration Server comes with an OLEDB provider.
>http://www.microsoft.com/hiserver/default.asp
>
>You can also acquire an OLEDB provider from other
vendors.
>

Yep, got it. I have set up a working linked server using
the MS OLE DB Provider for DB2. It was a bit painful
because I wasn't familiar with the provider's parameter
requirements but I muddled through and am able to excecute
queries on the DB2 server from within a SQL Server session.

>You mentioned you are setting up a Distributed
Transaction. This is not
>supported with transactional replication. I am not sure
that it is
>supported with the HIS provider. You might want to check
this on their web
>site.
>

I was actually trying a distributed
transaction "implicitly" within the context of a trigger
as a sort of klugey replication. (ie. an "insert" trigger
that traps inserts on a SQL Server table and then in turn
inserts them to the linked DB2 server). Then I dropped the
trigger and tried an insert to the DB2 linked server
following a "begin distributed tran" and got the same
error. I suspect that you're right though, distributed
transactions may not be supported with this provider. I
can live with that as long as I can get the replication
working. No luck so far but... wait a minute... What's
this... Hmmm, the MS support engineer, Shirley Kelly,
working on my case has sent me a hotfix that may apply to
my problem. I'll give it a try and report back here.

Thanks.

>| >>my desktop) Í{ wØ ¦ "òh!Tq?!"Óë ì to a DB2 server

M.V.

unread,
Jul 30, 2002, 3:46:47 PM7/30/02
to
Well,

With the help of MS Technical Support, I've managed to get
replication to DB2 working. The "internal netlib error"
was fixed by a Host Integration Server hotfix (see
Q280123). I was still having problems but after I dropped
my publication, subscription and the DB2 tables involved
(including the MSREPL7 table), recreated everything,
replication began to work.

Hopefully, documenting this saga will help someone else in
the future.

M.V.

0 new messages