A fatal scripting error occurred incorrect syntax was encountered while parsing go - Исправление ошибок и поиск оптимальных решений проблем

How to set dynamic count for GO statement?

I am getting the following error:

A fatal scripting error occurred.Incorrect syntax was encountered
while parsing Go.

when I tried to run the below query:

Declare @count int
Select @count=COUNT(*) From Users 

Insert Into #DummyUsers 
Select * from Users where UserName = 'Sachin' 

GO @Count

But the same is working fine when I use the below query with hard coded count.

Declare @count int
Select @count=COUNT(*) From Users 

Insert Into #DummyUsers 
Select * from Users where UserName = 'Sachin' 

GO 5

Appreciate your suggestions if you have any idea on this.

gotqn

40.2k46 gold badges156 silver badges241 bronze badges

asked Mar 10, 2015 at 19:39

You can’t. As soon as SSMS encounters GO the batch is terminated and your variable no longer exists.

answered Mar 10, 2015 at 19:41

Sean LangeSean Lange

32.7k3 gold badges26 silver badges39 bronze badges

You can’t use a variable for the count parameter to GO, but in your example (which may be contrived) you could just join back to Users :

Insert Into #DummyUsers 
Select U.* from Users U
INNER JOIN Users U2
    ON U.UserName = 'Sachin'

Other options:

Dynaimc SQL (building up SQL by concatenating strings) and executing via SQLCMD.EXE or OSQL.EXE
Using a WHILE loop with a counter

answered Mar 10, 2015 at 19:46

D StanleyD Stanley

148k11 gold badges174 silver badges238 bronze badges

If you simply want to insert a repeated row you could use a CTE or numbers table.

-- Sample data.
declare @Users as Table ( UserId Int Identity, Name VarChar(16) );
insert into @Users ( Name ) values
  ( 'Bob' ), ( 'Carol' ), ( 'Ted' ), ( 'Alice' );
select * from @Users;

-- Load another table with repetitions of a single user.
declare @TempUsers as Table ( UserId Int, Name VarChar(16) );
declare @Repetitions as Int = ( select Count(*) from @Users );
with TempUsers as (
  select UserId, Name, 1 as Repetitions
    from @Users
    where Name = 'Ted'
  union all
  select UserId, Name, Repetitions + 1
    from TempUsers
    where Repetitions < @Repetitions
  )
insert into @TempUsers ( UserId, Name )
  select UserId, Name
    from TempUsers;
select * from @TempUsers;

answered Mar 10, 2015 at 22:07

HABOHABO

15k5 gold badges37 silver badges57 bronze badges

Instead try this.

DECLARE @cntr INT=1

WHILE @cntr <= @count
  BEGIN
      INSERT INTO #DummyUsers
      SELECT *
      FROM   Users
      WHERE  UserName = 'Sachin'

      SET @cntr+=1
  END

answered Mar 10, 2015 at 19:44

Pரதீப்Pரதீப்

90.6k17 gold badges128 silver badges167 bronze badges

I would just loop it

Declare @count int
Select @count=COUNT(*) From Users 

WHILE(@count > 0)
BEGIN
    Insert Into #DummyUsers 
    Select * 
    FROM Users 
    WHERE UserName = 'Sachin' 

    SET @count = @count - 1;
END

answered Mar 10, 2015 at 19:50

StephanStephan

5,8311 gold badge15 silver badges24 bronze badges

While I agree with the others that there is likely a better way to achieve what you are trying to do, if there is some limitation that we are not seeing, you could look into using a sequence

The sequence you create persists and can be reset as needed and you can «increment» it by calling the NEXT VALUE FOR function

answered Mar 10, 2015 at 20:22

G BG B

1,41210 silver badges12 bronze badges

Источник

When you try to execute the code which has multiline comment after GO statement you get the error:

A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.

Following piece of code can be used to reproduce this error:

Try parsing this SQL Code:
[sql]
SELECT ‘A’
GO /*Completed the batch*/
[/sql]

The code looks perfectly fine but when you parse it you get the fatal error shown above. 🙁

Now try parsing this piece of code:
[sql]
SELECT ‘A’
GO –Completed the batch
[/sql]

This code parses successfully. 🙂

You get same error when you parse the below code:
[sql]
SELECT ‘A’ SELECT ‘B’
GO SELECT ‘C’
[/sql]

