Post reply

Help Understanding A SQL Script

  • July 18, 2022 at 9:19 pm

    #4064994

    Hello Community,

    I am trying to implement a SQL Script from this link, and pasted below

    --=================================================================================================
    -- DESCRIPTION
    --=================================================================================================
    --  The purpose of this script is to create a set of database views in a database within your Synapse Analytics Serverless SQL Pool that will 
    --  translate numeric codes for Option Sets, Status and States into human-readable descriptions for tables exported using Synapse Link for Dataverse.
    --  See the following article for background: https://docs.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-choice-labels
    --
    -- PREREQUISITES
    -- 1. You have configured Synapse Link for Dataverse to export to a Synapse Analytics Workspace
    -- 2. You have sufficient read access to the lake database created by Synapse Link for Dataverse
    -- 3. You have created an additional database in which views with enriched entities will be created
    -- 4. You have sufficient permissions to create database objects in your newly-created database
    --
    -- IMPORTANT: please execute this script in the context of the Lake Database corresponding to your Synapse Link for Dataverse!
    USE [optionsetdb] --Specify the name of the database corresponding to your Synapse Link for Dataverse

    --=================================================================================================
    --PROVIDE INPUT PARAMETERS:
    --=================================================================================================
    DECLARE
    @EnrichedViewDatabase sysname, --Specify the name of the database in which views with enriched entities will be created
    @EnrichedViewSchema sysname, --Specify the name of the database schema in which views with enriched entities will be created
    @EnrichedColumnSuffix varchar(50), --Specify the suffix for columns enriched with human-readable descriptions. For example, the suffix of "label" will change a statecode column in the base table to a statelabel column in the enriched view.
    @LanguageCode varchar(10), --Specify the language code for localized labels. For example, English - United States is 1033 (https://docs.microsoft.com/en-us/openspecs/office_standards/ms-oe376/6c085406-a698-4e12-9d4d-c3b0ee3dbc4a)
    @BaseTableSuffix varchar(50), --If applicable, specify the suffix in the names of the base tables or views (e.g., '_partitiond'). The default is an empty string.
    @PreviewOnly bit --Indicate whether to preview the SQL Script (without creating the views) = 1 ; Create views = 0;

    SET @EnrichedViewDatabase = 'optionsetdb'
    SET @EnrichedViewSchema = 'dbo'
    SET @EnrichedColumnSuffix = 'label'
    SET @LanguageCode = 1033
    SET @BaseTableSuffix = ''
    SET @PreviewOnly = 0

    --=================================================================================================
    -- Do not edit the script below this point
    --=================================================================================================

    --Get column metadata from the Lake Database managed by Synapse Link for Dataverse
    --The column metadata will be stored as a JSON document in a scalar variable
    --This is needed as a workaround for the limitation of not allowing system objects to be used in distributed queries
    DECLARE @ColumnMetadata nvarchar(MAX), @ColumnMetadataSQL nvarchar(MAX)

    --Define the SQL statement to retrieve column metadata from the Lake Database managed by Synapse Link for Dataverse
    --Results will be stored as a JSON document in a variable
    SET @ColumnMetadataSQL = 'SET @ColumnMetadataOUT = (
    SELECT TABLE_SCHEMA, 
    TABLE_NAME, 
    COLUMN_NAME, 
    ORDINAL_POSITION, 
    DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = ''dbo''
    AND TABLE_NAME NOT IN (''OptionsetMetadata'', ''GlobalOptionsetMetadata'',''StateMetadata'',''StatusMetadata'', ''TargetMetadata'')
    AND TABLE_NAME LIKE ''%' + @BaseTableSuffix + '''
    FOR JSON AUTO)'
    DECLARE @ParmDefinition NVARCHAR(MAX);  
    SET @ParmDefinition = N'@ColumnMetadataOUT NVARCHAR(MAX) OUTPUT';  
    EXECUTE sp_executesql @ColumnMetadataSQL, @ParmDefinition, @ColumnMetadataOUT=@ColumnMetadata OUTPUT;  

    --Declare a variable to store a SQL statement for creating enriched views
    DECLARE @SQL nvarchar(MAX) = ''

    ; WITH CM AS (
    --Parse column metadata variable and construct a table based on its content
    SELECT JSON_VALUE(CM.value, '$.TABLE_SCHEMA') AS TableSchema,
     JSON_VALUE(CM.value, '$.TABLE_NAME') AS TableName,
     LEFT(JSON_VALUE(CM.value, '$.TABLE_NAME'), LEN(JSON_VALUE(CM.value, '$.TABLE_NAME'))-LEN(@BaseTableSuffix)) AS EntityName,
     JSON_VALUE(CM.value, '$.COLUMN_NAME') AS ColumnName,
     CAST(JSON_VALUE(CM.value, '$.ORDINAL_POSITION') AS INT) AS OrdinalPosition,
     JSON_VALUE(CM.value, '$.DATA_TYPE') AS DataType
    FROM OPENJSON (@ColumnMetadata) AS CM
    )

    , OSM AS (
    --Get Option Set Metadata
    SELECT DISTINCT 
    EntityName, 
    OptionSetName, 
    QUOTENAME(EntityName + '_' + OptionSetName) AS Alias
    FROM dbo.[OptionsetMetadata]
    WHERE LocalizedLabelLanguageCode = @LanguageCode
    )

    , GOSM AS (
    --Get Global Option Set Metadata
    SELECT DISTINCT 
    OptionSetName, 
    QUOTENAME('Global_' + OptionSetName) AS Alias
    FROM dbo.[GlobalOptionsetMetadata]
    WHERE LocalizedLabelLanguageCode = @LanguageCode
    )

    , StateM AS (
    --Get State Metadata
    SELECT DISTINCT 
    EntityName, 
    QUOTENAME(EntityName + '_State') AS Alias
    FROM dbo.[StateMetadata]
    WHERE LocalizedLabelLanguageCode = @LanguageCode
    )

    , StatusM AS (
    --Get Status Metadata
    SELECT DISTINCT 
    EntityName,
    QUOTENAME(EntityName + '_Status') AS Alias
    FROM dbo.[StatusMetadata]
    WHERE LocalizedLabelLanguageCode = @LanguageCode
    )

    , SQLStatement AS (
    --Enumerate all lines in the source table and replace codes with labels where applicable
    SELECT CM.EntityName,
    --Before the first column of each table, construct a CREATE OR ALTER VIEW statement
    CASE WHEN CM.OrdinalPosition = 1
    THEN 'CREATE OR ALTER VIEW ' + QUOTENAME(@EnrichedViewSchema) + '.' + CM.EntityName + '
    AS
    SELECT '
    ELSE ','
    END
    --For each column, check if it needs to be replaced with a suitable localized label
    + CASE 
    WHEN OSM.OptionSetName IS NOT NULL THEN OSM.Alias + '.[LocalizedLabel] AS ' + REPLACE(QUOTENAME(CM.ColumnName), 'code]', @EnrichedColumnSuffix + ']')
    WHEN GOSM.OptionSetName IS NOT NULL THEN GOSM.Alias + '.[LocalizedLabel] AS ' + REPLACE(QUOTENAME(CM.ColumnName), 'code]', @EnrichedColumnSuffix + ']')
    WHEN StateM.EntityName IS NOT NULL THEN StateM.Alias + '.[LocalizedLabel] AS ' + REPLACE(QUOTENAME(CM.ColumnName), 'code]', @EnrichedColumnSuffix + ']')
    WHEN StatusM.EntityName IS NOT NULL THEN StatusM.Alias + '.[LocalizedLabel] AS ' + REPLACE(QUOTENAME(CM.ColumnName), 'code]', @EnrichedColumnSuffix + ']')
    ELSE '[Base].' + QUOTENAME(CM.ColumnName)
    END AS [SQLLine],
    CM.OrdinalPosition
    FROM CM 
    LEFT JOIN OSM
    ON CM.EntityName = OSM.EntityName
    AND CM.ColumnName = OSM.OptionSetName
    AND CM.DataType LIKE '%int' --Only include columns with integer data type
    LEFT JOIN GOSM
    ON CM.ColumnName = GOSM.OptionSetName
    AND CM.DataType LIKE '%int' --Only include columns with integer data type
    LEFT JOIN StateM
    ON CM.EntityName = StateM.EntityName
    AND CM.ColumnName = 'statecode'
    AND CM.DataType LIKE '%int' --Only include columns with integer data type
    LEFT JOIN StatusM
    ON CM.EntityName = StatusM.EntityName
    AND CM.ColumnName = 'statuscode'
    AND CM.DataType LIKE '%int' --Only include columns with integer data type

    UNION ALL
    --Construct the first line of the FROM clause, referencing external tables created by Synapse Link for Dataverse
    SELECT DISTINCT
    CM.EntityName,
    'FROM ' + QUOTENAME(DB_NAME()) + '.' + QUOTENAME(CM.TableSchema) + '.' + QUOTENAME(CM.TableName) + ' AS Base' AS SQLLine,
    10000 AS OrdinalPosition
    FROM CM

    UNION ALL 
    --Construct LEFT JOIN statements for each relevant OptionSetMetadata field
    SELECT DISTINCT OSM.EntityName AS EntityName,
    'LEFT JOIN ' + QUOTENAME(DB_NAME()) + '.[dbo].[OptionSetMetadata] AS ' + OSM.Alias + ' 
    ON ' + OSM.Alias + '.EntityName = ''' + OSM.EntityName + ''' 
    AND ' + OSM.Alias + '.OptionSetName = ''' +  OSM.OptionSetName + '''
    AND [Base].' + QUOTENAME(OSM.OptionSetName) + ' = ' + OSM.Alias + '.[Option]
    AND ' + OSM.Alias + '.LocalizedLabelLanguageCode = ' + @LanguageCode + '' AS SQLLine,
    20000 AS OrdinalPosition
    FROM OSM
    JOIN CM
    ON CM.EntityName = OSM.EntityName
    AND CM.ColumnName = OSM.OptionSetName
    WHERE CM.DataType LIKE '%int' --Only capture columns with Integer Data Types

    UNION ALL 
    --Construct LEFT JOIN statements for each relevant GlobalOptionSetMetadata field
    SELECT DISTINCT CM.EntityName AS EntityName,
    'LEFT JOIN ' + QUOTENAME(DB_NAME()) + '.[dbo].[GlobalOptionSetMetadata] AS ' + Alias + ' 
    ON ' + Alias + '.OptionSetName = ''' +  OptionSetName + '''
    AND [Base].' + QUOTENAME(OptionSetName) + ' = ' + Alias + '.[Option]
    AND ' + Alias + '.LocalizedLabelLanguageCode = ' + @LanguageCode + '' AS SQLLine,
    30000 AS OrdinalPosition
    FROM GOSM
    JOIN CM
    ON CM.ColumnName = GOSM.OptionSetName
    WHERE CM.DataType LIKE '%int' --Only capture columns with Integer Data Types

    UNION ALL 
    --Construct LEFT JOIN statements for each relevant State Metadata field
    SELECT DISTINCT CM.EntityName AS EntityName,
    'LEFT JOIN ' + QUOTENAME(DB_NAME()) + '.[dbo].[StateMetadata] AS ' + StateM.Alias + ' 
    ON ' + StateM.Alias + '.EntityName = ''' + StateM.EntityName + ''' 
    AND [Base].statecode' + ' = ' + StateM.Alias + '.[State]
    AND ' + StateM.Alias + '.LocalizedLabelLanguageCode = ' + @LanguageCode + '' AS SQLLine,
    40000 AS OrdinalPosition
    FROM StateM
    JOIN CM
    ON CM.EntityName = StateM.EntityName
    AND CM.ColumnName = 'statecode'
    WHERE CM.DataType LIKE '%int' --Only capture columns with Integer Data Types

    UNION ALL
    --Construct LEFT JOIN statements for each relevant Status Metadata field
    SELECT DISTINCT CM.EntityName AS EntityName,
    'LEFT JOIN ' + QUOTENAME(DB_NAME()) + '.[dbo].[StatusMetadata] AS ' + StatusM.Alias + ' 
    ON ' + StatusM.Alias + '.EntityName = ''' + StatusM.EntityName + ''' 
    AND [Base].statuscode' + ' = ' + StatusM.Alias + '.[Status]
    AND ' + StatusM.Alias + '.LocalizedLabelLanguageCode = ' + @LanguageCode + '' AS SQLLine,
    40000 AS OrdinalPosition
    FROM StatusM
    JOIN CM
    ON CM.EntityName = StatusM.EntityName
    AND CM.ColumnName = 'statuscode'
    WHERE CM.DataType LIKE '%int' --Only capture columns with Integer Data Types

    UNION ALL
    --Add statement terminator
    SELECT DISTINCT
    EntityName,
    '; ' + CHAR(10) AS SQLLine,
    100000 AS OrdinalPosition
    FROM CM
    )

    --Construct individual statements to create views (1 view per row)
    --Since CREATE VIEW statement must be the first statement in a batch, assign each view definition to a variable 
    --and use the EXEC(@variable) command to create view as part of its own, separate batch.
    , ViewDefinitions AS (
    SELECT 'DECLARE @' + EntityName + ' NVARCHAR(MAX) = ''
    ' + REPLACE(STRING_AGG(CAST(SQLLine as varchar(MAX)), CHAR(10)) WITHIN GROUP (ORDER BY EntityName, OrdinalPosition, SQLLine), '''', '''''') + ''' ' + CHAR(10) + 'EXEC [' + @EnrichedViewDatabase + '].dbo.sp_executesql @' + EntityName + CHAR(10) AS ViewDefinition
    FROM SQLStatement
    GROUP BY EntityName
    )

    --Construct a comprehensive SQL statement to create all views
    SELECT @SQL = STRING_AGG(ViewDefinition, ';' + CHAR(10) + CHAR(10))
    FROM ViewDefinitions

    --Return a preview of the SQL Script to be generated or go ahead and create the views.
    IF @PreviewOnly = 1
    BEGIN
    --Return the final SQL statement
    SELECT '--================================================================================================='+ CHAR(10) +' ' + CHAR(10) AS [--SQL Statement]
    UNION ALL 
    SELECT '-- A preview of the script to generate enriched views is provided below.'  AS [--SQL Statement]
    UNION ALL 
    SELECT '-- No database objects have been created.' AS [--SQL Statement]
    UNION ALL 
    SELECT '-- Re-run this script with the @PreviewOnly parameter set to 0 to actually create the views.' AS [--SQL Statement]
    UNION ALL 
    SELECT '--================================================================================================='+ CHAR(10) +' ' + CHAR(10) AS [--SQL Statement]
    UNION ALL 
    SELECT VALUE AS [--SQL Statement] FROM STRING_SPLIT(@SQL, CHAR(10))
    END
    ELSE
    BEGIN
    --Execute the SQL statement
    PRINT 'Beginning view creation'
    EXEC sp_executesql @SQL
    PRINT 'Completed view creation'
    END

    You will notice that the script is intended for Synapse SQL Server Pool on Dataverse, but this script should work on any SQL Server.

    The expected results is:

    When @PreviewOnly = 0, new views will be created in your database. Any existing views with matching names will be updated.

    However, when I enter @PreviewOnly = 0 no views aren't created, and I get the error:

    Cannot perform alter on 'dbo.activityparty' because it is an incompatible object type.

    Before, I go ahead and bombard you with sample data, is this a common error that you recognize? And is it a easy fix?

    The full error looks like the following:

    Any thoughts?

  • July 18, 2022 at 9:39 pm

    #4065009

    Looks to me like you did not create a new database (prerequisite #3) and update the script to reference that new database.  The database where the script is trying to ALTER the VIEW - that object already exists and is not a VIEW.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • July 18, 2022 at 9:43 pm

    #4065011

    Interesting - I was hoping I could achieve the result with the existing database. I will try with a new database.

    Thanks

  • July 18, 2022 at 9:53 pm

    #4065016

    Hi Jeffrey,

    I am now getting the following error:

     

    'testdb.dbo.sp_executesql' is not supported in this version of SQL Server.

    Would you happen to know version of SQL Server that supports sp_executesql?

  • July 18, 2022 at 10:04 pm

    #4065020

    'EXEC [' + @EnrichedViewDatabase + '].dbo.sp_executesql @'

     

    it should be sys, not dbo

  • July 18, 2022 at 10:14 pm

    #4065023

    Hi Frederico,

    Thanks for getting in touch.

    Yes, I have read the manual.

    The manual states to hardcode the database name

  • July 18, 2022 at 10:17 pm

    #4065024

    Hi Frederico

    Are you suggesting that I replace:

    @EnrichedViewDatabase

    With

    'EXEC [' + @EnrichedViewDatabase + '].dbo.sp_executesql @'

     

  • July 18, 2022 at 10:33 pm

    #4065028

    Hi Frederico,

    Are you able to clarify please?

  • July 18, 2022 at 10:36 pm

    #4065030

    carlton 84646 wrote:

    Hi Frederico,

    Are you able to clarify please?

    'EXEC [' + @EnrichedViewDatabase + '].dbo.sp_executesql @'

     

    it should be sys, not dbo

  • July 18, 2022 at 10:42 pm

    #4065032

    Ahhh ... ok

    I changed as follows: 'EXEC [' + @EnrichedViewDatabase + '].sys.sp_executesql @'

    But I'm now getting the error:

    Reference to database and/or server name in 'testdb.sys.sp_executesql' is not supported in this version of SQL Server

  • July 18, 2022 at 10:47 pm

    #4065033

    Any other thoughts?

  • July 18, 2022 at 11:29 pm

    #4065046

    Hi Frederico

    I have just been informed that you're suggestion of

    changing dbo to sys won't work

    'EXEC [' + @EnrichedViewDatabase + '].sys.sp_executesql @'

    What was actually said was:

     

     You can't reach across database boundaries  since everything is contained within the context of a single database.

     

     

  • July 18, 2022 at 11:41 pm

    #4065051

    @Jeffrey,

    It was also suggested that I shouldn't be using two different databases

  • July 19, 2022 at 6:38 am

    #4065132

    if you are using SQL Azure or a contained database then you can't reference other db's - you need to know what you can and can't do with your particular setup (which we know nothing about) - or you need to tell us exactly what that setup is.

    assuming one of the above then you need to look at the script and see the potential it has to change the schema where the objects are created so you bypass the original error you got (and you will still need to change the above exec in any case - likely even completely remove the concatenation of the db + sys)

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy