Cannot properly parse sql script while trying to parse using TSql100Parser

Question

I am using ScriptDom for Parsing a SQL Script. My program is as under

static void Main(string[] args)
        {
            string script = @"
                            SET QUOTED_IDENTIFIER ON
                            GO

                            SET ANSI_NULLS ON
                            GO

                            CREATE PROCEDURE dbo.ws_Device_Update
                            (
                                @ApplicationId uniqueidentifier   ,
                                @OriginalApplicationId uniqueidentifier   ,
                                @DeviceIMEI nvarchar (50)  ,
                                @OriginalDeviceIMEI nvarchar (50)  ,
                                @ModelId int   ,
                                @DeviceName nvarchar (50)  ,
                                @DeviceDescription nvarchar (255)  ,
                                @DeviceState int   ,
                                @IsExpired bit   ,
                                @IsSuspended bit   ,
                                @LastAccessed datetime   ,
                                @ClientSeqNo bigint   ,
                                @ServerSeqNo bigint   ,
                                @ModelCode varchar (50)  ,
                                @PushToken varchar (512)  ,
                                @PushLastAlive datetime   ,
                                @PushLastDead datetime   ,
                                @DeviceType int   
                            )

                            AS

                            UPDATE dbo.[ws_Device]
                            SET

                                 [ModelId]              = @ModelId           --Does a device model change with same DeviceIMEI .I doubt it
                                ,[DeviceName]           = @DeviceName        --Does a device name change with same DeviceIMEI .I doubt it
                                ,[DeviceDescription]    = @DeviceDescription --Does a device description change with same DeviceIMEI .I doubt it
                                ,[DeviceState]          = @DeviceState       
                                ,[IsExpired]            = @IsExpired
                                ,[IsSuspended]          = @IsSuspended
                                ,[LastAccessed]         = @LastAccessed
                                ,[ClientSeqNo]          = @ClientSeqNo
                                ,[ServerSeqNo]          = @ServerSeqNo
                                ,[ModelCode]            = @ModelCode         --Does a device model code with same DeviceIMEI .I doubt it
                                ,[PushToken]            = @PushToken
                                ,[PushLastAlive]        = @PushLastAlive
                                ,[PushLastDead]         = @PushLastDead
                                ,[DeviceType]           = @DeviceType        --Does a device device type change with same DeviceIMEI .I doubt it
                            WHERE
                                [ApplicationId]         = @OriginalApplicationId 
                            AND [DeviceIMEI]            = @OriginalDeviceIMEI
                            ";

            IList<ParseError> parseErrors;
            TSql100Parser tsqlParser = new TSql100Parser(false);
            TSqlFragment fragment;
            using (StringReader stringReader = new StringReader(script))
            {
                fragment = (TSqlFragment)tsqlParser.Parse(stringReader, out parseErrors);
            }
            if (parseErrors.Count > 0)
            {
                Console.WriteLine(@"Errors encountered: ""{0}""", parseErrors[0].Message);
            }

            Console.ReadKey();
        }

Works fine.

But if I don't mention the

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

It fails

Errors encountered: "Incorrect syntax near CREATE."

My question is why it is happening....Without SET QUOTED_IDENTIFIER ON OR SET ANSI_NULLS ON, it is still a valid stored procedure.

OR is it mandatory to use those? Is there any way to bypass that?

it is still a valid stored procedure - Those directives are for the server & are not part of the stored procedures (they are outside CREATE) — Alex K.
– Alex K., Commented May 1, 2013 at 9:50

Arvind Shyamsundar · Accepted Answer · 2013-05-01 09:40:55Z

1

Priyanka the issue is that there is a (invisible) 0xFFFE just before your CREATE PROCEDURE text. You can verify this if you use the keyboard, go to the C character and move the cursor to the left. You will find that to the left of the C the cursor does not seem to advance to the left, unless you press the left arrow key again.

0xFFFE is the byte-order marker used to detect Unicode byte ordering, I suspect this has crept into your code in some way. For now, you can simply delete the hidden character by using the DEL or BackSpace key and it would fix it.

Also in future always pass in true to the constructor of the TSql100Parser class. Quoted Identifier handling is ON by default, so we need to respect that in the parser constructor.

answered May 1, 2013 at 9:40

Arvind Shyamsundar

113 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2299169 · Accepted Answer · 2013-04-23 10:43:47Z

0

Read these two articles:
QUOT.ID: http://msdn.microsoft.com/en-us/library/ms188048.aspx
ANSI: http://msdn.microsoft.com/en-us/library/ms174393.aspx

In your case, probably the lack of this latter one causes the issue as your UPDATE gets NULL(s)

answered Apr 23, 2013 at 10:43

user2299169

Collectives™ on Stack Overflow

Cannot properly parse sql script while trying to parse using TSql100Parser

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related