Chose MSSQL collation

hi,

I want to migrate from MYSQL to MSSQL 2005. My website Contains Multilangugal contents "Frace & Arabic". I 'm using "UTF_8 generalCI" Collation in MYSQL . What Collation I should use in MSSQL 2005. I only want to have One DB.

thanks,

Inorder to store multi-lingual text, you need to set the field datatype to NVARCHAR. This should work fine. If you need more information, let me know. I am currently working on an arabic web portal.

Thanks

|||

thanks for your Rply,

I've set field types as nvarchar. But If I set collation as "latin1swedish" and field as nvarchar then I can't find Arabic words.. would you please tell me, what is the role of collation in DB. suppose

would you please tell me what method are you using for Arabic website developement.Do you use 2 Seprate DB? hae you create arabic section as a real subweb or you use methods like profile.

best regards,

|||

Hi,

The collation is used when sorting and querying. When sorting, SQL Server will sort according to the selected collation.

You can alter a column's collation by using the COLLATE clause. Please check the following link for more information.

http://msdn2.microsoft.com/en-us/library/ms184391.aspx

|||thanks Kevin for your reply,If I've understood right, I could enter multi languagual contents to NVARCHAR fields, and Collation doesn't matter while save data.We could change the DB Colation later if needed.But what is the usual Collation. ex. what www.asp.net uses for it's collation. regards,

Chinese characters in the database

hi all!
My database contains simplified chinese chars. when i view table contents via Enterprise Manager -> open table, all data is ok.
now i have to display it via ASP scripts. and there everything gets screwd up. i get ? marks instead of the chinese chars.
any idea?check if chinese is installed on u'r m/c and what are u'r default settings ,
check the locale settings of the machine.|||In your ASP pages, you need to check your codec

I'm not sure if this is u'r case, but maybe u could try this asp code in your asp script

Depends on your Chinese Char Type either

Response.CodePage = 936
Response.CharSet = "GB2312"

or

Response.CodePage = 950
Response.CharSet = "big5"

Then the data u select out from your MS SQL Database should be able to be viewed, and proceed with normal coding.

If this happens to be related to u'r problem, u might want to search the net to know more about this "codepage" and "charset" attributtes.

Hope this helps.

Checking that an object is not empty

Hi,

Does anyone know the correct syntax to check the contents of an object variable. I want to do this as part of a constraint,

TIA

R

Variables of type Object are not support in expressions -

"The data type of variable "User::Variable" is not supported in an expression."

|||

Thanks for your reply.

I have a problem then. I want to pass in this object to use in an ExecuteSQL task, as my input parameter. However, because of some conditional execution, there may not be anything to update. This means that the object has nothing in it, and at the moment. This is causing my task to fail.

Any ideas?

|||

You could read the object variable inside a script task, and check for it equalling Nothing.

Then set a boolean variable indicating whether it is populated or not and put an expression on the precedence constraint that checks the boolean variable to see whether it should execute or not.

-Jamie

Checking contents of column and replacing contents if first character is a letter

Hi All,

I have come up against a wall which i cannot get over.

I have an sql db where the date column is set as a varchar (i know, should have used datetime but this was done before my time and i've got to work with what is there). The majority of values are in the format dd/mm/yyyy. However, some values contain the word 'various'.

I'm attempting to compare the date chosen on a c# .net page with the values in the db and also return all the 'various' values as well.

I have accomplished casting the varchar to a datetime and then comparing to the selected date on the .net page. However, it errors when it comes across the 'various' entrant.

Is there anyway to carry out a select statement comparing the start_date values in the db to the selected date on the .net page and also pull out all 'various' entrants at the same time without it erroring? i thought about replacing the 'various' to a date like '01/01/2010' so it doesn't stumble over the none recognised format, but am unsure of how to do it.

This is how far i have got: casting the varchar column to datetime and comparing.

SELECT * FROM table1 WHERE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime) '" + date + "'"

Many thanks in advance!

You could adapt your query as follows:

"SELECT
*
FROM
table1
WHERE
CASE Start_Date
WHEN 'Various' THEN '01 January 0001'
ELSE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime)
END = '" + date + "' "

However, solution two is to add a new column called "Start_DateTime" and populate it with genuine dates. You can then phase out "Start_Date" entirely. It's always easier to data-clean sooner rather than later!

Also, read up on dynamic SQL and SQL injection when you get a minute.

|||

Hi Sohnee,

Thanks for the quick reply!

I've tested your query and I think it is still trying to cast the 'various' entrants as a date, as it returns the following:

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

Also, how would compare the date selected to the dates in db as i'm unsure of where to place the '>'.

I'll take you advice and start reading!

Thanks again!

|||

Can anybody else help at all?? PLEASEEEEE!!

Ta!

|||

SELECT *FROM table1WHERE CASE WHEN ISDATE(Cast(SUBSTRING(Start_Date,4,2) +'/' + SUBSTRING(Start_Date,1,2) +'/' +SUBSTRING(Start_Date,7,4)as datetime))=1 THENCast(SUBSTRING(Start_Date,4,2) +'/' + SUBSTRING(Start_Date,1,2) +'/' +SUBSTRING(Start_Date,7,4)as datetime)ELSENULLEND='" + date + "'"

|||

Apologies - in my example I used the minimum date from .NET - which is a bit early for SQL.

"SELECT
*
FROM
table1
WHERE
CASE Start_Date
WHEN 'Various' THEN '01 January 1900'
ELSE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime)
END = '" + date + "' "

|||

Hi All,

Thanks for all the responses...unfortunately the suggested solutions aren't doing the trick.

I think its still trying convert the varchar 'various' to a date format even when you've asked it set 'various' as '01/01/2010'

Oh well!

|||

Run this query to see if you get any clues back...

SELECT DISTINCT
Start_Date,
ISDATE(Start_Date),
CASE Start_Date
WHEN 'Various' THEN 'Text'
ELSE 'Date'
END = '" + date + "'
FROM
table1

My guess is that you will find other values (as well as 'Various') that aren't dates. - Problem areas will be lines that think they are dates, but that aren't.