Solved

Linked Server Error With Informix ODBC Data Source

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

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Read about achieving the basic levels of HRIS security in the workplace.
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

930 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

12 Experts available now in Live!

Get 1:1 Help Now