How can we help you today? How can we help you today?

BUG : Script Table / Partition scheme syntax error and missing in index definition

Hi Redgate Team,

Hovering over a partitioned table, SQL Prompt does not add the partition column to the partition scheme which causes a syntax error. Also, no aligned index includes the partition scheme which is wrong as well. 
Would be great to see a fix in the next release - thanks.

Example /result of a partitioned table and aligned index

CREATE TABLE dbo.Transactions
(
col1 int NOT NULL
  , col2 int NOT NULL
  , DateTime datetime2(2) NOT NULL
) ON ps_DateTime;  --- missing the partition column


CREATE NONCLUSTERED INDEX NCL_Transactions_Ccol2
ON dbo.Transactions (
col2
  , DateTime
);   --- missing the partition scheme and column
GO

Torsten 
MVP
torsten.strauss
0

Comments

4 comments

Sort by
  • Kurt_M
    Hi @torsten.strauss,

    May I ask which version of SQL Prompt you are currently using? 

    I've done a bit of testing myself on 10.8.4.24201 and I'm not able to replicate exactly what you're encountering.

    Using the code in the following guide to create a table with a partition, I can see that SQL Prompt is showing everything related to the partition. 
    (https://www.mssqltips.com/sqlservertip/2888/how-to-partition-an-existing-sql-server-table/)

    Below is the example:


    You can see it has everything related to the partition included in the hover over create script.

    Running this script works, so long as the partition scheme has been created/exists first.
    Kurt_M
    0
  • torsten.strauss
    Hi Kurt!

    You are right! I just figured out that this is a problem with an Azure SQL Database only.
    Can you please create the table on a SQL Database and try again - thanks!

    Torsten
    torsten.strauss
    0
  • Kurt_M
    Hi @torsten.strauss

    Thanks for the clarification.

    I've tested this on an Azure SQL Database and have managed to replicate your initial post.

    SQL Server Database


    Azure SQL Database


    I'll go ahead and get this logged with the development team.

    I'll let you know as soon as we have any updates.
    Kurt_M
    0
  • Kurt_M
    Hi @torsten.strauss

    Your issue should now be fixed in the most recent release of SQL Prompt:

    You can update from the following link:
    https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.10.2.25539.exe
    Kurt_M
    0

Add comment

Please sign in to leave a comment.