Replication using SQL Server 2012 and Informix 9.7 publisher

• kpmcginn

  Valued Member

  Points: 58

  More actions

  August 4, 2014 at 5:22 pm

  #297610

  I am using SQL Server 2012 Enterprise edition. I have created an ODBC connection to my Informix 9.7 instance which resides on a remote server. The ODBC connection test works successfully.

  In SQL Server, I have created a Linked server via the ODBC connection with:

  EXEC master.dbo.sp_addlinkedserver @server='ifx1',@provider=N'MSDASQL',

  @datasrc='Informix_V001_Prod',@srvproduct='',

  @provstr='DSN=Informix_V001_Prod;Database=V001';

  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname='ifx1',@useself=N'False',@locallogin=NULL,

  @rmtuser='xcal',@rmtpassword=xxxx

  Under the linked server instance all of the database tables are listed. Using the wizard to select the first 1000 rows from any of the tables fails with the error no rows found. However, in a query session I can select from any of the tables. Based on this it would appear that i have both connectivity and select access.

  Under replication, when Attempt to define a publisher, my linked server is not included in the list of servers to define as the publisher. If I manually add the linked server as the publisher using the system procs, there are no errors, but when I attempt to add the local SQL Server instance as a subscriber, the linked server that I defined as a publisher is not included as a valid publisher.

  I am defining my SQL Server instance as both the distributor and subscriber. The account I am using is defined as the owner of the subscription database. This same account is a windows auth account.

  Any ideas as to what I am doing incorrectly would be appreciated.