Now Partitioning is set, can't create table, Msg 1921, Level 16, State 1, Invalid partition scheme specified.

• quinn.jay

  SSCertifiable

  Points: 5505

  More actions

  July 30, 2018 at 5:39 pm

  #354795

  Hello, now that the data partition for tables in my database is set, code ran end to end without error, I go to create a table, and I get this error:

  Msg 1921, Level 16, State 1, Line 129
  Invalid partition scheme 'myDateRangePS1' specified.

  'myDateRangePS1' is the correct named partition scheme

  
  CREATE TABLE [dbo].[DATABASETABLE_Sub](
  [actvt_dt] [datetime] NULL,
  [actvt_ym] [int] null,
  ...
  ) ON myDateRangePS1 (actvt_dt)
  GO
  

  I ran this to check:

  
  USE [DATABASE_BI]
  GO

  SELECT * FROM sys.partition_schemes WHERE name='psDataSplitOnFunctionID';
  

  And it returned no rows.

  Any ideas as to what may be going on here?

  Thanks

• quinn.jay

  SSCertifiable

  Points: 5505

  More actions

  July 30, 2018 at 6:24 pm

  #1999619

  quinn.jay - Monday, July 30, 2018 5:39 PM

  Hello, now that the data partition for tables in my database is set, code ran end to end without error, I go to create a table, and I get this error:

  Msg 1921, Level 16, State 1, Line 129
  Invalid partition scheme 'myDateRangePS1' specified.

  'myDateRangePS1' is the correct named partition scheme

  
  CREATE TABLE [dbo].[DATABASETABLE_Sub](
  [actvt_dt] [datetime] NULL,
  [actvt_ym] [int] null,
  ...
  ) ON myDateRangePS1 (actvt_dt)
  GO
  

  I ran this to check:

  
  USE [DATABASE_BI]
  GO

  SELECT * FROM sys.partition_schemes WHERE name='psDataSplitOnFunctionID';
  

  And it returned no rows.

  Any ideas as to what may be going on here?

  Thanks

  Here is the section of code that creates the partition function and the partition scheme

  
  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
  

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  July 31, 2018 at 10:53 am

  #1999724

  Don't I recall you saying in another thread that you forgot to run that script with the EXEC statements un-commented?

  EDIT:  come to look closer, your just posted script has no execute for the scheme...

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• quinn.jay

  SSCertifiable

  Points: 5505

  More actions

  July 31, 2018 at 10:58 am

  #1999725

  sgmunson - Tuesday, July 31, 2018 10:53 AM

  Don't I recall you saying in another thread that you forgot to run that script with the EXEC statements un-commented?

  Yes, and that is set now as it was missing, and another tweak on the partitions to use Primary to shore up, happily I'm loading now, but still in test mode, but much progress had