July 30, 2018 at 2:08 pm
Hello, I've got help over a few days getting a script to run and create data partitioning for tables. I'm told that others, one on SQLServer 2017, can run this whole script in one go and its executes properly. I run it, in one shot, or step through one block at a time, I get no resutls or errors. If the whole ,thing, it comes back with the result set of 84, which is obvciusly not right. Or, I get to the first block, the loop, and its an error:
Msg 137, Level 15, State 2, Line 17
Must declare the scalar variable "@Counter".
Is there something I need to configure in my SSMS SQL editor window??
USE ABHS_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' + CHAR(10)
SET @SQLScript = @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
SELECT @Counter
SET @Counter = 0
-- creates the partition function
SET @SQLScript = 'CREATE PARTITION FUNCTION myDateRangePF1 (datetime) AS RANGE RIGHT FOR VALUES ('
WHILE @Counter < @MONTHS
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
July 30, 2018 at 2:15 pm
Runs fine for me. 84 is the value of @Counter when you SELECT it. Why is it obviously not right?
Are you checking the Messages window?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 30, 2018 at 2:27 pm
DECLARE @MONTHS INT = 84
...
SET @Counter = 0
WHILE @Counter < @MONTHS
BEGIN
...
SET @Counter = @Counter + 1
END
That's always going to end with @Counter holding a value of 84 (i.e. the point at which @Counter < @MONTHS becomes false). I'm not sure why you think that is "obviously wrong"
July 30, 2018 at 2:29 pm
Like Phil said, the script is fine on SQL Server 2016. I wonder if you are stepping though the "blocks" of the script and getting the error because your variables are not declared. If you break the script up into "blocks" such that the "blocks" are being executed in separate batches, the variables would need to be declared in each "block". One problem with that approach though is that the script would not be able to be executed as a single "block" because the variables would be declared multiple times.
Hope that makes sense 😉
July 30, 2018 at 2:34 pm
Phil Parkin - Monday, July 30, 2018 2:15 PMRuns fine for me. 84 is the value of @Counter when you SELECT it. Why is it obviously not right?Are you checking the Messages window?
I did look at the messages window, it shows that the filegroups and file were created, yet I go look at the database properties and nothing was created, it's like it's only printing to the screen (msg window) and not actually performing the actions needed
July 30, 2018 at 2:37 pm
andycadley - Monday, July 30, 2018 2:27 PMDECLARE @MONTHS INT = 84
...SET @Counter = 0
WHILE @Counter < @MONTHS
BEGIN
...
SET @Counter = @Counter + 1
END
That's always going to end with @Counter holding a value of 84 (i.e. the point at which @Counter < @MONTHS becomes false). I'm not sure why you think that is "obviously wrong"
Because it never actually created any filegroups and files, and it exected less than a nanosecond, it's printing to screen the message of a result but actually did nothing
July 30, 2018 at 2:39 pm
heb1014 - Monday, July 30, 2018 2:29 PMLike Phil said, the script is fine on SQL Server 2016. I wonder if you are stepping though the "blocks" of the script and getting the error because your variables are not declared. If you break the script up into "blocks" such that the "blocks" are being executed in separate batches, the variables would need to be declared in each "block". One problem with that approach though is that the script would not be able to be executed as a single "block" because the variables would be declared multiple times.Hope that makes sense 😉
it does make sense, and it'd be great that when it exec in one whole shot, that it would actually create the filegroups and files for me, as it did someone else on a different system
July 30, 2018 at 2:40 pm
All the EXEC statements are commented out. I presume so you can run it, check the SQL it would have executed and then either execute it manually or uncomment the EXECs and re-run the script to create the filegroups etc. It's a fairly common technique when developing Dynamic SQL based scripts.
July 30, 2018 at 2:47 pm
andycadley - Monday, July 30, 2018 2:40 PMAll the EXEC statements are commented out. I presume so you can run it, check the SQL it would have executed and then either execute it manually or uncomment the EXECs and re-run the script to create the filegroups etc. It's a fairly common technique when developing Dynamic SQL based scripts.
Looks like this was it, my lack of experience too, I removed the comments on the exec and now its running
Thank you!
July 30, 2018 at 2:49 pm
quinn.jay - Monday, July 30, 2018 2:39 PMheb1014 - Monday, July 30, 2018 2:29 PMLike Phil said, the script is fine on SQL Server 2016. I wonder if you are stepping though the "blocks" of the script and getting the error because your variables are not declared. If you break the script up into "blocks" such that the "blocks" are being executed in separate batches, the variables would need to be declared in each "block". One problem with that approach though is that the script would not be able to be executed as a single "block" because the variables would be declared multiple times.Hope that makes sense 😉
it does make sense, and it'd be great that when it exec in one whole shot, that it would actually create the filegroups and files for me, as it did someone else on a different system
if we take a step back...I'm wondering why would you want to create all of these files and filegroups. It seems very excessive. Can you give us your thoughts on that? Also, have you considered how you will deal with these files in a recovery scenario?
July 30, 2018 at 2:50 pm
quinn.jay - Monday, July 30, 2018 2:47 PMLooks like this was it, my lack of experience too, I removed the comments on the exec and now its runningThank you!
No worries, it's an easy thing to overlook.
July 30, 2018 at 2:51 pm
andycadley - Monday, July 30, 2018 2:40 PMAll the EXEC statements are commented out. I presume so you can run it, check the SQL it would have executed and then either execute it manually or uncomment the EXECs and re-run the script to create the filegroups etc. It's a fairly common technique when developing Dynamic SQL based scripts.
Glad this works, but I hope you've thought this through from an administrative perspective.
July 30, 2018 at 2:58 pm
heb1014 - Monday, July 30, 2018 2:49 PMquinn.jay - Monday, July 30, 2018 2:39 PMheb1014 - Monday, July 30, 2018 2:29 PMLike Phil said, the script is fine on SQL Server 2016. I wonder if you are stepping though the "blocks" of the script and getting the error because your variables are not declared. If you break the script up into "blocks" such that the "blocks" are being executed in separate batches, the variables would need to be declared in each "block". One problem with that approach though is that the script would not be able to be executed as a single "block" because the variables would be declared multiple times.Hope that makes sense 😉
it does make sense, and it'd be great that when it exec in one whole shot, that it would actually create the filegroups and files for me, as it did someone else on a different system
if we take a step back...I'm wondering why would you want to create all of these files and filegroups. It seems very excessive. Can you give us your thoughts on that? Also, have you considered how you will deal with these files in a recovery scenario?
I'm needing to get a month of data into its own data partition, as for a period of time it's going to be deleted and loaded many times, before rolling out of the picture, it's to make for faster deletes and faster inserts for the months I'm working with, usually current and previous, and I will hold 3 previous years, with current year, so occasional complete reloads of a year or more will also occur.
July 30, 2018 at 3:09 pm
heb1014 - Monday, July 30, 2018 2:51 PMandycadley - Monday, July 30, 2018 2:40 PMAll the EXEC statements are commented out. I presume so you can run it, check the SQL it would have executed and then either execute it manually or uncomment the EXECs and re-run the script to create the filegroups etc. It's a fairly common technique when developing Dynamic SQL based scripts.Glad this works, but I hope you've thought this through from an administrative perspective.
What do you mean, what should I be watching out for?
July 31, 2018 at 5:36 am
quinn.jay - Monday, July 30, 2018 2:58 PMheb1014 - Monday, July 30, 2018 2:49 PMquinn.jay - Monday, July 30, 2018 2:39 PMheb1014 - Monday, July 30, 2018 2:29 PMLike Phil said, the script is fine on SQL Server 2016. I wonder if you are stepping though the "blocks" of the script and getting the error because your variables are not declared. If you break the script up into "blocks" such that the "blocks" are being executed in separate batches, the variables would need to be declared in each "block". One problem with that approach though is that the script would not be able to be executed as a single "block" because the variables would be declared multiple times.Hope that makes sense 😉
it does make sense, and it'd be great that when it exec in one whole shot, that it would actually create the filegroups and files for me, as it did someone else on a different system
if we take a step back...I'm wondering why would you want to create all of these files and filegroups. It seems very excessive. Can you give us your thoughts on that? Also, have you considered how you will deal with these files in a recovery scenario?
I'm needing to get a month of data into its own data partition, as for a period of time it's going to be deleted and loaded many times, before rolling out of the picture, it's to make for faster deletes and faster inserts for the months I'm working with, usually current and previous, and I will hold 3 previous years, with current year, so occasional complete reloads of a year or more will also occur.
Why do you need all the files and filegroups for that?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply