sql server mseq: N prefex in a non-unicode database -- sql server mseq

N prefex in a non-unicode database

kiran_yalla
7/15/2005 7:09:03 AM

sql server mseq: This problem is about N prefex in a non-unicode database.
Collation name of the database is "SQL_Latin1_General_CP1_CL_AS".

Though the database is a non-unicode database, our application generates
N prefexed (before the string constants) queries. This is because, our
assumption is that non-unicode databse in sql2000 works fine with regular
string constants and N prefixed string constants.

The query is as follows.
SELECT iG_Category_Ita FROM Company WHERE iG_Category_Ita NOT LIKE N'%'

Here iG_Category_Ita is of type varchar(50) and it can take NULL values.

The problem here is, the out put we are getting when the string is not N
prefexed
is diff from the query with N prefexed.

In other words,
1. Output of SELECT iG_Category_Ita FROM Company WHERE iG_Category_Ita
NOT LIKE N'%'
is diff from
2. Output of SELECT iG_Category_Ita FROM Company WHERE iG_Category_Ita
NOT LIKE '%'

second query is returning table rows whose iG_Category_Ita = NULL but first
query is not returning NULL rows.

Can someone please help me if our assumption is correct or not?

Thanks & Regards,

RE: N prefex in a non-unicode database

kiran_yalla
7/17/2005 11:36:02 PM

Thanks Michael for the info.

My doubt here is, can we use Nâ€™%â€™ for varchar/char types? We generalized our
application earlier to generate N prefixed string constants irrespective of
whether the type is nvarchar/nchar or varchar/char. I would like to know if
this generalization is valid or not.

Regards,
Kiran

[quoted text, click to view]

RE: N prefex in a non-unicode database

kiran_yalla
7/17/2005 11:45:02 PM

Michael,

I would like to add one more point here. This generalization works fine in
all other cases. We are facing problem only in this particular case. I would
also like to know if Microsoft is aware of this situation/problem.

Regards,
Kiran

[quoted text, click to view]

RE: N prefex in a non-unicode database

v-mingqc NO[at]SPAM online.microsoft.com
7/18/2005 12:00:00 AM

Hi Kiran,

Welcome to use MSDN Managed Newsgroup!

'N' is used to represent Unicode character so when you use N'%' it
indicates that the potential data types that you are using is either
nvarchar or nchar (both are used to support unicode character sets) where
are when we use '%' the data types would be char or varchar.

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

RE: N prefex in a non-unicode database

v-mingqc NO[at]SPAM online.microsoft.com
7/19/2005 10:16:24 AM

Hi Kiran,

Thanks for your update.

- My doubt here is, can we use N'' for varchar/char types?
No, it is not recommanded to do so. But if your database is an English one
and it works for most times, it think it's OK if you leave them what they
used to be.

Yes, I have realized SQL cannot handle the NULL values after convert() but
not sure why. I will let our development guys know about this.

Thanks for your patience and cooperation.

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server mseq group.