Solved

Linked Server Error With Informix ODBC Data Source

Posted on 2010-09-16
13
2,458 Views
Last Modified: 2012-05-10
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
Comment
Question by:ellswrth
  • 6
  • 4
  • 3
13 Comments
 
LVL 5

Expert Comment

by:flutophilus
ID: 33692807
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
 
LVL 10

Expert Comment

by:GDG_DBA
ID: 33692901
Is your server a 64 bit server?  If so, did you install 32 bit or 64 bit drivers?
-G
0
 

Author Comment

by:ellswrth
ID: 33692905
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:ellswrth
ID: 33692969
Is your server a 64 bit server?  If so, did you install 32 bit or 64 bit drivers?

-G

Server is 32 bit.
0
 
LVL 5

Expert Comment

by:flutophilus
ID: 33693500
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
 
LVL 10

Expert Comment

by:GDG_DBA
ID: 33693517
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
 
LVL 5

Expert Comment

by:flutophilus
ID: 33693726
Also is there any reason why you can't install up-to-date Informix drivers ? Available at no charge from IBM web site.
0
 

Author Comment

by:ellswrth
ID: 33693813
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
 

Author Comment

by:ellswrth
ID: 33693848
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
 
LVL 10

Expert Comment

by:GDG_DBA
ID: 33693896
Ellswrth is right.  First try to update the drivers.  I don't think I will be of much help.
-G
0
 
LVL 10

Expert Comment

by:GDG_DBA
ID: 33693901
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
 

Author Comment

by:ellswrth
ID: 33716143
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
 

Accepted Solution

by:
ellswrth earned 0 total points
ID: 33744623
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question