Prefix N to unicode not always working

tentrade

I am stumped.  It seems that sometimes it works and other times it does not.

One minute it will work, then a minute later, on the same code it won't.

No examples to post because sometimes it works and sometimes it doesn't EVEN ON THE SAME CODE.

Anybody else have this problem? Any ideas?

Using SQL Prompt Version 9.4.6.7396. Tested on both SSMS 16 and 17.

Alex B

Hi @tentrade,

It would be good to get at least one sample of what you have seen this happen to, even if that is not occurring the entire time- including the DECLARE and the SET statements.

This should only be adding the N to strings that variables of datatype NVARCHAR or NCHAR are being set equal to.  I've found that it appears to only do this for those in a SET statement, not those that are a part of the DECLARE statement and raised an issue for this.

E.g. 

<div>DECLARE @a NVARCHAR(3) = 'abc';</div><div>DECLARE @b VARCHAR(3) = 'def';<br><div>SET @a = 'ABC';</div><div>SET @b = 'DEF';</div></div>

turns into

<div>DECLARE @a NVARCHAR(3) = 'abc';</div><div>DECLARE @b VARCHAR(3) = 'def';<br><div>SET @a = N'ABC';</div><div>SET @b = 'DEF';<b></b></div></div>

where I think the 'ABC is prefixed, and both @b are not, but I think 'abc' should also be prefixed with N.

Please share one of your examples of this and I'll investigate further.

Kind regards,
Alex

Alex B

Hi @tentrade,

The issue I mentioned above should now be fixed in the release today 9.4.9.7873.  If there is a different issue or that doesn't help you please let us know!

Kind regards,
Alex

tentrade

Hello Alex.

I am so sorry for not responding to you. I had not checked back here since you posted your replies and the emails had escaped my attention. I just now came here to look at it because I got the message about the update being available and I saw that this issue had been addressed.

Yes, that looks like it did it! Thank you.

This is actually a more important subject than some people might think. I recently was "tripped" up by not prefacing a string with the "N". I had never thought it actually had any practical impact and that all conversions were taken care of implicitly where needed. However, I had a query that wasn't working correctly and it baffled me, until I added the "N" and magically it worked! From that moment on I realized that it IS necessary to put the "N" before unicode strings.

I cannot remember the exact query that this problem occurred in but if I do I will post it.

This leads me to another extension request for this feature. Do you think we could make it add the "N" in places other than assignments? For example in comparisons? What I mean is, take the following simple select statement:

SELECT *

FROM   table1

WHERE  column1 = 'mystring';

If column1 is a Unicode string, then could it also add an "N" before 'mystring'?

P.S. SSMS 18 Preview is available now. Is there a timeline on when SQL Prompt will be available for it? I notice that on my system that SQL Search IS working in SSMS 18 but sadly SQL Prompt is not.

Alex B

Hi @tentrade,

You should create a Uservoice suggestion for the feature request for prefixing N to all appropriate string literals as that is additional functionality onto the existing/intended functionality.

For the P.S. - unfortunately SQL Prompt doesn't and has no plans to support preview versions, though the majority of other tools now work in it.

Kind regards,
Alex