0

I have a SQL Server 2012 Enterprise edition linked server connection to an IBM AS400 DB2 database. I am using the IBMDASQL provider for the connection.

I have problems with some Hungarian characters: the letter Ő is converting to O during the select.

In this example the name_converted column will be OK, the Ő letter remains Ő.

The name column will be converted to O.

SELECT * FROM openquery (g,
   SELECT 
       cast(name as char(35) ccsid 870) as name_converted,
       name,
   FROM libr.mytable')

My question is: can I create the linked server so that all the character is using the CCSID 870?

EXEC master.dbo.sp_addlinkedserver @server = N'G', @srvproduct = N'HUN00101', @provider  = N'IBMDASQL', @datasrc = N'HUN00101'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'G', @useself=N'False', 
            @locallogin=NULL, @rmtuser=N'query', @rmtpassword='########' 
GO

EXEC master.dbo.sp_serveroption @server = N'G', @optname = N'collation compatible', 
                                @optvalue = N'false' 
GO
EXEC master.dbo.sp_serveroption @server=N'G', @optname=N'data access', @optvalue=N'true' GO
EXEC master.dbo.sp_serveroption @server=N'G', @optname=N'dist', @optvalue=N'false' GO
EXEC master.dbo.sp_serveroption @server=N'G', @optname=N'pub', @optvalue=N'false' GO
EXEC master.dbo.sp_serveroption @server=N'G', @optname=N'rpc', @optvalue=N'false' GO
EXEC master.dbo.sp_serveroption @server=N'G', @optname=N'rpc out', @optvalue=N'false' GO
EXEC master.dbo.sp_serveroption @server=N'G', @optname=N'sub', @optvalue=N'false' GO
EXEC master.dbo.sp_serveroption @server=N'G', @optname=N'connect timeout', @optvalue=N'0' GO
EXEC master.dbo.sp_serveroption @server=N'G', @optname=N'collation name', @optvalue=null GO
EXEC master.dbo.sp_serveroption @server=N'G', @optname=N'lazy schema validation', @optvalue=N'false' GO
EXEC master.dbo.sp_serveroption @server=N'G', @optname=N'query timeout', @optvalue=N'0' GO
EXEC master.dbo.sp_serveroption @server=N'G', @optname=N'use remote collation', @optvalue=N'true' GO
EXEC master.dbo.sp_serveroption @server=N'G', @optname=N'remote proc transaction promotion', @optvalue=N'true' GO

Thanks

AngocA
  • 7,655
  • 6
  • 39
  • 55
Tomi7979
  • 53
  • 6
  • On this link: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=151072 I found Host CCSID=37;PC Code Page=850. Can I build it somehow into the Linked servers connection? – Tomi7979 Jul 30 '14 at 08:37

1 Answers1

0

It looks like the data may not have been properly tagged with CCSID 870 on the IBM i system. Can you try using the "Force Translate" custom property and setting it to 870? Note: This will cause all data not properly tagged with a CCSID to be handled as CCSID 870.

jweberhard
  • 576
  • 3
  • 7
  • Thanks for the answer. The "Force Translate" option is on AS400 side or it is on SQL Server? – Tomi7979 Aug 04 '14 at 10:50
  • My guess is that you would set it like the following: EXEC master.dbo.sp_serveroption @server=N'G', @optname=N'Force Translate', @optvalue=N'870' GO – jweberhard Aug 05 '14 at 01:19
  • Unfortunately there are no option 'Force Translate' for the sp_serveroption procedure: [link] http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(SP_SERVEROPTION_TSQL);k(SQL11.SWB.TSQLRESULTS.F1);k(SQL11.SWB.TSQLQUERY.F1);k(MISCELLANEOUSFILESPROJECT);k(DevLang-TSQL)&rd=true – Tomi7979 Aug 05 '14 at 07:13
  • Try using @provstr=N'Force Translate=870' with sp_addlinkedserver. – jweberhard Aug 06 '14 at 12:25
  • I inserted your suggestion into the command, but unfortunately it is still not ok. EXEC master.dbo.sp_addlinkedserver @server = N'G', @srvproduct=N'HUN00101', @provider=N'IBMDASQL', @datasrc=N'HUN00101',@provstr=N'Force Translate=870' – Tomi7979 Aug 07 '14 at 13:32