The code given below works fine, it runs the select statements in the batch for 4 times.
[sql]
SELECT ‘A’ SELECT ‘B’
GO 4
[/sql]

But this code fails while parsing:
[sql]
SELECT ‘A’ SELECT ‘B’
GO 4 /*Execute the batch times%
[/sql]

So in general(not completely sure) SQL Parser is giving error when GO statement has some text following it(except — or a number)

Alternate Fix:

Use single line comments(–) instead of multiline comments(/**/) after the GO statement

This error is logged with microsoft in the following link: Microsoft link

Источник

I have a Powershell script I created to script out the views and stored procedures in a database. Usually I just use it to find all the references to a particular table or column, but in this case I wanted to run the procedure on another database. When running the script, I ran into this error:
“A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.”

The error came from the SQL in this script. (The issue is dependent on formatting, I couldn’t get the format to come through in the WordPress page).

At first I was puzzled, everything looked fine to me. I did have the extra space in the front of the third line, so I deleted it and re-ran. Still with the same error. I knew that GO needed to be on its own line (unless I added a number to indicate I wanted the batch to run that many times).
I came across this post that described the same error. I didn’t have anything on the same line as the GO, but I decided to check my Powershell script. Instead if using a carriage return and line feed after the GO:
`r`n

I had inadvertently used a `v (a vertical tab) instead of a `n. Error on my part, but easily corrected.
Instead of regenerating my script, I could also save a copy of the script from SSMS.
In the Save dialog, select the arrow next to the Save button and select Save With Encoding.
Under Line Endings, change ‘Current Setting’ to ‘Windows (CR LF)’, then click OK and then Save.

Источник

I have the following script using SQLCMD setvar, but I receive an error when I parse the script:

:setvar dbname "PROD"
:setvar backuppath = "e:sqlrestorePROD.bak"

USE [master];
GO

-- backup the existing PRODUCTION database
BACKUP DATABASE [$(dbname)]
TO DISK = '$(backuppath)'
WITH COPY_ONLY, COMPRESSION

-- we need to clear the existing PRODCOPY users
-- before we restore and replace
IF DB_ID('PRODCOPY') IS NOT NULL
BEGIN
    -- Remove any connected users
    ALTER DATABASE [PRODCOPY]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    WAITFOR DELAY '00:00:05'
    ALTER DATABASE [PRODCOPY]
    SET MULTI_USER
END

-- restore PROD as PRODCOPY
-- ...

The error is:

A fatal scripting error occurred.
Incorrect syntax was encountered while parsing :setvar.

If I comment out the section:

IF DB_ID('PRODCOPY') IS NOT NULL
BEGIN
...
END

Then the error goes away. It seems like the use of SQLCMD is actually testing whether the PRODCOPY database exists, which of in this case it doesn’t.

I’ve tried to use profiler, but although I can see the SQL commands:

SET PARSEONLY ON
SET PARSEONLY OFF

However, profiler is not logging anything in between the two instructions that should be parsed, but I still see the error! Any ideas what I need to do to see the content that is supposed to be parsed by the server? Which columns, events to trace?

Is this an oddity with SQLCMDs?

Источник

(last updated: 2021-08-13 @ 19:50 ET / 2021-08-13 @ 23:50 UTC )

In a previous post, Prevent Full Script Execution (Understanding and Using PARSEONLY and NOEXEC), I explained how to easily disable a script using the PARSEONLY session setting. That same method can be used to disable one or more sections within a script instead of the entire script. But in either case, “disabling” doesn’t mean that the script, or section of code, will be skipped entirely as if it wasn’t there. It will still be parsed by SQL Server as that is not something that can be turned off. This means that you could still see parsing errors related to undeclared variables, syntax errors, etc.

Then one day I tried something silly that I figured wouldn’t work but wanted to try anyway (because quite often you don’t know until you know), and it actually did work (for the most part). I found a way to fully disable an entire T-SQL batch, and there really isn’t any indication that it happened. However, this “technique” is more limited than PARSEONLY as it only works on individual batches, and it only works in some environments.

The Thing That Does Nothing

What is this mysterious “technique”, you ask? Well, it’s none other than everyone’s favorite batch separator: GO. Yes, you read that correctly, GO. Anyone who works with SQL Server and has submitted queries via client tools such as SQL Server Management Studio (SSMS), sqlcmd, and others has used GO to separate batches of query statements. Some people put GO between most statements and that really isn’t necessary. There are some statements that require being the only statement in a batch, such as CREATE PROCEDURE and CREATE VIEW, in which case GO will be used (assuming that you will be running the whole script in one execution and not merely highlighting each section to execute individually, in which case GO isn’t needed).

