T-Script Error on resetting Counter

Question

Post reply

T-Script Error on resetting Counter

quinn.jay

SSCertifiable

Points: 5505
More actions
July 30, 2018 at 9:11 am

#414453

Hello, When I run this T-SQL script that begins a dynamic way to create table partitions, I’m fine until the last step of resetting the Counter back to 0 and get that error. It seems syntactically fine and makes sense and is needed before it goes to the next block of codes, which all also have this same counter reset to 0. I’ve tried redeclaring, omitting it, mod the syntax, but nothing I do works and I always error. What can I try or rewrite with this design? Thanks
ERROR - Msg 137, Level 15, State 2, Line 27 Must declare the scalar variable "@Counter".

USE
DATABASE_BI;
GO
-- sets declares on variables
DECLARE @SQLScript VARCHAR(4096)
DECLARE @MONTHS INT = 84
DECLARE @Counter INT = 0
DECLARE @Path VARCHAR(1024) = 'G:\'
-- creates filegroups dynamically
WHILE @Counter < @MONTHS
BEGIN
       SET @SQLScript = 'ALTER DATABASE DATABASE_BI ADD FILEGROUP AA'
       + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
       + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, @Counter, '2015-01-01'))))),2)
       + 'fg'
       PRINT @SQLScript
       EXEC (@SQLScript)
       SET @Counter = @Counter + 1
END
SET @Counter = 0

ERROR - Msg 137, Level 15, State 2, Line 27 Must declare the scalar variable "@Counter".

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 1

I can't recreate the error. I created a database DATABASE_BI, ran the code & it was all fine.

Jonathan AC Roberts SSCoach Points: 18259 More actions · Answer 2

From the error you are getting it sounds like you have the text '@Counter' in your dynamic query text. But the code you have pasted in doesn't have a problem. You should check the placement of quotes in the code you are running to make sure @Code isn't appearing in @SQLScript.

quinn.jay SSCertifiable Points: 5505 More actions · Answer 3

quinn.jay - Monday, July 30, 2018 9:11 AM
Hello, When I run this T-SQL script that begins a dynamic way to create table partitions, Iâ€™m fine until the last step of resetting the Counter back to 0 and get that error. It seems syntactically fine and makes sense and is needed before it goes to the next block of codes, which all also have this same counter reset to 0. Iâ€™ve tried redeclaring, omitting it, mod the syntax, but nothing I do works and I always error. What can I try or rewrite with this design? Thanks
ERROR - Msg 137, Level 15, State 2, Line 27 Must declare the scalar variable "@Counter".

USE
DATABASE_BI;
GO
-- sets declares on variables
DECLARE @SQLScript VARCHAR(4096)
DECLARE @MONTHS INT = 84
DECLARE @Counter INT = 0
DECLARE @Path VARCHAR(1024) = 'G:\'
-- creates filegroups dynamically
WHILE @Counter < @MONTHS
BEGIN
       SET @SQLScript = 'ALTER DATABASE DATABASE_BI ADD FILEGROUP AA'
       + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
       + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, @Counter, '2015-01-01'))))),2)
       + 'fg'
       PRINT @SQLScript
       EXEC (@SQLScript)
       SET @Counter = @Counter + 1
END
SET @Counter = 0
ERROR - Msg 137, Level 15, State 2, Line 27 Must declare the scalar variable "@Counter".

I've gotten past a number of issues, now at the last step, an error, I run the whole script at once ,and it gets to the lat step of creating the partition scheme, and errors with same/similar errors, not sure what to try next:

Msg 137, Level 15, State 1, Line 91
Must declare the scalar variable "@SQLScript".
Msg 137, Level 15, State 2, Line 93
Must declare the scalar variable "@Counter".
...

The odd thing is the above error is when I isolated the block below to run, the first time when I ran the whole thing in one shot, I get an error something about a misplaced ')', I did not save the error sadly

--creates partition scheme
SET @SQLScript = 'CREATE PARTITION SCHEME myDateRangePS1 AS PARTITION myDateRangePF1 TO ('

