Solved

Linked Server Error With Informix ODBC Data Source

Posted on 2010-09-16
13
2,407 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
 

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now