The Official Word

Let’s take a look at the documentation to see what the behavior is supposed to be:

SQL Server Utilities Statements – GO

There are two things in particular that are of importance: syntax and where applicable.

Syntax

GO [count]
count
Is a positive integer.

A positive integer, eh? I guess we can rule out «-1», but what about «0»? While 0 is typically considered neither positive nor negative, there are some cases — some philosophical (e.g. Is zero positive or negative? ), some due to forgetting to check the minimum value of user input — when it’s a little more ambiguous. So, what would happen if we asked the client to send the T-SQL batch to SQL Server, well, never? It seems a little silly, but that’s hardly a reason to not try something, right?

Where Applicable

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

Apparently that list has not been updated to include: Visual Studio, Visual Studio Code, and Azure Data Studio.

GO is also recognized by many 3rd party utilities, including an open source, lightweight ADO.NET-based (i.e. not ODBC) replacement for sqlcmd that I wrote: Simple SQL Exec.

Why does this list of SQL Server clients matter? Well, due to GO being processed by the clients instead of SQL Server, there is the possibility of there being different behavior between the various clients.

Tests

Review

First let’s review what PARSEONLY ON does.

PARSEONLY is a session setting that is processed at parse time, prior to compilation (and execution, of course). This means that the location of the SET PARSEONLY statement within the batch is irrelevant since this setting controls whether or not to proceed to the next step / phase of the execution process. It can be at the beginning, middle, or end, and it doesn’t matter: the batch is being parsed no matter what.

In the following example, there is no output because PARSEONLY is set to ON within the batch:

-- No output:
GO
SET NOCOUNT, PARSEONLY OFF;
GO

SELECT 1;

SET PARSEONLY ON;

SELECT 2;

GO
SET PARSEONLY OFF;
GO

If there are parsing errors (syntax errors, undeclared variables, etc), those will still be reported, even if processing ends at the completion of this phase due to PARSEONLY being set to ON. For example:

-- Parse error:
GO
SET NOCOUNT, PARSEONLY OFF;
GO

SELECT 1;

SET PARSEONLY ON;

SELECT @UndeclaredVariable;

GO

SELECT 1/0; -- execution/runtime error
SELECT NoSuchColumn FROM sys.objects; -- compilation error

GO
SET PARSEONLY OFF;
GO
/*
Msg 137, Level 15, State 2, Line XXXXX
Must declare the scalar variable "@UndeclaredVariable".
*/

There are no runtime errors as the code is not being executed. There aren’t even any compilation errors as the code isn’t being compiled either. But, parsing errors cannot be avoided as parsing cannot be disabled.

Or, can it?

But first, let’s confirm that batch separators are handled by client tools and not SQL Server by placing the typical one in dynamic SQL:

-- SQL Server error:
EXEC (N'
SELECT 1;
GO
SELECT 2;
');
/*
Msg 102, Level 15, State 1, Line XXXXX
Incorrect syntax near 'GO'.
*/

The Main Event

Now we finally get to play with batch repetition. If you haven’t tried this before, adding a positive integer after the batch separator will send the preceding batch of statements to SQL Server that many times:

PRINT 'Candyman.';
GO 5

returns the following¹ in the «Messages» tab:

Beginning execution loop
Candyman.
Candyman.
Candyman.
Candyman.
Candyman.
Batch execution completed 5 times.

Now, let’s execute the same query batch that we tried a moment ago that received a parse error due to the undeclared variable, but this time we will add a «0» to the GO :

-- No output, but SSMS error:
GO
SET NOCOUNT, PARSEONLY OFF;
GO

SELECT 1;

SET PARSEONLY ON;

SELECT @UndeclaredVariable;

GO 0
SET PARSEONLY OFF;
GO
/*
(no "Results" tab; "Messages" tab is empty)
("Query completed with errors." in bottom status bar of SSMS)
*/

This time there’s no parse error because the batch of statements was never sent to SQL Server for processing. The client skipped that set of statements and moved on to the next batch of statements. To further illustrate this point, the following example shows text that should definitely return errors, even if PARSEONLY is enabled, yet does not error:

-- No output, but SSMS error (no indication of error in SQLCMD):
GO
SET NOCOUNT, PARSEONLY OFF;
GO

This should definitely produce an error, right?

1) "PARSEONLY" has not been set to ON.
2) More importantly, this is not even remotely close to 
   being valid T-SQL.

