IDS Forum

Re: Can Informix access Sql Server table?

Posted By: Art Kagel
Date: Tuesday, 5 March 2013, at 12:24 p.m.

In Response To: RE: Can Informix access Sql Server table? (John Adamski)

Sounds like a perfect application of CDC and the API is not complex. You
create a stand-alone app which uses the API to connect to the SQL Server
database, register the tables and operations it wants to be notified about
with a callback function for each and wait for messages. It would trap for
inserts, updates, and/or deletes take the data it gets and either add it
directly to the Informix side with a direct connection, hand it off to a
separate thread connected to Informix, or pipe the data to a separate app
connected to Informix to handle that side.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.

On Tue, Mar 5, 2013 at 12:11 PM, John Adamski <adamski@graceland.edu> wrote:

> Art,
>
> Thanks for this info will research and also pass on to the developer.
>
> He is now trying to do what he needs from the SQL server side but is
> getting
> and error which I'm guessing has to do with the way SQL server triggers
> work
> and it is not 'opening' a connection to Informix.
>
> Here is the developers last email to me.
>
> Since it sounds like going from Informix to SQL is going to take a while
> and
> may include some costly purchases I am trying to go the other way SQL to
> Informix.
>
> Basically just using a simple trigger that takes any rows inserted into the
> SQL table and writing them to a table in Informix.
>
> If I run this code manually with a where clause it works fine
> insert into CX.cars.informix.cus_cf_answers
> (SubmissionID, ItemID, AnswerValue)
> select SubmissionID, ItemID, AnswerValue
> from CUS_CF_Answers
> where SubmissionID = '64166500-0CD4-40FF-8989-046263B6E83C'
>
> but as a trigger using the INSERTED table I get the error below.
>
> The trigger code:
> CREATE trigger [dbo].[i_cus_cf_answers]
> ON [dbo].[CUS_CF_Answers]
> AFTER INSERT
> AS
> --do not return any results
> SET NOCOUNT ON
> BEGIN
> insert into CX.cars.informix.cus_cf_answers
>
> (SubmissionID, ItemID, AnswerValue)
> select SubmissionID, ItemID, AnswerValue
> from INSERTED
> END
>
> Here is the error I get when I insert into the SQL table.
>
> The operation could not be performed because OLE DB provider "Ifxoledbc"
> for
> linked server "CX" was unable to begin a distributed transaction.
>
> Any ideas what we would need to change to fix this error?
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Tuesday, March 05, 2013 8:07 AM
> To: ids@iiug.org
> Subject: Re: Can Informix access Sql Server table? [29695]
>
> There is another option. You can use CDC (Change Data Capture) to replicate
> the SQL Server table(s) to Informix either by coding it yourself
> - the CDC API is an open one and supported by both SQL Server and Informix
> so
> you could make it bi-directional - or using a 3rd party tool - DbMoto
> comes to
> mind or IBM's CDC product. This will be less expensive than EGM.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
> by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Tue, Mar 5, 2013 at 8:51 AM, John Adamski <adamski@graceland.edu>
> wrote:
>
> > Oh, pooh, we looked at EGM for another project about 5 years ago and
> > was out of our price range. That was what I was thinking that was not
> > going to be able to help the developer.
> >
> > Thanks everyone, I now go get to rain on the developer's parade. :-)
> >
> > John
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Art Kagel
> > Sent: Monday, March 04, 2013 11:12 AM
> > To: ids@iiug.org
> > Subject: Re: Can Informix access Sql Server table? [29674]
> >
> > The only way you can do that would be to purchase the Infomrix
> > Enterprise Gateway product.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own
> > opinions and do not reflect on my employer, Advanced DataTools, the
> > IIUG, nor any other organization with which I am associated either
> > explicitly, implicitly, or by inference. Neither do those opinions
> > reflect those of other individuals affiliated with any entity with
> > which I am affiliated nor those of the entities themselves.
> >
> > On Mon, Mar 4, 2013 at 11:45 AM, John Adamski <adamski@graceland.edu>
> > wrote:
> >
> > > Informix:
> > > HPUX 11.31 (iiiv3) on HP BL870c IA server IDS 11.70.FC4 SQL Server:
> > > W2k8 r2 SP1 on Vsphere 5 cluster
> > > SQL Server 2008 r2
> > >
> > > One of our developers has asked me to help him setup "whatever is
> needed"
> > > so
> > > from Informix he can access data on a SQL Server table. We already
> > > have the other direction (SQL server accessing Informix data) setup
> > > and working using odbc on the sql server.
> > >
> > > I'm still clarifying with the developer if he needs only read access
> > > or read/write access to the SQL server table from Informix.
> > >
> > > I don't know if this is possible, is it? If so how do I connect the
> > > two databases? What tools/software do I need?
> > >
> > > A SQL server friend said I might be able to use SQL Server
> > > Integration Service
> > > (SSIS) but he has never used it so wasn't sure. I have never heard
> > > of it so know even less. :-)
> > >
> > > John Adamski, Sr. Network Specialist Graceland University, 1
> > > University Place, Lamoni, IA 50140 adamski@graceland.edu
> > >
> > >
> > >
> > >
> >
> >
> >
>
>
> *******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --bcaec54b48d0489c3c04d71c745b
> >
> >
> >
> >
>
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --f46d042d051c91e3f304d72df251
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--bcaec554d232b2576304d730bdb6

Messages In This Thread

• Can Informix access Sql Server table?
  John Adamski -- Monday, 4 March 2013, at 11:45 a.m.
  • Re: Can Informix access Sql Server table?
    Art Kagel -- Monday, 4 March 2013, at 12:12 p.m.
    • RE: Can Informix access Sql Server table?
      John Adamski -- Tuesday, 5 March 2013, at 8:51 a.m.
      • Re: Can Informix access Sql Server table?
        Art Kagel -- Tuesday, 5 March 2013, at 9:06 a.m.
        • Re: Can Informix access Sql Server table?
          Paul Watson -- Tuesday, 5 March 2013, at 9:16 a.m.
        • Re: Can Informix access Sql Server table?
          Keith Simmons -- Tuesday, 5 March 2013, at 10:50 a.m.
          • Re: Can Informix access Sql Server table?
            Art Kagel -- Tuesday, 5 March 2013, at 10:58 a.m.
        • RE: Can Informix access Sql Server table?
          John Adamski -- Tuesday, 5 March 2013, at 12:11 p.m.
          • Re: Can Informix access Sql Server table?
            Art Kagel -- Tuesday, 5 March 2013, at 12:24 p.m.
            • RE: Can Informix access Sql Server table?
              John Adamski -- Tuesday, 5 March 2013, at 2:59 p.m.
              • Re: Can Informix access Sql Server table?
                Art Kagel -- Tuesday, 5 March 2013, at 3:20 p.m.
                • RE: Can Informix access Sql Server table?
                  John Adamski -- Wednesday, 6 March 2013, at 7:57 a.m.
                  • Re: Can Informix access Sql Server table?
                    Art Kagel -- Wednesday, 6 March 2013, at 8:47 a.m.
                    • Re: Can Informix access Sql Server table?
                      Art Kagel -- Wednesday, 6 March 2013, at 8:53 a.m.
      • Re: Can Informix access Sql Server table?
        Mike Aubury -- Tuesday, 5 March 2013, at 9:52 a.m.
  • RE: Can Informix access Sql Server table?
    Alexandre Marini -- Monday, 4 March 2013, at 12:15 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.