×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Insert into query adds records to unreadable format, how do I change that?

Insert into query adds records to unreadable format, how do I change that?

Insert into query adds records to unreadable format, how do I change that?

(OP)
Hello everybody, I use Microsoft SQL Server Management Studio 19.1 (Microsoft SQL Server 2022)
I have created a database with one table Customers. When I run an INSERT INTO QUERY record is inserted but in unreadable format.

CODE --> SQL

INSERT INTO Customers (custID,custAFM,custDOY, custName,custProfession,custHomePhone,custMobilePhone,custEmail,custAddress,custCity,custPrefecture,custPostCode,custFloor) 
VALUES('EL00000001','000000000','Αθηνών','Ασημακοπούλου Αγγελική','','','','','','','','',''); 

When I display all records with

CODE --> SQL

SELECT * FROM Customers; 

I see record with this format EL00000001 000000000 ??a??? ?s?µa??p????? ???e????
Initially I had the typeof the field nchar, I changed it to nvarchar without effect and I also changed it to text without effect too.
Any suggestions please? Why does this happen? Thank you so much in advanced.

RE: Insert into query adds records to unreadable format, how do I change that?

you need to specify the strings as National strings e.g. N'X'

CODE

drop table if exists #customers

create table #Customers (custID NVARCHAR(200)
,custAFM NVARCHAR(200)
,custDOY NVARCHAR(200)
, custName NVARCHAR(200)
,custProfession NVARCHAR(200)
,custHomePhone NVARCHAR(200)
,custMobilePhone NVARCHAR(200)
,custEmail NVARCHAR(200)
,custAddress NVARCHAR(200)
,custCity NVARCHAR(200)
,custPrefecture NVARCHAR(200)
,custPostCode NVARCHAR(200)
,custFloor NVARCHAR(200)
) 

INSERT INTO #Customers (custID,custAFM,custDOY, custName,custProfession,custHomePhone,custMobilePhone,custEmail,custAddress,custCity,custPrefecture,custPostCode,custFloor) 
VALUES('EL00000001','000000000','Αθηνών','Ασημακοπούλου Αγγελική','','','','','','','','','')
, (N'EL00000001',N'000000000',N'Αθηνών',N'Ασημακοπούλου Αγγελική','','','','','','','','','')
; 

select *
from #Customers 

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Insert into query adds records to unreadable format, how do I change that?

(OP)
Thank you so much, I finally used NChar and NText wherever it need and it definetaly worked with N char in the insert into query.
Do I have to change the collation?

RE: Insert into query adds records to unreadable format, how do I change that?

Quote:


Do I have to change the collation?

Be very careful about changing the collation of anything. This is a very complex issue and should not be taken lightly.

Collations do not affect how the data is stored. It only affects how data is sorted and compared. There is a default collation on the SQL Server instance. This is used whenever you create a new database. There is a default collation for each database. This affects how strings columns are created unless you specify the collation. In fact, each and every string you create has a collation. If you don't have a consistent collation everywhere, you will likely find yourself specifying the collation in lots of unexpected places.

The biggest "gotcha" I experienced was when I sold my software to a customer in another country. The default collation was different and lots of things started failing. Specifically, I had problems with temp tables and table variables. These things are created in the tempdb, which is recreated whenever the instance is restarted. The default collation for tempdb is the default collation for the instance, which happened to be different from the default collation for my database. This is not a situation you want to get in to.

I agree with Frederico, basically, don't mess with the collation.

My database uses a case-insensitive collation (where A = a). The only time I use the collate clause in my application is when I want a case sensitive compare, which really only happens in 1 or 2 places.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Insert into query adds records to unreadable format, how do I change that?

nchar and nvarchar (Transact-SQL)

These fields store UTF-16, which is able to support any language. So there is no thought necessary which collation supports Greek or whatever other language.

So the collation you set for your instance, database, tables or columns are only concerned with sorting and comparing aspects, not what languages are supported ot unsupported, they can be specialized on the rules of sorting for a specific language, some countries even have multiple norms about that.

So I'd conclude listen to both fredericofonseca and gmmastros. It surely is good at the start of defining your server and database to choose a collation that matches your needs. And obviously you need to be aware of the tempdb woes you could have, if you run on a server with another default collation than you need.

Chriss

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close