GO 0
/*
(no "Results" tab; "Messages" tab is empty)
("Query completed with errors." in bottom status bar of SSMS)
*/

And, if you either execute those statements manually (i.e. interactive mode) in sqlcmd or copy them to a SQL script and use that as an input file to sqlcmd, not only will any batch followed by GO 0 be skipped, but there won’t be any indication of an error.

"C:Program FilesMicrosoft SQL ServerClient SDKODBC170ToolsBinnSQLCMD.EXE" -E -i VoidBatchInSQLCMD-InvalidTSQL.sql

Another way of proving that parsing is not happening is to use the session setting that we saw at the beginning, PARSEONLY, which we know is processed during parsing:

-- If GO 0 prevents parsing, then PARSEONLY will also be prevented
GO
SET NOCOUNT, PARSEONLY OFF;
GO

SELECT 1; -- this will execute

GO 
SET PARSEONLY ON; -- This will NOT execute...
GO 0              -- because this line skips the batch

SELECT 2; -- this will execute because SET PARSEONLY ON did not

GO

The query above returns a result set for both SELECT statements (instead of just the first one) because the SET PARSEONLY ON statement is never sent to SQL Server.

Just to be Sure

Even though it’s highly unlikely that GO -1 would work, the simple fact that GO 0 does work proves that we really shouldn’t rely on assumptions.

GO
SELECT 3;
GO -1
/*
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.
*/

Ok. So, at least we have confirmed our suspicions. It’s always better to be certain, and to have actual proof.

Conclusion

The batch separator, typically GO, tells the client tool to send all of the preceding statements to SQL Server. Adding a positive integer after the batch separator should cause the client tool to send the batch that number of times to SQL Server (there’s no guarantee that any particular client tool will support this, but most should). The batch separator (and hence the optional repetition) is functionality provided entirely by the client. SQL Server neither sees the batch separator, nor does it handle any repetition. In fact, attempting to use a batch separator in dynamic SQL will result in an error.

As we have seen, using GO 0 will completely skip the preceding batch (it won’t even be parsed because it isn’t sent to SQL Server), and there won’t even be a runtime error (so it can’t be trapped or detected). This behavior has nothing to do with SQL Server itself (only clients), and does not appear to be intentional. Most likely this is just due to an oversight that was never tested because who would do this naturally (outside of someone like me trying to see what they can get away with)?

While this “technique” can certainly be used to make enemies at work, it’s also possible to use GO 0 in place of block comments (i.e. /* ... */ ). Since the text preceding GO 0, up to the prior GO or the beginning of the script (or selection if you are executing highlighted code), is skipped, you can have large comments that don’t even get sent to the server, unlike block comments. HOWEVER, this is not recommended due to a) differing behavior between clients, and b) the possibility that this behavior might get fixed at some point (though maybe not likely given that it’s been around for 16 years and more impactful bugs aren’t getting fixed).

To `GO` or Not To `GO`, That is a Configuration

To be fair, GO is the batch separator by default and thus by convention. It is possible to configure most client tools to use a different batch separator — in SSMS go to the «Tools» -> «Options» -> «Query Execution» -> «Batch separator», and for sqlcmd use the -c switch — but this option is probably seldomly used (I’ve never used it, nor seen anyone else use it, in my 20 years of working with SQL Server; and now it doesn’t seem to work in the most recent version of sqlcmd, reported here: SQLCMD 2019 not accepting different batch separator).

So, this “technique” is really about the behavior of the batch separator (and only in some client tools) and not specifically about GO. Meaning, if you were to configure your SSMS and/or sqlcmd to use w00t as the batch separator, then w00t 0 would cause the batch to never execute.

Behavior by Client

I have tested with the following clients:

SQL Server Management Studio (SSMS): 18.9.2
Azure Data Studio (ADS): 1.31.1
sqlcmd utility: 11.0.2100.60 NT x64 and 15.0.4083.2 NT
osql utility: 14.0 NT and 15.0 NT (came with SQL Server 2017 and 2019, respectively)
Visual Studio: 2019 (16.10.4)
Visual Studio Code: 1.58.2
Older stuff
- Query Analyzer: 8.00.194 (came with SQL Server 2000)
- isql utility: 8.00.194 (came with SQL Server 2000)
- Microsoft SQL Server Management Studio Express: 9.00.4035.00 (came with SQL Server 2005)
- Visual Studio: 2015

