FETCHING DATA FROM SQL EXPRESS 2005

[Renga]

Hello, I had a very peculiar problem today which I am sure hundreds would have had and solved before.

I have developed a VB.Net + SQL Server application and installed it in a few dozen places. All worked very well. Then I installed it in a persons computer in Middle East in the City of Nablus. To my surprise, the app is not even reading user id and password.. The database is connected but just not fetching any data..

The Nablus installation obviously is having a English and Jordan(Arabic) interface and of course the person can easily change from English to Arabic with the keyboard.

I am at a loss to know why the SQL Server is not performing there.

Kindly help to overcome this issue.

[Renga]

Well, I just googled and went all over to find out what are the ways of getting out of this problem. I just found that too many people have gone thru this before and there are just too many articles on utf-8 vs utf16, encoding of dbs, etc etc..including pages and pages of scripts to run to alter the collations etc.

Finally, as usual after a tiring search of answer to this issue - I found what is the real problem. The problem is this: I had made the database in India with the default collation sequence that comes with sql server express 2005. Language used in English.

Now the database has gone to another country where the language is Arabic and the Regional Language Setting is Jordan(Arabic). Now when the SQL Express is installed in that country it has selected a collation sequence (probably) based on the collation sequence and is reading the exisiting userid and password data of my database file based on that collation sequence.

Now how to solve this issue: Solutions given are:
a) Rejig the data to the new coll.seq..using scripts, tools etc..The sql scripts given there are just complex enough and none can guarantee they will solve the issue fully..hence you need to try.

b) Recreate the database to what ever the collation sequence is there by using create table procedures and then move the data. This seems to be a most preferred soltuion by many as they say it does the job quicker. My app has a few XML and XL backup and reload features and this is what I am choosing..

Can anyone comment on this pl or suggest a better one which is quicker ?

Thanks.

[Renga]

Well, I just soloved it.

1. Wrote a script to get the whole of DB data into an XML file. Then wrote a script to
   a) Read that data in full from the DB
   b) Delete the entire records from the Database and make it empty
   c) Read that XML file and Populate the entire data into that DB..all with appropriate user messages.

2. Made an Empty DB file and Sent that file to the user at Nablus along with the XML data file. Asked him to just read from the XML and populate the DB in his PC..

3. The DB was used to run the APP. It just worked fine.

I could do this because the database was just about 10 mb and the XML file was in KB.. I think this is a foolproof method to beat all the problems associated with migrating the databse - if the DB is small.

When it comes to moving a bigger DB files, this method could work, but the read and write to DB consumes time..even for a few thousand records. Hence it could take a while - say a few hours or days before a empty DB is filled up with and gets the new data.

As again, I would like more experienced people to give a better method or a tool to solve this problem. I am asking this because, not many are aware of this kind of issues - like collation, utf-8 or 16 etc till it hits them at the crucial time.

Thanks for Reading !