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

Question

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.

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.

Main sql server and intermediate have the same collation.

i am calling stored procedure using Operating system command.

It was working with intermediate server


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 this collation.
I am new for this sql server.
Please help me out from this issue?

Answer 1

To answer How can i check the collation in object level and database level?
At DB level: run the below query by providing your Database name instead of DB_Name.
select collation_name from sys.databases where name = <'DB_Name'>

At Object level: AFAIK object by default inherits the collation defined for the database.

---

Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

Answer 2

Hi,
I would like to suggest you to post this tread to "SQL Server Tools General " forum.

Thanks-

---

Let us TRY this | Don’t forget to mark the post(s) that answered your question

Answer 3

Dude In ur sp are U using any temp tables lika ... #temp Tables ???

Answer 4

I am not using temp table like #.
Please check my sp


ALTER                       PROCEDURE SP_GETUPDATETRANSTOora AS

set xact_abort on
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

DECLARE ATCURSOR CURSOR FOR
SELECT AXT.TST_REFCODE, AXT.TST_STATUS, AXT.TST_STATUSDATE, AXT.TST_STATUSDESC,
AXT.TST_STATUPDATEDBY, AXT.TST_STATUPDATEDON, AXT.TST_nam_REF,
AXT.TST_nam_UPDATEDBY, AXT.TST_nam_UPDATEDON FROM OR_TRANSACTION OT INNER JOIN
Oradbnam..ScmaNam.tableName AXT ON OT.TST_CUST_ID=AXT.CUST_ID AND OT.TST_REFCODE=AXT.TST_REFCODE
WHERE OT.TST_STATUS<>AXT.TST_STATUS  AND AXT.TST_STATUS IN (3,4,6,7)  FOR READ ONLY
DECLARE @TST_REFCODE NVARCHAR(15),
   @TST_STATUS NUMERIC,
   @TST_STATUSDATE DATETIME,
   @TST_STATUSDESC NVARCHAR(1000),
   @TST_STATUPDATEDBY NVARCHAR(50),
   @TST_STATUPDATEDON DATETIME,
   @TST_nam_REF NVARCHAR(100),
   @TST_nam_UPDATEDBY NVARCHAR(50),
   @TST_nam_UPDATEDON DATETIME
OPEN ATCURSOR
FETCH NEXT FROM ATCURSOR INTO @TST_REFCODE, @TST_STATUS, @TST_STATUSDATE, @TST_STATUSDESC, @TST_STATUPDATEDBY, @TST_STATUPDATEDON, @TST_nam_REF, @TST_nam_UPDATEDBY, @TST_nam_UPDATEDON
WHILE @@FETCH_STATUS=0
BEGIN
   SELECT @TST_STATUSDESC=
      CASE @TST_STATUS
         WHEN 3 THEN 'aaa'
         WHEN 4 THEN ''bbb
         WHEN 6 THEN 'ccc'
  WHEN 7 THEN 'ddd'
   END
   UPDATE OR_tableNaa SET TST_STATUS = @TST_STATUS, TST_STATUSDATE = @TST_STATUSDATE, TST_STATUSDESC = @TST_STATUSDESC,
   TST_STATUPDATEDBY = @TST_STATUPDATEDBY, TST_STATUPDATEDON = @TST_STATUPDATEDON, TST_nam_REF = @TST_nam_REF,
   TST_nam_UPDATEDBY = @TST_nam_UPDATEDBY, TST_nam_UPDATEDON=@TST_nam_UPDATEDON WHERE TST_REFCODE=@TST_REFCODE
   FETCH NEXT FROM ATCURSOR INTO @TST_REFCODE, @TST_STATUS, @TST_STATUSDATE, @TST_STATUSDESC, @TST_STATUPDATEDBY, @TST_STATUPDATEDON, @TST_nam_REF, @TST_nam_UPDATEDBY, @TST_nam_UPDATEDON
END
CLOSE ATCURSOR
DEALLOCATE ATCURSOR

INSERT INTO Oradbnam..ScmaNam.tableName SELECT distinct * FROM V_TRANSACTION WHERE
CONVERT(NVARCHAR(15),CUST_ID) + '' + TST_REFCODE NOT IN (SELECT CONVERT(nVARCHAR(15),CUST_ID) + '' + TST_REFCODE FROM
Oradbnam..ScmaNam.tableName) AND TST_UPDATEDON >= GETDATE() - 30

set xact_abort OFF