WHILE @Counter < @MONTHS
BEGIN
IF @Counter > 0
SET @SQLScript = @SQLScript + ', '

   SET @SQLScript = @SQLScript + 'sw'
                   + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
                   --+ RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, @Counter, '2015-01-01'))))),2)
                   --+ RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, ' + @Counter + ', '2015-01-01'))))),2) -- placing the counter value inside
                   + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, + @Counter, + '2015-01-01'))))),2)
                   + 'fg'

SET @Counter = @Counter + 1
END

SET @SQLScript = @SQLScript + ')'

PRINT @SQLScript

EXEC (@SQLScript)

Jonathan AC Roberts SSCoach Points: 18259 More actions · Answer 4

quinn.jay - Monday, July 30, 2018 12:16 PM
quinn.jay - Monday, July 30, 2018 9:11 AM
Hello, When I run this T-SQL script that begins a dynamic way to create table partitions, Iâ€™m fine until the last step of resetting the Counter back to 0 and get that error. It seems syntactically fine and makes sense and is needed before it goes to the next block of codes, which all also have this same counter reset to 0. Iâ€™ve tried redeclaring, omitting it, mod the syntax, but nothing I do works and I always error. What can I try or rewrite with this design? Thanks
ERROR - Msg 137, Level 15, State 2, Line 27 Must declare the scalar variable "@Counter".

USE
DATABASE_BI;
GO
-- sets declares on variables
DECLARE @SQLScript VARCHAR(4096)
DECLARE @MONTHS INT = 84
DECLARE @Counter INT = 0
DECLARE @Path VARCHAR(1024) = 'G:\'
-- creates filegroups dynamically
WHILE @Counter < @MONTHS
BEGIN
       SET @SQLScript = 'ALTER DATABASE DATABASE_BI ADD FILEGROUP AA'
       + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
       + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, @Counter, '2015-01-01'))))),2)
       + 'fg'
       PRINT @SQLScript
       EXEC (@SQLScript)
       SET @Counter = @Counter + 1
END
SET @Counter = 0
ERROR - Msg 137, Level 15, State 2, Line 27 Must declare the scalar variable "@Counter".
I've gotten past a number of issues, now at the last step, an error, I run the whole script at once ,and it gets to the lat step of creating the partition scheme, and errors with same/similar errors, not sure what to try next:
Msg 137, Level 15, State 1, Line 91
Must declare the scalar variable "@SQLScript".
Msg 137, Level 15, State 2, Line 93
Must declare the scalar variable "@Counter".
...
The odd thing is the above error is when I isolated the block below to run, the first time when I ran the whole thing in one shot, I get an error something about a misplaced ')', I did not save the error sadly
--creates partition scheme
SET @SQLScript = 'CREATE PARTITION SCHEME myDateRangePS1 AS PARTITION myDateRangePF1 TO ('
WHILE @Counter < @MONTHS
BEGIN
   IF @Counter > 0
       SET @SQLScript = @SQLScript + ', '
   SET @SQLScript = @SQLScript + 'sw'
                   + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
                   --+ RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, @Counter, '2015-01-01'))))),2)
                   --+ RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, ' + @Counter + ', '2015-01-01'))))),2) -- placing the counter value inside
                   + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, + @Counter, + '2015-01-01'))))),2)
                   + 'fg'
   SET @Counter = @Counter + 1
END
SET @SQLScript = @SQLScript + ')'
PRINT @SQLScript
EXEC (@SQLScript)

Do you have a GO in your script after you have declared your variables but before you start using them? If so GO resets all the all the variables so there are none.
You need to have the DECLARE @... in the same block where you use of the variables. If you try to run the part of the script without including the DECLARE's the script will fail with the errors you have.

quinn.jay SSCertifiable Points: 5505 More actions · Answer 5

Jonathan AC Roberts - Monday, July 30, 2018 12:31 PM
quinn.jay - Monday, July 30, 2018 12:16 PM
quinn.jay - Monday, July 30, 2018 9:11 AM
Hello, When I run this T-SQL script that begins a dynamic way to create table partitions, Iâ€™m fine until the last step of resetting the Counter back to 0 and get that error. It seems syntactically fine and makes sense and is needed before it goes to the next block of codes, which all also have this same counter reset to 0. Iâ€™ve tried redeclaring, omitting it, mod the syntax, but nothing I do works and I always error. What can I try or rewrite with this design? Thanks
ERROR - Msg 137, Level 15, State 2, Line 27 Must declare the scalar variable "@Counter".

USE
DATABASE_BI;
GO
-- sets declares on variables
DECLARE @SQLScript VARCHAR(4096)
DECLARE @MONTHS INT = 84
DECLARE @Counter INT = 0
DECLARE @Path VARCHAR(1024) = 'G:\'
-- creates filegroups dynamically
WHILE @Counter < @MONTHS
BEGIN
       SET @SQLScript = 'ALTER DATABASE DATABASE_BI ADD FILEGROUP AA'
       + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
       + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, @Counter, '2015-01-01'))))),2)
       + 'fg'
       PRINT @SQLScript
       EXEC (@SQLScript)
       SET @Counter = @Counter + 1
END
SET @Counter = 0
ERROR - Msg 137, Level 15, State 2, Line 27 Must declare the scalar variable "@Counter".
I've gotten past a number of issues, now at the last step, an error, I run the whole script at once ,and it gets to the lat step of creating the partition scheme, and errors with same/similar errors, not sure what to try next:
Msg 137, Level 15, State 1, Line 91
Must declare the scalar variable "@SQLScript".
Msg 137, Level 15, State 2, Line 93
Must declare the scalar variable "@Counter".
...
The odd thing is the above error is when I isolated the block below to run, the first time when I ran the whole thing in one shot, I get an error something about a misplaced ')', I did not save the error sadly
--creates partition scheme
SET @SQLScript = 'CREATE PARTITION SCHEME myDateRangePS1 AS PARTITION myDateRangePF1 TO ('
WHILE @Counter < @MONTHS
BEGIN
   IF @Counter > 0
       SET @SQLScript = @SQLScript + ', '
   SET @SQLScript = @SQLScript + 'sw'
                   + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
                   --+ RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, @Counter, '2015-01-01'))))),2)
                   --+ RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, ' + @Counter + ', '2015-01-01'))))),2) -- placing the counter value inside
                   + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, + @Counter, + '2015-01-01'))))),2)
                   + 'fg'
   SET @Counter = @Counter + 1
END
SET @SQLScript = @SQLScript + ')'
PRINT @SQLScript
EXEC (@SQLScript)
Do you have a GO in your script after you have declared your variables but before you start using them? If so GO resets all the all the variables so there are none.
You need to have the DECLARE @... in the same block where you use of the variables. If you try to run the part of the script without including the DECLARE's the script will fail with the errors you have.

I do not have a GO after the variables are declared

Jonathan AC Roberts SSCoach Points: 18259 More actions · Answer 6

quinn.jay - Monday, July 30, 2018 12:33 PM
Jonathan AC Roberts - Monday, July 30, 2018 12:31 PM
quinn.jay - Monday, July 30, 2018 12:16 PM
quinn.jay - Monday, July 30, 2018 9:11 AM
Hello, When I run this T-SQL script that begins a dynamic way to create table partitions, Iâ€™m fine until the last step of resetting the Counter back to 0 and get that error. It seems syntactically fine and makes sense and is needed before it goes to the next block of codes, which all also have this same counter reset to 0. Iâ€™ve tried redeclaring, omitting it, mod the syntax, but nothing I do works and I always error. What can I try or rewrite with this design? Thanks
ERROR - Msg 137, Level 15, State 2, Line 27 Must declare the scalar variable "@Counter".

USE
DATABASE_BI;
GO
-- sets declares on variables
DECLARE @SQLScript VARCHAR(4096)
DECLARE @MONTHS INT = 84
DECLARE @Counter INT = 0
DECLARE @Path VARCHAR(1024) = 'G:\'
-- creates filegroups dynamically
WHILE @Counter < @MONTHS
BEGIN
       SET @SQLScript = 'ALTER DATABASE DATABASE_BI ADD FILEGROUP AA'
       + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
       + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, @Counter, '2015-01-01'))))),2)
       + 'fg'
       PRINT @SQLScript
       EXEC (@SQLScript)
       SET @Counter = @Counter + 1
END
SET @Counter = 0
ERROR - Msg 137, Level 15, State 2, Line 27 Must declare the scalar variable "@Counter".
I've gotten past a number of issues, now at the last step, an error, I run the whole script at once ,and it gets to the lat step of creating the partition scheme, and errors with same/similar errors, not sure what to try next:
Msg 137, Level 15, State 1, Line 91
Must declare the scalar variable "@SQLScript".
Msg 137, Level 15, State 2, Line 93
Must declare the scalar variable "@Counter".
...
The odd thing is the above error is when I isolated the block below to run, the first time when I ran the whole thing in one shot, I get an error something about a misplaced ')', I did not save the error sadly
--creates partition scheme
SET @SQLScript = 'CREATE PARTITION SCHEME myDateRangePS1 AS PARTITION myDateRangePF1 TO ('
WHILE @Counter < @MONTHS
BEGIN
   IF @Counter > 0
       SET @SQLScript = @SQLScript + ', '
   SET @SQLScript = @SQLScript + 'sw'
                   + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
                   --+ RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, @Counter, '2015-01-01'))))),2)
                   --+ RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, ' + @Counter + ', '2015-01-01'))))),2) -- placing the counter value inside
                   + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, + @Counter, + '2015-01-01'))))),2)
                   + 'fg'
   SET @Counter = @Counter + 1
END
SET @SQLScript = @SQLScript + ')'
PRINT @SQLScript
EXEC (@SQLScript)
Do you have a GO in your script after you have declared your variables but before you start using them? If so GO resets all the all the variables so there are none.
You need to have the DECLARE @... in the same block where you use of the variables. If you try to run the part of the script without including the DECLARE's the script will fail with the errors you have.
I do not have a GO after the variables are declared

"The odd thing is the above error is when I isolated the block below to run,"
The block you are running does not have the DECLAREs in.

quinn.jay SSCertifiable Points: 5505 More actions · Answer 7

quinn.jay - Monday, July 30, 2018 12:33 PM
Jonathan AC Roberts - Monday, July 30, 2018 12:31 PM
quinn.jay - Monday, July 30, 2018 12:16 PM
quinn.jay - Monday, July 30, 2018 9:11 AM
Hello, When I run this T-SQL script that begins a dynamic way to create table partitions, Iâ€™m fine until the last step of resetting the Counter back to 0 and get that error. It seems syntactically fine and makes sense and is needed before it goes to the next block of codes, which all also have this same counter reset to 0. Iâ€™ve tried redeclaring, omitting it, mod the syntax, but nothing I do works and I always error. What can I try or rewrite with this design? Thanks
ERROR - Msg 137, Level 15, State 2, Line 27 Must declare the scalar variable "@Counter".

USE
DATABASE_BI;
GO
-- sets declares on variables
DECLARE @SQLScript VARCHAR(4096)
DECLARE @MONTHS INT = 84
DECLARE @Counter INT = 0
DECLARE @Path VARCHAR(1024) = 'G:\'
-- creates filegroups dynamically
WHILE @Counter < @MONTHS
BEGIN
       SET @SQLScript = 'ALTER DATABASE DATABASE_BI ADD FILEGROUP AA'
       + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
       + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, @Counter, '2015-01-01'))))),2)
       + 'fg'
       PRINT @SQLScript
       EXEC (@SQLScript)
       SET @Counter = @Counter + 1
END
SET @Counter = 0
ERROR - Msg 137, Level 15, State 2, Line 27 Must declare the scalar variable "@Counter".
I've gotten past a number of issues, now at the last step, an error, I run the whole script at once ,and it gets to the lat step of creating the partition scheme, and errors with same/similar errors, not sure what to try next:
Msg 137, Level 15, State 1, Line 91
Must declare the scalar variable "@SQLScript".
Msg 137, Level 15, State 2, Line 93
Must declare the scalar variable "@Counter".
...
The odd thing is the above error is when I isolated the block below to run, the first time when I ran the whole thing in one shot, I get an error something about a misplaced ')', I did not save the error sadly
--creates partition scheme
SET @SQLScript = 'CREATE PARTITION SCHEME myDateRangePS1 AS PARTITION myDateRangePF1 TO ('
WHILE @Counter < @MONTHS
BEGIN
   IF @Counter > 0
       SET @SQLScript = @SQLScript + ', '
   SET @SQLScript = @SQLScript + 'sw'
                   + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
                   --+ RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, @Counter, '2015-01-01'))))),2)
                   --+ RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, ' + @Counter + ', '2015-01-01'))))),2) -- placing the counter value inside
                   + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, + @Counter, + '2015-01-01'))))),2)
                   + 'fg'
   SET @Counter = @Counter + 1
END
SET @SQLScript = @SQLScript + ')'
PRINT @SQLScript
EXEC (@SQLScript)
Do you have a GO in your script after you have declared your variables but before you start using them? If so GO resets all the all the variables so there are none.
You need to have the DECLARE @... in the same block where you use of the variables. If you try to run the part of the script without including the DECLARE's the script will fail with the errors you have.
I do not have a GO after the variables are declared

This may help at this point, here is the code in it's entirety

-- sets database to perform
USE DATABASE_BI;
GO

-- sets declares on variables
DECLARE @SQLScript VARCHAR(4096)
DECLARE @MONTHS INT = 84
DECLARE @Counter INT = 0
DECLARE @Path VARCHAR(1024) = 'G:\'

-- creates filegroups dynamically
WHILE @Counter < @MONTHS
BEGIN
    SET @SQLScript = 'ALTER DATABASE DATABASE_BI ADD FILEGROUP sw'
                    + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
                    + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, + @Counter, + '2015-01-01'))))),2)
                    + 'fg'
    PRINT @SQLScript

EXEC (@SQLScript)

SET @Counter = @Counter + 1
END

--SET @Counter = 0

-- creates file(s) to hold partitioned data
WHILE @Counter < @MONTHS
BEGIN
    SET @SQLScript = 'ALTER DATABASE DATABASE_BI ADD FILE( NAME = sw'
                    + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
                    + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, + @Counter, + '2015-01-01'))))),2)
                    + 'dat, FILENAME = ''' + @Path + 'sw'
                    + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
                    + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, + @Counter, + '2015-01-01'))))),2)
                    + 'dat.ndf'', SIZE = 10 MB, MAXSIZE = 250 MB, FILEGROWTH = 10 MB) TO FILEGROUP sw'
                    + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
                    + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, + @Counter, + '2015-01-01'))))),2)
                    + 'fg'
    PRINT @SQLScript

EXEC (@SQLScript)

SET @Counter = @Counter + 1
END

--SET @Counter = 0

-- creates the partition function
SET @SQLScript = 'CREATE PARTITION FUNCTION myDateRangePF1 (datetime) AS RANGE RIGHT FOR VALUES ('

WHILE @Counter < @MONTHS - 1
BEGIN
IF @Counter > 0
SET @SQLScript = @SQLScript + ', '

    SET @SQLScript = @SQLScript + ''''
                    + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
                    + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, + @Counter, + '2015-01-01'))))),2)
                    + '01'''

SET @Counter = @Counter + 1
END

SET @SQLScript = @SQLScript + ')'

PRINT @SQLScript

EXEC (@SQLScript)

--SET @Counter = 0

--creates partiton scheme
SET @SQLScript = 'CREATE PARTITION SCHEME myDateRangePS1 AS PARTITION myDateRangePF1 TO ('

