none
Parsing error with a special character

    Question

  • I have a stored procedure that I am passing in to it a parameter that has a "special" character; in this case the character is the degree sign °, but in another case I received the same error when using the character of an "e" with an accent above it. When I log on to the server and run Management Studio directly on the server that the database lives on, I get an error when parsing this script.

    The SQL I am running is as follows:

    DECLARE @GUIDOutput UNIQUEIDENTIFIER
    EXEC spMyStoredProcedure '129cd8fc-def4-488c-a55b-8f50c8ea6e38', 'N 40.4298° W 105.04096°', '1A99A369-777B-4E84-B6B3-B5C2B7D79A62', @GUIDOutput OUTPUT

    The error is:
    Query completed with errors:
    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near '1'.

    The second parameter has the degree sign, and the error seems to think that the quote ending the text is not closed, hence the incorrect syntax error.

    But when I log on to another server and connect to the same database using that other server's instance of Management Studio, everything works fine. The store procedure executes without error, and the data appears correctly in my application with the degree signs.

    I can only think that there must be some setting that is different in the two different instances of Management Studio on the two servers, but I cannot think what it could be. I'm using Management Studio version 10.0.5500.0 in both cases. The collation on the database is SQL_Latin1_General_CP1_CI_AS. I have also tried setting Quoted identifier on and off and it has made no difference.

    Tuesday, July 23, 2013 3:05 PM

Answers

  • Hi,

    What happens if you type your whole T-SQL sentence on the SSMS window from scratch and then execute it? (instead of copying and pasting from Notepad, Word, or wherever else)



    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu



    • Edited by Sebastian Sajaroff Thursday, July 25, 2013 7:05 PM missing word
    • Marked as answer by Bob Sala Thursday, July 25, 2013 8:25 PM
    Thursday, July 25, 2013 7:04 PM

