Error in SQL Linked server to Informix DB
Gianni Candi's SQL Server linked server to Informix via ODBC raised an overflow/conversion error on a CHAR column containing certain special characters. Eric Vercelletto suspected a codepage issue and advised setting DB_LOCALE and CLIENT_LOCALE, finding the collation via sysmaster sysdbslocale. Gianni later said it was still unsolved; enabling the locale string caused error 7303. Unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi, I have a Linked Server pointing to Informix DB. Using this linked server I am writing query SQL to fetch the data and load in SQL table. But there are some tables exist which is throwing datatype overflow error. For a table I identified the column and the record which is causing the issue. Could not convert the data value due to reason other than sign mismatch or overflow If I exclude this column or the specific record, SELECT statement returns the result without any issue. I analyzed this column which is Char datatype; I obtain error when there are certain character ( or character) in the field. Do anyone having any idea about it and help me?
Gianni,
you probably are hitting a codepage issue.
What type of client are you using ? 4GL? JDBC, ODBC, other?
Anyway, on the client side, you need to set a couple of environment variables
called on the client side
- DB_LOCALE
- CLIENT_LOCALE
to get the right value, you have to query against the sysmaster database:
SELECT dbs_collate
FROM sysdbslocale
WHERE dbs_dbsname = "<your db name>"
if the client is on windows, you may have to find the equivalent on windows
for the codepage.
Hope this helps
Eric
Hi Eric, I created an ODBC connection on my client (a windows client with Sql Sqerver), then a created a linked server in sql server. I try your tip and let you know. Thanks Gianni
Hi Eric, after a long time I resume the discussion. Unfortunately I can't solve the problem. This is the script that I use to create Linked Server on SqlServer. If I remove the commentLine on 5th row I receive error 7303 (cannot inizializate Ifxoledbc). With the comment the Linked Server is good but there is the problem of my first post. DECLARE @provider NVARCHAR(4000); SET @provider = N'Driver={IBM INFORMIX ODBC DRIVER};' + N'SERVICE=1526 ;' --Informix service name + N'PROTOCOL=onsoctcp ;' --Informix protocol --+ N'DB_LOCALE=en_US.819; CLIENT_LOCALE=en_US.819;'; EXEC master.dbo.sp_addlinkedserver @server =N'LS_INFORMIX', --Linked Server system name @srvproduct=N'Ifxoledbc', @provider=N'Ifxoledbc', @datasrc=N'xxx@yyyyyy', --Informix Database @provstr= @provider;