Unexpected error in GDR when using UPDATETEXT statement: TSD02010: Incorect syntax near END

Question

I have a project imported from SQL 2000 database server that contains thousand of objects (tables, sp, view, functions, etc).
All the .sql source files that contains UPDATETEXT statement appeas on VSDB as Error, the description is complaining as the statement is not valid.

For example: the following code report to have a problem on the line just before the last END keyword:

[...]
IF @Position > 0
    BEGIN
        SET @PropStr = ISNULL(( SELECT  SUBSTRING(ExtraProperties, @Position,
                                                  8000)
                                FROM    SYC_Fields
                                WHERE   ID = @ObjectID
                              ), '')
        SET @PropLen = ISNULL(PATINDEX('%' + @CRLF + '%', @PropStr), 0)
        IF @PropLen = 0
            SET @PropLen = LEN(@PropStr)
        ELSE
            SET @PropLen = @PropLen - 1
        IF @Position = 1
            SET @Position = 0
        ELSE
            BEGIN
                SET @Position = @Position - 3
                SET @PropLen = @PropLen + 2
            END
        SELECT  @ptrval = TEXTPTR(ExtraProperties)
        FROM    SYC_Fields
        WHERE   ID = @ObjectID
        UPDATETEXT SYC_Fields.ExtraProperties @ptrval @Position @PropLen
    END
[...]

I search the forum and I saw that there are some statement that cannot be very well interpreted by compiler so some advanced users recommend an alternative of writing the same code. But in my situation (UPDATETEXT) there is not alternative, because there are no other way of updateting just a part of text/image column.

Any suggestion?

Answer 1

Eugen,

The error message is caused by a bug in our parser.  The parser expects a literal or variable to be specified.  We will be fixing the parser bug in a future release.

SQL Server allows the parameter to be omitted, but the statement will not update text it will remove it. To work around the issue specify a value for the statement. If your intention is to remove text specify NULL.  Here is an example:

DROP TABLE TestUpdateText

GO

CREATE TABLE TestUpdateText(

	Id INT IDENTITY(1,1) PRIMARY KEY,

	TestText NTEXT

)

GO

INSERT TestUpdateText(TestText) VALUES('123456789'),('123456789'),('123456789')

GO

SELECT * FROM TestUpdateText

GO

DECLARE @ptrval binary(16)

SELECT @ptrval = TEXTPTR(TestText) FROM TestUpdateText WHERE Id = 1

UPDATETEXT TestUpdateText.TestText @ptrval 5 1

GO

DECLARE @ptrval binary(16)

SELECT @ptrval = TEXTPTR(TestText) FROM TestUpdateText WHERE Id = 2

UPDATETEXT TestUpdateText.TestText @ptrval 5 1 NULL

GO

DECLARE @ptrval binary(16)

SELECT @ptrval = TEXTPTR(TestText) FROM TestUpdateText WHERE Id = 3

UPDATETEXT TestUpdateText.TestText @ptrval 5 1 '?'

GO

SELECT * FROM TestUpdateText

---

Barclay Hill Program Manager VSTS: DB Team (DataDude, DBPro, Database Edition) Please mark the responses as answer if it resolves your question.

Answer 2

Hello Barclay,

I have a similar issue to the one raised by Eugen, the difficultly I have is that the stored procedure has been provided by an external 3rd party development house. If the code had been written internal I would happy to follow the suggestion you have rasied. But as we do not as such "own" the code this is not an option I have.

An example of the T-SQL is as follows :


I have tried using a varition of the "Suppress Warnings" within the Build tab approach suggested , so that both 4151 & 2010 are suppresed. But as this is an errror not a warning it does not appear to work.

Do you have any suggestions as to how I can move forward on this? Is there a potenial timeline on when this parser bug will be resolved, potentially I can just wait to add this database to out VSTS set up, until then?

Many Thanks,


Richard

Answer 3

Richard,

This bug will be fixed in the next version of the product (Currently Visual Studio Team System Development 2010) or the next time we service the GDR. 

Errors are not suppressible, only warnings.

You can set the file as "Not In Build" or remove the file from your project.  This works as long as you dont enable "DROP objects in the target that dont exist in the project" option. 

If the stored procedure that causes the issue is in a referenced database project you can suppress the error when adding the reference.

Thanks,

---

Barclay Hill Program Manager VSTS: DB Team (DataDude, DBPro, Database Edition) Please mark the responses as answer if it resolves your question.

Answer 4

Hey Barclay,

Let me be sure I understand.  All of the discussion about specifying literals to allow builds, etc. are work arounds, right?

If I have one database object that refers to another and use a literal to get around build errors in one of them, I'll have to go back at some point and restore the true reference.  Is that right?  Or am I missing something important.

Here's what I'm looking for:

On the Add Database Reference dialog, there is an option to select 'Define Database Variable'.  If I check that box, I can also check the 'Literal' option.

What does this mean?  And more importantly, how does it change the behavior of the reference to the database in question?

Thanks for your help and explanation - Jonathan

Answer 5

Hi Jonathan,

This is a common question.  Database References use variables and optionally literals.  If you use a variable only your 3/4 part references will look like:

SELECT SomeCols FROM [$(MyProject)].[dbo].[Table1]

The variable will be substituted at the time of deployment.

If you use a literal in addition to a variable the same would look like this:

SELECT SomeCols FROM [MyProject].[dbo].[Table1]

The variable is nice since it can be easily changed if the referenced database name changes on the server.

Thanks,

---

Barclay Hill Program Manager VSTS: DB Team (DataDude, DBPro, Database Edition) Please mark the responses as answer if it resolves your question.