T-Script Error on resetting Counter

  • quinn.jay

    SSCertifiable

    Points: 5505

    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".

  • laurie-789651

    SSCertifiable

    Points: 7620

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

  • Jonathan AC Roberts

    SSCoach

    Points: 16685

    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

    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: 16685

    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

    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: 16685

    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

    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: 16685

    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

    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

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

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