Junk arabic details while transfer arabic data from sql server 2000 to oracle

  • noush.k17

    SSC Veteran

    Points: 236

    We are facing problems to view arabic details in oracle which is transfered from sql server 2000.

    In oracle database we are using charecterset is UTF8.

    NLS_CHARACTERSET :UTF8 and NLS_NCHAR_CHARACTERSET:UTF8

    Sqlr server collation is SQL_Latin1_General_CP1256_CI_AS.

    we are using stored procedure for transfering data from sql server 2000 to oracle.

    And i am able to view some arbic details which is not transfered from sql server 2000.

    I thing some configuration i need to change in sql server.

    Can any one help me out from this issue?

  • Mohammad Mazharuddin Ehsan

    SSCarpal Tunnel

    Points: 4548

    UTF8 CHARACTERSET has known issue with import. I have used AR8MSWIN1256 without problem for Arabic data. You can also try AL16UTF16. The SQL Server coalation you are using is perfect.

    -----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

  • noush.k17

    SSC Veteran

    Points: 236

    Thank you for your reply..

    We were using to import from another intermediate sqlserver and it was successfull.

    And more over i cannot change anything in oracle database which is live.

    The issue is we are using one intermediate sqlserver for transfering the data to oracle apart from main sqlserver.

    now we want to stop the intermediate and need to transfer from main sqlserver to oracle Db.

    i guess , i need to change some configuration in main sql server.

    Main sql server and intermediate have the same collation.

    Could you please help me out from this problem?

  • Mohammad Mazharuddin Ehsan

    SSCarpal Tunnel

    Points: 4548

    I don't know what stored procedures you are using to trnasfer data. I suggest you to use SSIS. Using SSIS, proper code page properties should be used (1256 for Arabic)

    -----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

  • noush.k17

    SSC Veteran

    Points: 236

    i am calling stored procedure using Operating system command.

    It was working with intermediate server

  • Mohammad Mazharuddin Ehsan

    SSCarpal Tunnel

    Points: 4548

    I hope you have proper coalition at the object level and the database level in the main SQL Server which is failing to transfer data to Oracle properly.

    -----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

  • noush.k17

    SSC Veteran

    Points: 236

    Thank you for your reply..

    It might be the problem of collation.

    because thats the one I did additionally in main sql server.

    The collation of main sql server was SQL_Latin1_General_CP1_CI_AS

    And i have changed to SQL_Latin1_General_CP1256_CI_AS using this scommand sqlservr -m -T4022 -T3659 -q"SQL_Latin1_General_CP1256_CI_AS" which i run through command prompt.

    It might be the way which i used to change the collation is wrong.

    How can i check the collation in object level and database level.

    What should i have to do for changing thiscollation.

    Please guid me. and I am new for this sql server.

    could you please give me to change the script for collation?

  • Mohammad Mazharuddin Ehsan

    SSCarpal Tunnel

    Points: 4548

    I think the root cause of the problem you are facing is desctibed in the below excerpt

    Note If you use the ALTER DATABASE command in SQL Server 2000 to change the collation of a database, the collation of the columns in the tables is not automatically changed. To change the collation of the columns, use the ALTER TABLE command and the ALTER COLUMN command. If you are using DTS, you can create the table and the columns with the appropriate collation before you transfer the data or you can use the Use Collation option. If you are using DTS and the table with the appropriate collation already exists, make sure to disable the Drop Existing Objects First option before you run the package.

    See the below site for more details

    http://support.microsoft.com/kb/325335

    You need to identify the fields in the tables which contain arabic data and change their colation as follows:

    ALTER TABLE test ALTER COLUMN value varchar(20) SQL_Latin1_General_CP1256_CI_AS

    The beter way is to create a database with the same collation as the main one and then restore from the main to the newly created one.

    -----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

  • gmdba.khan

    Valued Member

    Points: 63

    Install ODBC Drivers and configure odbc.ini file in that we set parameter IANAAppCodePage=106 . Sample odbc file below:

    [MSSQL]

    Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMmsss23.so

    Description=DataDirect SQL Server Wire Protocol driver

    Database=<dbname>

    LogonID=<username>

    Password=<password>

    Address=IP Address,1433

    QuotedId=No

    AnsiNPW=No

    IANAAppCodePage=106

    And check from ur application from where u r not getting Arabic Data, if need some changes do changes in ur application . The IANAAppCodePage=106 condtains code page 106 is nothing but UTF-8 code that v set in odbc file to get Arabic data and if required some changes like using code page in our application.

    Enjoy : :smooooth::smooooth::smooooth:

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply