6

After about 3 days of trying, I am unable to create a linked server from SQL Server 2016 to Informix 11.5. I haven't tried this with other versions (2008R2, etc) since I wanted to set up the connection in a test environment first.

What I have Done

1) Using IBM Informix instructions with OLE DB Provider in which I get the following error (regardless if I use a DNS in provider string):

Cannot initialize the data source object of OLE DB provider "Ifxoledbc" for linked server "UCCX". OLE DB provider "Ifxoledbc" for linked server "UCCX" returned messazge "EIX000: (-23197)" Database locale information mismatch" (Microsoft SQL Server, Error: 7303)

The DB Locale is en_US.57372

2) Using Microsoft OLE DB Provider for SQL Server as explained in this blog which returns the error:

Named Pipes Provider: Coould not open a connection to SQL Server. OLE DB provider SQLNCLI11 for linked server UCCX returned message "Login timeout expired" OLE DB provider SQLNCLI11 for linked server UCCX retunred a message "A network-related or instance-specific error has occured while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. SQLNCLI11 for linked server UCCX returned message "Invalid connection string attribute"

I have very little experience with Informix which should be apparent and this isn't a local installation, it's a Cisco instance as you probably gleaned from the blogs I was using. Any advice would be appreciated.

  • For Issue #1, what bit-level of the drivers did you install and what bit-level of SQL Server are you running? These two should be the same. For #2, you can't use the SQL Native Client to connect to an Informix database. That blog is misleading as I suspect the author is using the Informix drivers installed in step #1 of her instructions. – John Eisbrener Apr 17 '17 at 15:15
  • @JohnEisbrener 64 bit for both. I saw where a lot of people had problems installing 32-bit drivers and getting them to work. I thought it was fishy as well but she posted an image AND put the text with that provider. If she would have chosen the Informix driver the catalog would have been grayed out. – scsimon Apr 17 '17 at 15:17
  • Can you see the recently installed Informix driver under ServerName -> Server Objects -> Linked Servers -> Providers? If you cannot see the provider, you may need to restart the instance. Alternatively, you may have an odd driver install that is user-specific and not server wide, in which case you may need to install it as the same user running SQL. – John Eisbrener Apr 17 '17 at 15:20
  • @JohnEisbrener I do see the driver when connected as myself (an admin) and connected as another local admin account. – scsimon Apr 17 '17 at 15:24
  • I hope this is not your issue, as the workaround looks plain awful. I don't think your Linked Server is misconfigured, but maybe passing Client_Locale=en_us.8859-1 in the connection string (via ConnectionStrings.com) would work? I'm grasping at straws on this though... sorry – John Eisbrener Apr 17 '17 at 15:40
3

We had a nightmare setting something similar up, specifically with the issue of connecting to multiple Informix environments. I had several Informix instances that needed to be linked to a single SQL Server instance for warehousing purposes, and was told by our resident SME that this was impossible; only a single Informix instance could be referenced per server.

I would consistently receive the same errors as you, and nothing I was able to dredge up online proved helpful.

Ultimately, the solution had several parts, of which you've done the first few:

  1. The correct IBM Informix ODBC Driver must be installed on the database server.
  2. Use the SetNet32 application provided with the drivers and set up the Server Information tab appropriately. Additionally, set up a corresponding System DSN entry.
  3. The trick with the Linked Server setup is to use the following (items in codeblocks are literal):

    • Provider: Microsoft OLE DB Provider for ODBC Drivers
    • Product Name: Ifxoledbc
    • Data Source: $YourSystemDSNName
    • Provider String
      • Driver = {IBM INFORMIX ODBC DRIVER}
      • Database = $YourDatabaseName
      • etc...

If you're more of a manual SQL kind of person, see below. The provider string is pulled into a variable for inline commenting purposes only, while still having executable code. Modify as needed:

DECLARE @provider NVARCHAR(4000);
SET @provider = N'Driver={IBM INFORMIX ODBC DRIVER};'
  + N'DATABASE= ;'  --Informix DB name
  + N'HOST= ;'      --Informix Hostname or IP adddress
  + N'SERVICE= ;'   --Informix service name, ex.  ifx1_tcp1
  + N'PROTOCOL= ;'  --Informix protocol, ex.  onsoctcp
  + N'SERVER= ;'    --Informix server, ex. ifx1
  + N'DB_LOCALE=en_US.819; CLIENT_LOCALE=en_US.819;'; --Change to your locale, as needed

EXEC master.dbo.sp_addlinkedserver 
@server = N'UCCX', --Linked Server system name
@srvproduct=N'Ifxoledbc', 
@provider=N'MSDASQL', 
@datasrc=N' ', --Your System DSN Name
@provstr= @provider;

Since you're dealing with a single instance, it should be enough to simply match the values used to create your System DSN. If you need to add additional linked servers for other instances, that can be done following exactly the same format described above without additional DSN entries.

I hope that helps.

  • A portion of that is from memory, so if you encounter any issues, let me know and we can work on it together. – coyote Jun 26 '17 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.