Can any body clarify my issue please ? [Resolved]

Posted by Sanyok under Sql Server on 3/18/2010 | Views : 1311 | Status : [Member] | Replies : 4
Hello

I am Sanyok, I was serching something on sql server. So, I got this site. please clarify my doubts.

I am just opening new query window and doing the query(my database is Adventureworks)

my query is
select 'Both are A' 'Result' where 'A' = 'a'

Normally, the query should return the folloiwng result as..

Result
---------
Both are A

But, In my system Its not. only the result is (In my office, its working fine the same query, but why not in my system)

Result
-------

Is any problem in my installation of sql server 2005 / any other issue ? because i am not very strong in sql.

waiting for reply
thanks
Sanyok K




Responses

Posted by: Pandians on: 3/18/2010 [Member] [MVP] Silver

Up
0
Down

Resolved
Hi

You are in right place to get clarify.

Your problem is looks like COLLATION. Just clarify yourself with the following workarround.

1. Modify your query and run as given below
select 'Both are A' 'Result' where 'A' = 'A'
(or)
select 'Both are A' 'Result' where 'a' = 'a'

- If its working fine ? Try the folloiwng... Your database is Adventureworks. Correct ? Try the folloiwng script
Use Adventureworks


DECLARE @DBName VARCHAR(50)
SELECT @DBName = DB_NAME()

IF EXISTS(SELECT 1 FROM fn_helpcollations() WHERE [name]=DATABASEPROPERTYEX(@DBName,'Collation')
AND description LIKE '%case-sensitive%')
SELECT 'YES. Your Database Is Case-Sensitive' 'Sensitivity'
ELSE
SELECT 'NO. Your Database Is Not Case-Sensitive' 'Sensitivity'

- What is the result of the script given ?

If the result is "YES. Your Database Is Case-Sensitive " then the root cause is COLLATION setting of your database(Adventureworks).

Solution :
- Try to change the COLLATION setting of your database.(If required)
(or)
Try the following query instead
Suppose, If your Database COLLATION is SQL_Latin1_General_CP1_CS_AS then try this...
SELECT 'Both are A' 'Result' WHERE 'A' = ('a' COLLATE SQL_Latin1_General_CP1_CI_AS)

I think, Now your problem solved If my guess is correct.

please come back, If you have any issue further.

Cheers


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sanyok, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 3/18/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Good Day Sanyok

First your query is not correct.

you wrote

select 'Both are A' 'Result' where 'A' = 'a'


it has to be like this

select 'Both are A'  as 'Result' From MyTable   where A = 'a'




The Bolded part are your mistake. is that you don't mention the source table and after the where you must give the source field that you want to filter with. You say in your office this works, i doubt it will work anywhere.

Lastly you said

Is any problem in my installation of sql server 2005 / any other issue ? because i am not very strong in sql.


as long as you were able to write a query then it has nothing to do with the installation. One more thing is that when you post in a forum always include the error that you are receiving. we know almost all errors people encounter and it will be easy for us to help you.

Thank you for posting at DotnetFunda

Vuyiswa Maseko

Thank you for posting at Dotnetfunda
[Administrator]

Sanyok, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 3/18/2010 [Member] [MVP] Silver

Up
0
Down
Dear Vuyiswa

The syntax is not a problem.

In this scenario in sql server will work as a "Constant Internal Table" and Plan will be "Constant Scan" instead of "Table Scan" (like a Physical Table)

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sanyok, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sanyok on: 3/18/2010 [Member] Starter

Up
0
Down
Hello

Thanks, thanks, thanks

Your guess is correct. I got the solution.

thanks again
Sanyok K

Sanyok, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response