Across all of those clients, there are only three distinct behaviors (not including “not supported”), and two of those are quite similar. The following list shows which behaviors can be found in each of those clients:

Batched is skipped
- No indication of any error at all
  - sqlcmd utility
- Generic error message is shown² but execution is not stopped
  - SQL Server Management Studio (SSMS)
  - Visual Studio
Batch is not skipped (executed once; same as GO or GO 1 )
- isql utility
- osql utility
- Azure Data Studio
- Visual Studio Code
N/A (batch iteration is not supported)
- Query Analyzer

Post Update History

2021-08-13 @ 19:50 ET / 2021-08-13 @ 23:50 UTC — Initial posting

Источник

Posted by on February 23, 2012

A couple of days back, somebody asked if trace flag 1118 affect only the TempDB. My initial reaction was yes its only for TempDB. But then after further research, I figured out that it is not only for TempDB, but affects all user databases. The reason why we do not observe this change or why we don’t talk much about this is the fact that we do not create and drop tables on the user database at the same frequency as we do it for TempDB.

Since the schema of a user database does not change much often ( if it does in your environment, then you need to re-evaluate your design) the changes introduced by the TF does not pose any problems with DB growth or concurrency on the GAM and SGAM pages.

Changes Introduced by the TF 1118

Before we get into the details of the changes introduced, lets first talk about the default behavior in SQL. As an example we will try to create tables in both TempDB and a user DB (AdventureWorks).

When you create a table in SQL Server using the CREATE TABLE command, SQL will just create the meta data for the table, there is no allocation as of now. As seen below

use tempdb
go

create table TempTable (a int, b char(8000))
go

sp_spaceused ‘TempTable’
go

As the output for sp_spaceused indicates, there is no allocation done for the table as off now. The page allocation happens when we insert the FIRST record in the table. SQL Server will allocate 1 IAM page and 1 Database page (assuming the table has no indexes and also that we just need 1 page for the current set of records). SQL Server will allocate this one IAM page and 1 Data page from a mixed extent.

Insert into TempTable values (10, ‘AAA’)
go

sp_spaceused ‘TempTable’
go

As we can see SQL Server has allocation one Data Page and 1 Index Page (IAM) page. Under the default behavior SQL will try to Allocate the first 8 Data pages or index pages using Mixed Allocation. For any further pages, SQL will allocate a uniform extent.

In the example above we have made sure that 1 record occupies 1 DB page. If we add 8 records, as per the behavior SQL will only allocate 8 pages (all coming from Mixed Extents) and when we insert the 9th record, we would see a uniform extent being allocated.

Insert into TempTable values (10, ‘AAA’)
go 7;

Notice the go syntax used above, this is a special case where the Insert command is executed 7 times. Now lets check the output for sp_spaceused.

Notice that we have 8 DB pages and 1 IAM page. Now lets try to insert another record in the table.

Notice the increase in the unused space and the reserved space in the Table. We have 64KB allocated at the same time. Indicating the usage of a Uniform Extent this time.

That was the default behavior, what does TF 1118 change?

When TF 1118 is enabled, SQL will not perform any allocations from a Mixed Extent. So when you create a table, SQL will directly allocate a new uniform extent for the table. As shown below..

DBCC TRACEON (1118,-1)
GO

create table TempTable2 (a int, b char(8000))
go

Insert into TempTable2 values (10, ‘AAA’)
go
sp_spaceused ‘TempTable2’
go

The same will happen if we create a Table in any of the user Database.

use AdventureWorks
go
create table TempTable2 (a int, b char(8000))
go
Insert into TempTable2 values (10, ‘AAA’)
go
sp_spaceused ‘TempTable2’
go

The above output indicates that all allocation are not Uniform Extent Allocations.

Note: Once again since the rate of creation of Tables in a USER DB is almost zero, this TF will not see any impact of the TF on user DB’s.

P.S. Thank you Parikshit for helping out with the scripts.

Источник

The Thing That Does Nothing

The Official Word

Syntax

Where Applicable

Tests

Review

The Main Event

Just to be Sure

Conclusion

To GO or Not To GO, That is a Configuration

Behavior by Client

To `GO` or Not To `GO`, That is a Configuration