OSQL not differentiating between 'N' and 'Ñ'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

OSQL not differentiating between 'N' and 'Ñ'

Author

Topic

ratinakage
Starting Member

16 Posts

Posted - 2006-10-26 : 07:54:38

Hey

I'm having a problem when running some sql code with the OSQL interpretter. The inserts I am running look like this:

INSERT INTO lookup_elem (lookup_id,value,tag) VALUES(9,'N',2503)
INSERT INTO lookup_elem (lookup_id,value,tag) VALUES(9,'Ñ',2517)

I get back the following error: Cannot insert duplicate key...

I think it reads the 'N' and 'Ñ' as the same thing. Does anyone know an alernative to this? Perhaps using an ASCI character? My sql is not that great so Im not entirely sure of the syntax.

Thanks...!

G

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-26 : 07:58:58

It seems that you have accent-insensitive collation.

Run following command and check out the collation of your working database:

select databasepropertyex('database', 'collation')

Harsh Athalye
India.
"Nothing is Impossible"

ratinakage
Starting Member

16 Posts

Posted - 2006-10-26 : 08:55:37

I get a NULL back

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-26 : 09:01:54

[code]select databasepropertyex('Northwind', 'collation')[/code]

Did you change the word database to your actual database name ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-26 : 09:02:05

Replace 'database' with the name of your working database.

Harsh Athalye
India.
"Nothing is Impossible"

ratinakage
Starting Member

16 Posts

Posted - 2006-10-26 : 11:31:59

Ah

I get back, SQL_Latin1_General_CP1_CI_AS

ratinakage
Starting Member

16 Posts

Posted - 2006-10-26 : 11:36:05

By the way,

The same sql command works in SQL Query Analyser. So I don't think its a problem with my database's collation. I think its more an OSQL error.

Any ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-26 : 11:51:50

The collation for a column is not necessary the same as for database.
Use this instead

select collation_name from information_schema.columns
where table_name = 'lookup_elem' and column_name = 'value'

Peter Larsson
Helsingborg, Sweden

ratinakage
Starting Member

16 Posts

Posted - 2006-10-30 : 02:12:43

Once again, I get SQL_Latin1_General_CP1_CI_AS.

I think what I need is some sort of OSQL command for inserting a Ñ, given it's ASCI value rather than just a string literal. I know I could just type this into Enteprise manager directly, but I need this for an installer.

Cheers,
Guy

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 02:15:43

What code page are you using for creating the text file with osql commands?

Peter Larsson
Helsingborg, Sweden

ratinakage
Starting Member

16 Posts

Posted - 2006-10-30 : 02:31:53

I am not sure what you are asking... The text file is actually auto created using an in house product. It copies all values from tables and creates large insert scripts from the data. Used to create insert table scripts for installers.

The inserts look fine in the text file but 'Ñ' becomes 'N' when they are run.

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-10-30 : 15:33:39

quote:
Originally posted by ratinakage

INSERT INTO lookup_elem (lookup_id,value,tag) VALUES(9,'N',2503)
INSERT INTO lookup_elem (lookup_id,value,tag) VALUES(9,'Ñ',2517)

I get back the following error: Cannot insert duplicate key...

I think it reads the 'N' and 'Ñ' as the same thing. Does anyone know an alernative to this? Perhaps using an ASCI character? My sql is not that great so Im not entirely sure of the syntax.

Am I totally misunderstanding this.. it looks like the error is with the 9's (lookup_id) not the N's (value)...

ratinakage
Starting Member

16 Posts

Posted - 2006-11-02 : 06:03:32

Unfortunately not.

These are just two lines out of an entire spanish alphabet. Only these ones cause the error. The first and second columns together are the primary keys.

My questions restated:

Does anyone know any other way of inserting this value other than using the insert statement that I have there (perhaps by means of ASCI value). These statements work perfectly using MS SQL Query analyser and the results permiate into the database. The problem happens when I run them using my OSQL based installer. So I am hoping theres some different way of telling OSQL to insert them...

Any help would be appreciated...

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources