December 17, 2003 at 8:46 am
This is the scenario.
I am doing a little job for an existing website. The site has mostly english and
some hebrew data. It uses ASP & SQL Server 2000.
I am trying to insert Hebrew data from an MS Access 2000 database into a SQL Server 2000
databse. Whenever I try to just copy the data (using an ASP script)(or using dts)
the data is copied as Hebrew characters which apparently the site itself doesn't
understand, and interprets it as '?????'. As when I looked at the existing data
(before I came along, and what works fine for the site) in SQL Server it appeared
as gibberish.
What I need to do is convert the data from Access to SQL Server. I'm not 100% sure how each stores the data, but it appears that Access is storing it as ANSI (code page 1255), and SQL Server is storing it as Unicode. Or vice versa.
I have tried copying the data through ASP as well as dts. I'm not sure what else to do.
Thank you for your help.
Eric Bauer
December 22, 2003 at 12:00 pm
This was removed by the editor as SPAM
December 22, 2003 at 12:06 pm
Have you checked the SQL database and ensured the NVARCHAR is set? I think that you have to use the N data-types to store this kind of data.
Good Luck. If I get more time I will research this further for you.
AJ Ahrens
Good Hunting!
AJ Ahrens
webmaster@kritter.net
December 22, 2003 at 12:17 pm
quote:
Have you checked the SQL database and ensured the NVARCHAR is set?
Yup, it is set to NVARCHAR
Eric Bauer
December 22, 2003 at 1:28 pm
Can you post some of the data that you are having problems with? And possibly the offending code? I'll look at this when I get home tonight for you.
Thanks,
AJ
AJ Ahrens
Good Hunting!
AJ Ahrens
webmaster@kritter.net
December 22, 2003 at 1:39 pm
Access is storing this (but backwards - no big deal - I'm not sure if it shows up, but it's hebrew characters):
ה"ר דוד ב"ר יוסף אבודרהם
while SQL Server is storing this:
ä"ø ãåã á"ø éåñó àáåãøäí
I just need a way to transfer the data in Access to Sql Server while converting it.
I've been using ASP, and I've tried VB as well as dts, but I've had no luck. The data is always transfering the access version, and not going to the SQL way.
Also, I can't change the setup of the site that reads the data, as that already exists, and I can't change it.
Thank you
Eric
Edited by - nycbauer on 12/22/2003 1:39:47 PM
Eric Bauer
December 22, 2003 at 2:09 pm
Hate to ask this but you are using SQL 2K??
Have you tried setting the collation for the column(s) that are going to store the hebrew data using hebrew collation???
SELECT *
FROM ::fn_helpcollations()
Don't know if this will work but, I will try more later 🙂
AJ Ahrens
Good Hunting!
AJ Ahrens
webmaster@kritter.net
December 22, 2003 at 2:27 pm
I am using SQL 2K, however, since this is for an existing website, I can't make any changes to the db structure. At least I don't want to for fear of messing things up, especially since they work fine now.
Thanks
Eric Bauer
December 22, 2003 at 2:40 pm
Eric,
I have tried collation of Hebrew_Bin and Latin_General_Bin (both are windows collation) on a column and inserted the data directly from Access 2K to SQL 2K using an Append query in Access. Both the Access and SQL data looked like the data you want below.
Also, if you want the data reversed you may want to research REVERSE().
I don't know much about ASP so I don't know if it is possible to even use different collations per column in it.
Is it possible for you to set-up a test table and attempt to insert the Hebrew data into it using different collations and your existing ASP code??
Good Luck
AJ Ahrens
Good Hunting!
AJ Ahrens
webmaster@kritter.net
January 13, 2004 at 7:17 am
Hi there, sorry I haven't responded to you earlier, however I have tried testing it with different collations, and it still transfered the same, ie with the Hebrew characters as opposed to the gibberish ones (which by the way if you could tell me what they are ie unicode, ANSI, whatever I'll be happier that I am now).
The reversing is so important, as the front end already takes care of that. My problem is only the backend.
Thanks
Eric Bauer
January 14, 2004 at 7:37 pm
Hi Eric,
I am pretty sure they will have to be unicode to handle the special characters.
I have reviewed this since my last post where I think I was able to get it to work but, this was on a brand new table no web involved etc...
Have you tried to insert the data into a text field? or a blob?
I wish I could help more....
Good Hunting!
AJ Ahrens
webmaster@kritter.net
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply