ssas.model_json_ImportModelSql - V

type: V ( view ), modify_date: 2021-09-01 11:52:21

RepoObject_guid: 0760D8EE-E90A-EC11-8516-A81E8446D5B0

Description

Examples

Entity Diagram

entity-ssas.model_json_importmodelsql

Columns

Table 1. Columns of ssas.model_json_ImportModelSql - V
PK Column Name Data Type NULL? ID

nvarchar(128)

NOT NULL

nvarchar(4000)

NOT NULL

int

NULL

nvarchar(4000)

NOT NULL

varchar(max)

NULL

Foreign Key Diagram

entity_1_1_fk-ssas.model_json_importmodelsql

References

Referenced Objects

Object Reference Diagram - 1 1

entity_1_1_objectref-ssas.model_json_importmodelsql

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-ssas.model_json_importmodelsql

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-ssas.model_json_importmodelsql

Column Reference Diagram

entity_1_1_colref-ssas.model_json_importmodelsql

Indexes

idx_model_json_ImportModelSql__1

Column Details

_

databasename

databasename

nvarchar(128)

NOT NULL

Referenced Columns

ImportModelSql

ImportModelSql

nvarchar(4000)

NOT NULL

isModelJson

isModelJson

int

NULL

Description

(isjson([model_utf8]))


Referenced Columns

model_filename

model_filename

nvarchar(4000)

NOT NULL

Referenced Columns

model_utf8

model_utf8

varchar(max)

NULL

Referenced Columns

sql_modules_definition

ssas.model_json_ImportModelSql - V script
/*
model.bim normally is in UTF8

Idea how to import UTF-8 data ''as it is into SQL-Server is based on +
https://stackoverflow.com/questions/54626404/convert-utf-8-encoded-varbinarymax-data-to-nvarcharmax-string[]

When importing, we use `Single_Blob`, which generates varbinary(max) +
it is converted into UTF-8, when inserting into a UTF-8 column

That's why the target column has an UTF-8 collation, for example `Latin1_General_100_CI_AS_SC_UTF8`

CAUTION: the default SSMS options doesn't include the collation in the `CREATE TABLE` scripts.

*/
CREATE View [ssas].[model_json_ImportModelSql]
As
Select
    databasename
  , model_filename
  , model_utf8
  , isModelJson

  /*
Update ssas.model_json Set model_json =
(
Select BulkColumn
From
OpenRowset ( Bulk 'D:\aaa\bbb\ccc\Model.bim', Single_Clob )
As j
)
Where databasename = 'MySsasDatabase'
GO
*/
  , ImportModelSql = Concat (
                                'Update ssas.model_json Set model_utf8 = '
                              , Char ( 13 ) + Char ( 10 )
                              , '('
                              , Char ( 13 ) + Char ( 10 )
                              , 'Select BulkColumn '
                              , Char ( 13 ) + Char ( 10 )
                              , 'From '
                              , Char ( 13 ) + Char ( 10 )
                              , 'OpenRowset ( Bulk '''
                              , model_filename
                              , ''', Single_Blob )'
                              , Char ( 13 ) + Char ( 10 )
                              , 'As j'
                              , Char ( 13 ) + Char ( 10 )
                              , ')'
                              , Char ( 13 ) + Char ( 10 )
                              , 'Where databasename = '''
                              , databasename
                              , ''''
                              , Char ( 13 ) + Char ( 10 )
                              , 'GO'
                              , Char ( 13 ) + Char ( 10 )
                            )
From
    ssas.model_json