Can any body clarify my issue please ? [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

0	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

0	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

Latest Posts