CODEWITHSUNDEEP
sqlsql-serversyntax
badamtisss
badamtisss

Reputation: 1

Incorrect syntax near 'OFF'

I can't find anything related so please, I am knew

I am using SQL Server 2012.

I created a script using Visual Studio 2012 data compare so I can update one database to another.

The script (43k + lines), when ran, says query completed with errors (its set to roll back if an error occurs) but displays no messages.

Then I added a try/catch as shown below:

begin try
{script}
end try
begin catch
    --returns the complete original error message as a result set
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage

    --will return the complete original error message as an error message
    DECLARE @ErrorMessage nvarchar(400), @ErrorNumber int, 
            @ErrorSeverity int, @ErrorState int, @ErrorLine int

    SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState,       @ErrorNumber,@ErrorLine)
end catch

And it says

Incorrect syntax near 'OFF' at line 13

below is line number and script..

SET NUMERIC_ROUNDABORT OFF (Line 13)
GO (Line 14.. etc)
SET XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL,   ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

Changing Line 13 so that the GO is the last word (so combine 13 adn 14) gives the errors

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'GO'.

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'ON'.

Which makes me think some weird cr lf issue but I cant seem to figure it out.

I've use the script creation tool many times and have not had an issue in the past.

Thank you for your time.

Edit: the following code

SET NUMERIC_ROUNDABORT OFF GO
SET XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

gives an error at line 1

This issue has been fixed: It was fixed by me being frustrated and hitting the enter key 5 times at the beginning, making all the lines shift down 5. Now it works. I don't understand. If someone can explain, I'll give you an internet cookie or something.. idk...

Upvotes: 0

Views: 1384

Answers (1)

kamilk
kamilk

Reputation: 4039

GO is not a Transact-SQL statement - it's an instruction to SQL Management Studio and other utilities saying that at this point they are supposed to break down your script into two separate batches. It's basically as if you first ran one file up to the point of GO and then another one starting just after the statement.

What this means is that that your first batch's syntax is incorrect because it has a BEGIN TRY without a matching END TRY. You need to get rid of GO, or, if it's not possible, wrap every batch into its own try/catch.

Upvotes: 1

Related Questions