Only SQL Server Enterprise Edition supports compression - how to work around that?

Question

I have a script that needs to create a temp table and in that temp table I create an index.

I noticed in some of my servers, the script would go on forever, and when checking from a different session what is currently running there was no clear message available.

after cancelling the query I get this message:

Message: 
Cannot enable compression for object '#RADHE_sp_getsubscriptions__________________________________________________________________________________________0000000A923A'. 
Only SQL Server Enterprise Edition supports compression.  
Error: 7738 
Severity: 16 

this is the part of the script that is generating this error message:

IF object_id('TEMPDB..#RADHE_sp_getsubscriptions') IS NOT NULL
    DROP TABLE #RADHE_sp_getsubscriptions

    create table #RADHE_sp_getsubscriptions (
        publisher sysname NOT NULL,  
        publisher_db sysname NOT NULL, 
        publication sysname NOT NULL, 
        replication_type int,
        subscription_type int,
        last_updated datetime,
        subscriberd_db sysname,
        update_mode int,
        last_sync_status int,
        last_sync_summary nvarchar(4000),
        last_sync_time datetime)

   CREATE CLUSTERED INDEX I_RADHE_sp_getsubscriptions
                       ON #RADHE_sp_getsubscriptions (publisher,Publisher_db,publication)
                     WITH (   PAD_INDEX = OFF
                            , FILLFACTOR = 100  
                            , SORT_IN_TEMPDB = ON
                            , IGNORE_DUP_KEY = OFF
                            , STATISTICS_NORECOMPUTE = OFF
                            , ONLINE = OFF
                            , DATA_COMPRESSION=PAGE
                            , ALLOW_ROW_LOCKS = ON
                            , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

Later on this is what I store in this table:

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

TRUNCATE TABLE #RADHE_sp_getsubscriptions

insert into #RADHE_sp_getsubscriptions
exec sp_MSenumsubscriptions

select * from #RADHE_sp_getsubscriptions

which gives this valuable info:

Answer 1

After checking this link about sql server version check and this one below as well:

How do you find SQL Server version/edition without SSMS installed?

I have just got this script to check what he have in the current server:

  SELECT
    CASE 
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'     
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017' 
       --WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '15%' THEN 'SQL2019' 
       ELSE 'Unknown on April 2019'
    END AS MajorVersion,
      SERVERPROPERTY('productversion') as 'Product Version', 
      SERVERPROPERTY('productlevel') as 'Product Level',  
      SERVERPROPERTY('edition') as 'Product Edition',
      SERVERPROPERTY('buildclrversion') as 'CLR Version',
      SERVERPROPERTY('collation') as 'Default Collation',
      SERVERPROPERTY('instancename') as 'Instance',
      SERVERPROPERTY('lcid') as 'LCID',
      SERVERPROPERTY('servername') as 'Server Name',
    SERVERPROPERTY('EditionID') AS 'EditionID'

and used it in my create index script down below, after the create table:

        IF object_id('TEMPDB..#RADHE_sp_getsubscriptions') IS NOT NULL
            DROP TABLE #RADHE_sp_getsubscriptions

            create table #RADHE_sp_getsubscriptions (
                publisher sysname NOT NULL,  
                publisher_db sysname NOT NULL, 
                publication sysname NOT NULL, 
                replication_type int,
                subscription_type int,
                last_updated datetime,
                subscriberd_db sysname,
                update_mode int,
                last_sync_status int,
                last_sync_summary nvarchar(4000),
                last_sync_time datetime)

           IF ( SELECT RADHE = CASE WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('edition')) LIKE '%Enterprise%' THEN 1 ELSE 0 END ) = 1

                   CREATE CLUSTERED INDEX I_RADHE_sp_getsubscriptions
                                       ON #RADHE_sp_getsubscriptions (publisher,Publisher_db,publication)
                                     WITH (   PAD_INDEX = OFF
                                            , FILLFACTOR = 100  
                                            , SORT_IN_TEMPDB = ON
                                            , IGNORE_DUP_KEY = OFF
                                            , STATISTICS_NORECOMPUTE = OFF
                                            , ONLINE = OFF
                                            , DATA_COMPRESSION=PAGE
                                            , ALLOW_ROW_LOCKS = ON
                                            , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

           ELSE

                   CREATE CLUSTERED INDEX I_RADHE_sp_getsubscriptions
                                       ON #RADHE_sp_getsubscriptions (publisher,Publisher_db,publication)
                                     WITH (   PAD_INDEX = OFF
                                            , FILLFACTOR = 100  
                                            , SORT_IN_TEMPDB = ON
                                            , IGNORE_DUP_KEY = OFF
                                            , STATISTICS_NORECOMPUTE = OFF
                                            , ONLINE = OFF
                                            , DATA_COMPRESSION=NONE
                                            , ALLOW_ROW_LOCKS = ON
                                            , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]


--checking the index creation script
-- EXEC SP_COUNT 'TEMPDB..#RADHE_sp_getsubscriptions'

--Results:
 -- SERVER 1 - CREATE CLUSTERED INDEX I_RADHE_sp_getsubscriptions  ON [dbo].[#RADHE_sp_getsubscriptions__________________________________________________________________________________________0000000A9250] (  [publisher] ASC  , [publisher_db] ASC  , [publication] ASC  )  WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, DROP_EXISTING = ON, DATA_COMPRESSION=NONE, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]  GO 
 -- SERVER 2 - CREATE CLUSTERED INDEX I_RADHE_sp_getsubscriptions  ON [dbo].[#RADHE_sp_getsubscriptions__________________________________________________________________________________________00000001B274] (  [publisher] ASC  , [publisher_db] ASC  , [publication] ASC  )  WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, DROP_EXISTING = ON, DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]  GO 

and it is all working fine now.