Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2626
  • Last Modified:

Linked Server Error With Informix ODBC Data Source

I am attempting to connect to an old Informix database instance from SQL Server 2005 using ODBC on the SQL server.

The ODBC drivers are ancient, INFORMIX-CLI 2.5. The Informix server is SE, version 7.2 I believe, on an HP-UX box.

I have a system DSN called "globetek" which will expose the necessary Informix db tables to an Access database with no problem.

On my personal workstation which has SQL Server 2008 Developer installed, I can create the linked server using the following script and it works perfectly;

IF  EXISTS (SELECT srv.name FROM sys.servers srv 
WHERE srv.server_id != 0 
AND srv.name = N'Globetek')
EXEC master.dbo.sp_dropserver @server=N'Globetek', @droplogins='droplogins'
GO

EXEC sp_addlinkedserver 
   @server = 'Globetek', 
   @srvproduct = '',
   @provider = 'MSDASQL', 
   @provstr = 'pro=sesoctcp',
   @datasrc = 'globetek',
   @catalog = 'globetek'
GO

EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'Globetek',
    @useself = 'FALSE',
    @rmtuser = 'username',
    @rmtpassword = 'password'
GO

Open in new window


My production SQL server is SQL Server 2005. I have installed the same ODBC drivers on that server and created an identical "globetek" ODBC system DSN. I can use that DSN in Access and connect to the Informix db exactly as I can on my workstation.

When I create an identical linked server on the production server using the script the server does create, and the script message says Command(s) completed successfully..

Attempting to use the server, though, causes errors. Attempting to Test Connection on the linked server once is is created gives the following error

"The test connection to the linked server failed."

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "Globetek".
OLE DB provider "MSDASQL" for linked server "Globetek" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "Globetek" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "Globetek" returned message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load locale categories.". (Microsoft SQL Server, Error: 7303)

If I try to expand the linked server catalogs I get

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "Globetek". (Microsoft SQL Server, Error: 7303)

As an aside, the web links to Microsoft for support in the full error messages lead nowhere.

Any aid that can be provided to track down why this won't work in my production server but will on my workstation will help save what hair I haven't already pulled out. Thanks in advance!
0
ellswrth
Asked:
ellswrth
  • 6
  • 4
  • 3
1 Solution
 
flutophilusCommented:
I wonder where the "INTERSOLV" comes from in the error message? Intersolv used to provided ODBC drivers for Informix (may still do AFAIK), but I wouldn't expect their name to appear in an error message raised by the Informix Client SDK. Could there be a pre-existing Intersolv ODBC driver on the machine that's interfering in some way?
0
 
GDG_DBACommented:
Is your server a 64 bit server?  If so, did you install 32 bit or 64 bit drivers?
-G
0
 
ellswrthAuthor Commented:
The INFORMIX-CLI 2.5 drivers _are_ Intersolv drivers provided through Informix. There are no other ODBC drivers installed on the machine and only one installation of the Intersolv drivers exists.

As noted, the Intersolv/Informix drivers and the DSN do work when used to create linked tables in an Access database to validate their operation. The errors do not occur on my development SQL server, only on the production server.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ellswrthAuthor Commented:
Is your server a 64 bit server?  If so, did you install 32 bit or 64 bit drivers?

-G

Server is 32 bit.
0
 
flutophilusCommented:
It looks like the "Unable to load locale categories" which is coming back from Informix drivers could be caused by a missing environment variable, though why it's a problem with SQL2005 on the production server and not Access (or SQL2008 on your dev m/c) is a mystery. Perhaps Access is supplying some data by default that's missing otherwise.

Perhaps you could try running SETNET32 on both your machines to make sure the settings are identical? Check the settings for db_locale and client_locale.
0
 
GDG_DBACommented:
Have you tried a query to the linked server without expanding the catalogs?
I have seen where linked servers work, but they still can't show the catalogs in SSMS.
-G
 
0
 
flutophilusCommented:
Also is there any reason why you can't install up-to-date Informix drivers ? Available at no charge from IBM web site.
0
 
ellswrthAuthor Commented:
No reason I can't install new drivers on the SQL server if they'll solve the problem, as long as there's no change required on the HP-UX Informix server.

This is though an extremely old version of Infomix so I haven't tried too hard to find updated drivers thinking they may not be available.
0
 
ellswrthAuthor Commented:
Have you tried a query to the linked server without expanding the catalogs?

I have seen where linked servers work, but they still can't show the catalogs in SSMS.

-G

The point of linking the server is to create a copy of the Informix database on a SQL server for reporting purposes. The intention is to feed the Informix data to Analysis Services.

On my workstation, I have a set of stored procs that are designed to empty and load the required tables and they work there.

Queries that run for hours against the Informix through ODBC take seconds on the SQL copy.

I attempted to run the stored procs on the production SQL server and they failed with the same errors as I described above.
0
 
GDG_DBACommented:
Ellswrth is right.  First try to update the drivers.  I don't think I will be of much help.
-G
0
 
GDG_DBACommented:
Sorry I meant flutophilus
>>Ellswrth is right.  First try to update the drivers.  I don't think I will be of much help.
-G
0
 
ellswrthAuthor Commented:
So, I've tried removing the old drivers and installing current ones - now, on the machine that DID work correctly, I am getting the "Unable to load locale categories" error ... :-(

So far, no good.
0
 
ellswrthAuthor Commented:
So here's the update. None of the above either arrived at or assisted a solution (thanks for trying though). Why it worked on one machine but not others is a mystery, and why installing up to date drivers brought on the error is a mystery too.

I was able to work around the problem by first reinstalling the old drivers on the machine which could connect to the linked server.

I then used the SQL Management Studio on that machine to connect to the SQL 2005 production server to build the linked server, database copy and stored procedures there.

Once I had done all of that, THEN the SQL 2005 server could connect to the linked server and run the stored procedures.

So the symptom (can't connect to the linked server on the production server) is gone, and I'm moving forward.

No points awarded, though.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now