nvarchar column shows ???? when Inserting Hebrew characters 1

[thelordoftherings]

Hello,

I have created a table column as nvarchar in order for it to accept Hebrew characters. The problem is that when I go to this column and enter a hebre string manually I see it in hebrew but when I do the Insert using a Query through the Query Analyser I see ????? at this column.
I tried to change this column Collation to Hebrew and even the machine Regional settings but with no success.
Anyone has an Idea?

Roy

 

[mrdenny]

What application are you seeing this in? Is this in Enterprise Manager or Query Analyzer?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[thelordoftherings]

When I use the Enterprise Manager to insert a record manually I see Hebrew at this column but when I use Query Analyzer to do the Insert using a Query I see ???? at the same column.

 

[mrdenny]

Run a trace on the SQL Server then do the insert and check the trace. It may be doing something that your not thinking of when you write your insert script. Can you post your exact update script.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[thelordoftherings]

Here is the INSERT, very simple one:
INSERT INTO LOANS (record_number, more_details) VALUES (1, '????')

How do I do a trace?

Roy

 

[mrdenny]

You use Profiler to do a trace. You need to do your insert like this.

INSERT INTO LOANS 
(record_number, more_details) 
VALUES 
(1, [COLOR=red]N[/color]'????')

You need the N infront of the uni-code data so that it it stored correctly.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[thelordoftherings]

10X mrdenny, it is working. But can you please explain me why...?

 

[mrdenny]

When you enter in plain text data via an insert command SQL Server assumes that the data is using the data type varchar. To specify nvarchar you need to put the N in front of it.

If you ran a trace on SQL Server using profiler which you did the insert in Enterprise Manager you'll see that the N is there as well. By default Microsoft setup Enterprise Manager to do all it's inserts with the N just in case.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[thelordoftherings]

Now I understand, thank you very much for to simple yet comprehensive explanation. One mre question though: Since Administrating SQL Server is not my stronger side, can you please explain me how can I activate and see a trace on a query?

Thnk you in advance,
Roy

 

[mrdenny]

To run a trace you need to use SQL Profiler. Start > Programs > Microsoft SQL Server > Profiler. When it opens click new (top left) enter the server run click ok (or login, or whatever). If you want to make any changes to the trace defination you can on the next screen (you probably won't need to), then click run. Every command that is run on the SQL Server will now be scrolling accross your screen. Do the action you want to in EM then stop the trace (use the little stop button at the top; creative aren't they). Then hunt through the list of transactions looking for the right one. There is also a find feature (Control F or F3 should to the trick).

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[thelordoftherings]

Hello mrdenny,

I run the query: INSERT INTO LOANS (record_number, more_details) VALUES (1, '????'), searched the trace but I don't see that it adds N infront of it as you said...

Roy

 

[mrdenny]

You have to put the N in manually. If you insert data via the data view windows in Enterprise Manager it will take care of it automatically. When you do it via code in Query Analyzer you'll need to put the N in manually.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[thelordoftherings]

Hey mrdenny,

Can you please show me how to combine this "N" with a Stored Procedure that receives this string as a parameter?

Thank you in advance,
Roy

 

[mrdenny]

You have to put the N in when you are putting the data into the variable in the exec line when you run the procedure. Make sure that your variable types are set for nvarchar instead of varchar.

create procedure usp...
   @var1 nvarchar(100)
as
...
go
exec usp_... @var1=N'data goes here'
go

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[thelordoftherings]

10X a lot!

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)