WHILE @Counter < @MONTHS
BEGIN
IF @Counter > 0
SET @SQLScript = @SQLScript + ', '

    SET @SQLScript = @SQLScript + 'sw'
                    + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
                    + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, + @Counter, + '2015-01-01'))))),2)
                    + 'fg'

SET @Counter = @Counter + 1
END

SET @SQLScript = @SQLScript + ')'

PRINT @SQLScript

EXEC (@SQLScript)

-- Create partitioned tables to on partition actvt_ym
CREATE TABLE [dbo].[BI_Sub](
[actvt_dt] [datetime] NULL,
[actvt_ym] [int] null,
...
) ON myDateRangePS1 (actvt_dt)
GO

Jonathan AC Roberts SSCoach Points: 18259 More actions · Answer 8

quinn.jay - Monday, July 30, 2018 12:48 PM
quinn.jay - Monday, July 30, 2018 12:33 PM
Jonathan AC Roberts - Monday, July 30, 2018 12:31 PM
quinn.jay - Monday, July 30, 2018 12:16 PM
quinn.jay - Monday, July 30, 2018 9:11 AM
Hello, When I run this T-SQL script that begins a dynamic way to create table partitions, Iâ€™m fine until the last step of resetting the Counter back to 0 and get that error. It seems syntactically fine and makes sense and is needed before it goes to the next block of codes, which all also have this same counter reset to 0. Iâ€™ve tried redeclaring, omitting it, mod the syntax, but nothing I do works and I always error. What can I try or rewrite with this design? Thanks
ERROR - Msg 137, Level 15, State 2, Line 27 Must declare the scalar variable "@Counter".

USE
DATABASE_BI;
GO
-- sets declares on variables
DECLARE @SQLScript VARCHAR(4096)
DECLARE @MONTHS INT = 84
DECLARE @Counter INT = 0
DECLARE @Path VARCHAR(1024) = 'G:\'
-- creates filegroups dynamically
WHILE @Counter < @MONTHS
BEGIN
       SET @SQLScript = 'ALTER DATABASE DATABASE_BI ADD FILEGROUP AA'
       + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
       + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, @Counter, '2015-01-01'))))),2)
       + 'fg'
       PRINT @SQLScript
       EXEC (@SQLScript)
       SET @Counter = @Counter + 1
END
SET @Counter = 0
ERROR - Msg 137, Level 15, State 2, Line 27 Must declare the scalar variable "@Counter".
I've gotten past a number of issues, now at the last step, an error, I run the whole script at once ,and it gets to the lat step of creating the partition scheme, and errors with same/similar errors, not sure what to try next:
Msg 137, Level 15, State 1, Line 91
Must declare the scalar variable "@SQLScript".
Msg 137, Level 15, State 2, Line 93
Must declare the scalar variable "@Counter".
...
The odd thing is the above error is when I isolated the block below to run, the first time when I ran the whole thing in one shot, I get an error something about a misplaced ')', I did not save the error sadly
--creates partition scheme
SET @SQLScript = 'CREATE PARTITION SCHEME myDateRangePS1 AS PARTITION myDateRangePF1 TO ('
WHILE @Counter < @MONTHS
BEGIN
   IF @Counter > 0
       SET @SQLScript = @SQLScript + ', '
   SET @SQLScript = @SQLScript + 'sw'
                   + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
                   --+ RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, @Counter, '2015-01-01'))))),2)
                   --+ RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, ' + @Counter + ', '2015-01-01'))))),2) -- placing the counter value inside
                   + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, + @Counter, + '2015-01-01'))))),2)
                   + 'fg'
   SET @Counter = @Counter + 1
END
SET @SQLScript = @SQLScript + ')'
PRINT @SQLScript
EXEC (@SQLScript)
Do you have a GO in your script after you have declared your variables but before you start using them? If so GO resets all the all the variables so there are none.
You need to have the DECLARE @... in the same block where you use of the variables. If you try to run the part of the script without including the DECLARE's the script will fail with the errors you have.
I do not have a GO after the variables are declared
This may help at this point, here is the code in it's entirety