All replies

  • Hi,

    May you, please, paste your stored procedure header definition (where you declare the number and type

    of parameters)?



    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Tuesday, July 23, 2013 3:17 PM
  • My stored procedure looks like this:

    CREATE PROC [dbo].[spMyStoredProcedure]
     @GUID1 varchar(50),
     @Value VARCHAR(MAX)=NULL,
     @GUID2 varchar(50),
     @GUID3 varchar(50)=NULL OUTPUT

    Tuesday, July 23, 2013 3:25 PM
  • Hi,

    Please, try changing the @Value parameter from VARCHAR to NVARCHAR


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Tuesday, July 23, 2013 3:34 PM
  • Sebastian,

    Thanks for the suggesstion, but unfortunately it didn't work. I changed the parameter to an NVARCHAR but I still receive the same error when parsing the statement.

    Bob.

    Tuesday, July 23, 2013 6:21 PM
  • In addition to changing parameter (and the column in the table) as nvarchar you need to pass your parameter as nvarchar, e.g. N'value goes here'

    N' preceding the value will indicate, that you're passing nvarchar value.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 23, 2013 6:40 PM
    Moderator
  • Also, if you can post your code, it may help. Are you using dynamic SQL in your code by any chance?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 23, 2013 6:41 PM
    Moderator
  • Looking closer at the code you use to invoke the procedure, I think I know what the problem is.

    I believe you need to change your call to be

    DECLARE @GUIDOutput UNIQUEIDENTIFIER 
    EXEC spMyStoredProcedure @Guid1='129cd8fc-def4-488c-a55b-8f50c8ea6e38', @Value =N'N 40.4298° W 105.04096°', @Guid2='1A99A369-777B-4E84-B6B3-B5C2B7D79A62', @GUIDOutput = @GUIDOutput OUTPUT

    In other words, I suggest to use named parameters. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 23, 2013 6:44 PM
    Moderator
  • Naomi,

    I'll give the named parameters a try. At this point, I probably won't be able to know if this works until tomorrow, but I appreciate the quick responses.

    Bob.

    Tuesday, July 23, 2013 7:10 PM
  • Naomi,

    I have been able to try the changes to my call to explicitly name the parameters, but this unfortunately did not work. I still received the same parsing errors.

    I really seems odd to me that my code will parse using a different instance of SQL Management Studio but not the instance of Management Studio on the server itself, which leads me to believe that there could be some setting in Management Studio that is causing the parsing error but I can't figure out what it might be.

    Bob.

    Wednesday, July 24, 2013 7:29 PM
  • Are you sure you're connecting to the same SQL Server version in both cases?

    Are you saying that exactly same code is parsing OK on the client and doesn't parse on the server itself?

    Also, are you getting this error in the compile time (parse time) or when you try to run the procedure?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 24, 2013 9:14 PM
    Moderator
  • Yes, I am connecting to the same database with the same version of 2 instances of Management Studio: 2008 SP3 (10.0.5500.0)

    The exact same code parses ok on the client but not the server. And I get the error when parsing (at compile time).

    Bob.

    Wednesday, July 24, 2013 11:23 PM
  • Weird - are both Server and Client have the same local settings (same OS language)?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, July 25, 2013 2:27 AM
    Moderator
  • All language settings are set to "Englsih (United States)" on both servers. This whole thing is very weird, which I why I had to turn to this forum for assistance. I am a SQL Developer with over 10 years of experience using SQL Server but this one has me completely baffled! 

    Bob.

    Thursday, July 25, 2013 1:19 PM
  • Some underlying software handles the extended ASCII degree character as single quote.

    SELECT ASCII('°'), CHAR(39);  -- 176	'

    176 - 127 = 39 (single quote)

    It may not be related to SQL Server. It may be at Windows level.

    You may try UNICODE and submit strings like N'N 40.4298° W 105.04096°',


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Thursday, July 25, 2013 2:21 PM
    Moderator
  • Hi,

    Based on latest Kalman's comment, please try the following piece of code

    DECLARE @X AS NVARCHAR(50) = N'N 40.4298° W 105.04096°'
    DECLARE @I AS INT = 1 
    WHILE @I <= LEN(@X) 
    	BEGIN
    		PRINT UNICODE(SUBSTRING(@X, @I, 1))
    		SET @I = @I + 1 
    	END

    Paste your string exactly as it is on SSMS (copy and paste).

    You should see a list of characters in the range between 0 and 255.

    If any of them is greater than 255 then you got trouble! (Because VARCHAR can't deal with characters

    above 255, those belong to Unicode "realm")


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Thursday, July 25, 2013 2:27 PM
  • Running the code provided by Kalman and Sebastian, I get the same results on both servers:

    176 °

    then:

    78
    32
    52
    48
    46
    52
    50
    57
    56
    176
    32
    87
    32
    49
    48
    53
    46
    48
    52
    48
    57
    54
    176

    Thursday, July 25, 2013 5:33 PM
  • Hi,

    Would it be possible to paste the stored procedure code?

    Maybe the problem is there, not in the input parameters


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Thursday, July 25, 2013 5:41 PM
  • I realize it is a reasonable request to post my stored procedure code, but I would prefer not to post that code for various reasons. One reason is that the error happens when just parsing the call to the stored procedure. It is my understanding that when you parse SQL in Management Studio, it is just checking the syntax and not digging in to the actual stored procedure to do anything beyond making sure that the parameters are correct. Since the error is occurring during the parse, the actual code of the stored procedure should not make a difference.

    Thursday, July 25, 2013 5:51 PM
  • Hi,

    I understand your concern about stored procedure source code.

    Does your SQL statement come from an MS Word document?

    DECLARE @GUIDOutput UNIQUEIDENTIFIER
    EXEC spMyStoredProcedure '129cd8fc-def4-488c-a55b-8f50c8ea6e38', 'N 40.4298° W 105.04096°', '1A99A369-777B-4E84-B6B3-B5C2B7D79A62', @GUIDOutput OUTPUT

    I'm using MS Word in French, and I noticed that everytime I copy an SQL statement from SSMS to Word

    (or vice-versa) all apostrophes ['] (ASCII 39) get replaced by some fancy Unicode cousins

    (Unicode 2018 and 2019) [‘] and [’]

    exec sp_helpdb 'Joe' turns into exec sp_helpdb ‘Joe’

    Obviously, I have to replace those weird apostrophes by the "right" ones.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Thursday, July 25, 2013 5:58 PM
  • Sebastian,

    You might be on to something with the copy and paste, because I do sometimes copy and paste from various sources. And also the more I think about this issue the more I think it might be due to something external to SQL Server (whether it be a server setting or something else related to the data). But in my above results from the code you asked me to run, didn't the fact that I got no ascii values higher than 176 (which is the degree symbol) mean that everything is being treated as expected? I am going to continue to try to "break" my code by trying to reproduce on the client server the copy and paste scenarios.

    Bob.

    Thursday, July 25, 2013 7:00 PM
  • Hi,

    What happens if you type your whole T-SQL sentence on the SSMS window from scratch and then execute it? (instead of copying and pasting from Notepad, Word, or wherever else)



    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu



    • Edited by Sebastian Sajaroff Thursday, July 25, 2013 7:05 PM missing word
    • Marked as answer by Bob Sala Thursday, July 25, 2013 8:25 PM
    Thursday, July 25, 2013 7:04 PM
  • Sebastian,

    Typing in the special character works, meaning that it parses correctly when I type it in. So there must be something in what is happening with the copy and paste that is causing the issue. Even though I have not yet been able to identity exactly what I am doing "wrong", your suggession to type it in works for me. Thanks.

    Bob.

    Thursday, July 25, 2013 8:25 PM