Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Junk arabic details while transfer arabic data from sql server 2000 to oracle Expand / Collapse
Author
Message
Posted Monday, November 02, 2009 4:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 05, 2009 12:23 AM
Points: 4, Visits: 19
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?
Post #812296
Posted Monday, November 02, 2009 10:30 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 3:56 AM
Points: 378, Visits: 2,392
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.

-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time
Post #812729
Posted Monday, November 02, 2009 10:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 05, 2009 12:23 AM
Points: 4, Visits: 19
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?
Post #812737
Posted Tuesday, November 03, 2009 12:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 3:56 AM
Points: 378, Visits: 2,392
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)


-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time
Post #812760
Posted Tuesday, November 03, 2009 3:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 05, 2009 12:23 AM
Points: 4, Visits: 19
i am calling stored procedure using Operating system command.

It was working with intermediate server
Post #812840
Posted Tuesday, November 03, 2009 4:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 3:56 AM
Points: 378, Visits: 2,392
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.

-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time
Post #812852
Posted Tuesday, November 03, 2009 4:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 05, 2009 12:23 AM
Points: 4, Visits: 19
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?

Post #812855
Posted Wednesday, November 04, 2009 8:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 3:56 AM
Points: 378, Visits: 2,392
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.


-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time
Post #813647
Posted Saturday, June 22, 2013 7:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 22, 2013 6:55 AM
Points: 1, Visits: 0
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 :
Post #1466457
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse