saving emoji in sql not working
-
I have been struggling for hours now and I am getting desperate.
I have a tool that stores WhatsApp messages to my SQL database. These messages are delivered to my server, and I save them in a table containing a column named 'body' type nvarchar(2000)
Saving the message is through a store procedure "exec stp_WhatsApp_Save" with a parameter "@body nvarchar(2000)"
In that procedure I do something likeinsert into WhatsApp_Out (body) values (@body)
This result in the database as a few strange characters, no emoji is seen.Now the strange thing is: for debugging I write that sql command I sent to the server to a log text file:
exec stp_WhatsApp_Save N'Hello 😀'
, when I copy the command from that file and paste it in my SSMS, the result is okay and the emoji is good.
Anybody knows how this can be? -
SQL Server seems to treat these special characters oddly.
The reverse conversion doesn't seem go back to the original character
select ascii('😀'), nchar(ascii('😀'))DECLARE @a nchar(1)=N'😀'
select N'😀' [N'😀'],
'😀' ['😀'],
@a [@a],
ascii(N'😀') [ascii(N'😀')],
ascii('😀') [ascii('😀')],
ascii(@a) [ascii(@a)],
nchar(ascii(N'😀')) [nchar(ascii(N'😀'))],
char(ascii('😀')) [char(ascii('😀'))]
-
you have to use nvarchar(2), NOT nvarchar(1)
-
I suspect that when writing the command to the log file some sort of correction or formatting happens so that the command is correct when copy/pasted into SSMS???
-
To make a long story short...
Tried all kinds of string encoders on the sql command before execution but that did not work.
But if I save my command to a text file, read it back again, and then execute that sql command, all is well and the emoji is in the db.
Any ideas???
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply