SQLScript Replacement \\n

[georgedutton]

When trying to use config-prod.csv to replace the script in a sqlscript as part of a synapse deployment, I found that the library saves the file with escaped values, e.g. \n instead of \n etc. After debugging I found that it is due to the $output = ($obj.Body | ConvertTo-Json -Compress:$true -Depth 100) line in Save-SynapseObectAsFile.ps1.

Test script:

$output = "{type=SqlScript; name=Populate serverless; path=content.query; value=IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseDeltaFor
mat') \n\t"

$output | ConvertTo-Json -Depth 100

Output:

{type=SqlScript; name=Populate serverless; path=content.query; value=IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = \u0027SynapseDeltaFor\r\nmat\u0027) \\n\\t

[NowinskiK]

Everything looks correct - check below code:

$output = '{
    "type": "SqlScript", 
    "name": "Populate serverless", 
    "path": "content.query",
    "query": "SELECT * FROM sys.external_file_formats WHERE 1=1; \n\t"
}'

$output | ConvertTo-Json -Depth 100

Output:

{\r\n\t\"name\": \"10-Ext-Table\",\r\n\t\"properties\": {\r\n\t\t\"content\": {\r\n\t\t\t\"query\": \"SELECT * FROM sys.external_file_formats WHERE 1=1; \\n\\t\"\r\n\t\t},\r\n\t\t\"type\": \"SqlQuery\"\r\n\t}\r\n}

Note that new lines in value-part of json element has \\t\\n, but new line in JSON object itself is encoded to \n. Both are correct.

[georgedutton]

Thanks for the reply @NowinskiK Sorry for not saying this in the initial bug but we've found that if you deploy that output to Synapse (or view the ~sqlscript file) you find the values \n, \t and \u0027 are also present in the workspace.

The behaviour using config-prod.csv and without it, are different.

[NowinskiK]

George, can you show me the example of SQLScript (please scrub any client-related pieces). I'm wondering where the problem lies, as I can't do too much with ConvertTo-Json as it's standard cmd.

[georgedutton]

Yes of course, config-prod.csv contains this value (truncate for simplicity and obfuscation)

SqlScript,Populate serverless - Datawarehouse database,content.query,"IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseDeltaFormat') \n\tCREATE EXTERNAL FILE FORMAT [SynapseDeltaFormat] \n\tWITH ( FORMAT_TYPE = DELTA)\nGO\n\n"

~Populate serverless - Datawarehouse database.json file: {"name":"Populate serverless - Datawarehouse database","properties":{"content":{"query":"IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = \u0027SynapseDeltaFormat\u0027) \\n\\tCREATE EXTERNAL FILE FORMAT [SynapseDeltaFormat] \\n\\tWITH ( FORMAT_TYPE = DELTA)\\nGO\\n\\n","metadata":{"language":"sql"},"currentConnection":{"databaseName":"DataWarehouse","poolName":"Built-in"},"resultLimit":5000},"type":"SqlQuery"}}

deployment result:

Deployment results (without using config-prod.csv)

[NowinskiK]

Could you show me also what the original file (Populate serverless - Datawarehouse database.json) looks like?

[georgedutton]

Sure, I've removed alot of the script (which contains environment specific storage names which is the reason for the replacement) but the same should apply no matter the length.

{
    "name": "Populate serverless - Datawarehouse database",
    "properties": {
        "content": {
            "query": "IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseDeltaFormat') \n\tCREATE EXTERNAL FILE FORMAT [SynapseDeltaFormat] \n\tWITH ( FORMAT_TYPE = DELTA)\nGO\n\n",
            "metadata": {
                "language": "sql"
            },
            "currentConnection": {
                "databaseName": "DataWarehouse",
                "poolName": "Built-in"
            },
            "resultLimit": 5000
        },
        "type": "SqlQuery"
    }
}