-- sets database to perform
USE DATABASE_BI;
GO
-- sets declares on variables
DECLARE @SQLScript VARCHAR(4096)
DECLARE @MONTHS INT = 84
DECLARE @Counter INT = 0
DECLARE @Path VARCHAR(1024) = 'G:\'
-- creates filegroups dynamically
WHILE @Counter < @MONTHS
BEGIN
    SET @SQLScript = 'ALTER DATABASE DATABASE_BI ADD FILEGROUP sw'
                    + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
                    + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, + @Counter, + '2015-01-01'))))),2)
                    + 'fg'
    PRINT @SQLScript
    EXEC (@SQLScript)
    SET @Counter = @Counter + 1
END
--SET @Counter = 0
-- creates file(s) to hold partitioned data
WHILE @Counter < @MONTHS
BEGIN
    SET @SQLScript = 'ALTER DATABASE DATABASE_BI ADD FILE( NAME = sw'
                    + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
                    + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, + @Counter, + '2015-01-01'))))),2)
                    + 'dat, FILENAME = ''' + @Path + 'sw'
                    + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
                    + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, + @Counter, + '2015-01-01'))))),2)
                    + 'dat.ndf'', SIZE = 10 MB, MAXSIZE = 250 MB, FILEGROWTH = 10 MB) TO FILEGROUP sw'
                    + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
                    + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, + @Counter, + '2015-01-01'))))),2)
                    + 'fg'
    PRINT @SQLScript
    EXEC (@SQLScript)
    SET @Counter = @Counter + 1
END
--SET @Counter = 0
-- creates the partition function
SET @SQLScript = 'CREATE PARTITION FUNCTION myDateRangePF1 (datetime) AS RANGE RIGHT FOR VALUES ('
WHILE @Counter < @MONTHS - 1
BEGIN
    IF @Counter > 0
        SET @SQLScript = @SQLScript + ', '
    SET @SQLScript = @SQLScript + ''''
                    + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
                    + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, + @Counter, + '2015-01-01'))))),2)
                    + '01'''
    SET @Counter = @Counter + 1
END
SET @SQLScript = @SQLScript + ')'
PRINT @SQLScript
EXEC (@SQLScript)
--SET @Counter = 0
--creates partiton scheme
SET @SQLScript = 'CREATE PARTITION SCHEME myDateRangePS1 AS PARTITION myDateRangePF1 TO ('
WHILE @Counter < @MONTHS
BEGIN
    IF @Counter > 0
        SET @SQLScript = @SQLScript + ', '
    SET @SQLScript = @SQLScript + 'sw'
                    + LTRIM(RTRIM(STR(YEAR(DATEADD(MONTH, @Counter, '2015-01-01')))))
                    + RIGHT('0'+LTRIM(RTRIM(STR(MONTH(DATEADD(MONTH, + @Counter, + '2015-01-01'))))),2)
                    + 'fg'
    SET @Counter = @Counter + 1
END
SET @SQLScript = @SQLScript + ')'
PRINT @SQLScript
EXEC (@SQLScript)
-- Create partitioned tables to on partition actvt_ym
CREATE TABLE [dbo].[BI_Sub](
[actvt_dt] [datetime] NULL,
[actvt_ym] [int] null,
...
) ON myDateRangePS1 (actvt_dt)
GO

Your script is trying to execute the line:
CREATE PARTITION SCHEME myDateRangePS1 AS PARTITION myDateRangePF1 TO ()
Which causes error:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.

quinn.jay SSCertifiable Points: 5505 More actions · Answer 9

Jonathan AC Roberts - Monday, July 30, 2018 12:59 PM

quinn.jay - Monday, July 30, 2018 12:48 PM

Your script is trying to execute the line:
CREATE PARTITION SCHEME myDateRangePS1 AS PARTITION myDateRangePF1 TO ()
Which causes error:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.

Yes that's the error, and it is supposed to exec that line, except between the parenthesis , to run all the code from the While to the last Set statement