locked
Now Partitioning is set, can't create table, Msg 1921, Level 16, State 1, Invalid partition scheme RRS feed

  • Question

  • 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
    Monday, July 30, 2018 11:41 PM

Answers

  • Hi JP_Quinn,

    Based on the error message, SQL Server cannot find the schema 'myDateRangePS1', can you find this schema from sys.partition_schemes? 

    Besides, after checking the following code, it looks like that you did not execute the script generated to create schema.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 31, 2018 2:37 AM

All replies

  • Here is the section of code where the partition function and partition scheme are being created

    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

    Tuesday, July 31, 2018 12:23 AM
  • Hi JP_Quinn,

    Based on the error message, SQL Server cannot find the schema 'myDateRangePS1', can you find this schema from sys.partition_schemes? 

    Besides, after checking the following code, it looks like that you did not execute the script generated to create schema.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 31, 2018 2:37 AM