Synapse: Issue with Tables with white spaces in synapse parameters

I have a dynamic query which works fine in SSMS meaning the columns are fetched using the below query in SSMS.

DECLARE @s NVARCHAR(MAX) = NULL
DECLARE @tablename NVARCHAR(500) = '@{pipeline().parameters.table_name}'
DECLARE @watermark NVARCHAR(1000) = '@{pipeline().parameters.watermark}'
DECLARE @period_back_from_utcnow NVARCHAR(500) = '@{pipeline().parameters.period_back_from_utcnow}'


SELECT @s = ISNULL(@s + ', ', '') + '[' + c.name + ']' + ' as [' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(c.name, ' ', '_'), '(', ''), ')', ''), '.', ''), '/', '') + ']' 
FROM sys.all_columns c 
JOIN sys.views t ON c.object_id = t.object_id 
WHERE t.name = @tablename


SELECT CONCAT('SELECT ', @s, ' FROM ', @tablename) AS Query

But when I used the Table name parameter as per the picture which has whitespaces in the table name, im getting error like : { "errorCode": "2200", "message": "Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Incorrect syntax near the keyword 'FROM'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Incorrect syntax near the keyword 'FROM'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=156,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=156,State=1,Message=Incorrect syntax near the keyword 'FROM'.,},],'", "failureType": "UserError", "target": "bpw table to adls landing", "details": [] }

After execution i could see the input json as : { "source": { "type": "SqlServerSource", "sqlReaderQuery": "SELECT FROM Currency rate table", "queryTimeout": "02:00:00", "partitionOption": "None" }, "sink": { "type": "ParquetSink", "storeSettings": { "type": "AzureBlobFSWriteSettings" }, "formatSettings": { "type": "ParquetWriteSettings" } }, "enableStaging": false, "parallelCopies": 5, "translator": { "type": "TabularTranslator", "typeConversion": true, "typeConversionSettings": { "allowDataTruncation": true, "treatBooleanAsNumber": false } } }

As we can clearly see that Select statment has not been getting any columns in it. I tried to change the parameters with different types of concatenation and encapsulations but no luck. I tried to change the tablename variable declaration as below . Eventhough the input json could be created as above mentioned but with single quotes but no columns were being fetched in the SQL in Expression builder:

1. DECLARE @tablename NVARCHAR(500) = CONCAT('''', @{pipeline().parameters.table_name}, '''')
2. Tried by hardcoding : DECLARE @tablename NVARCHAR(500) = CONCAT('''', Currency rate table, '''')

note : The connections and linked services are correct and no problem with them.

I tried to change the parameters with different types of concatenation and encapsulations but no luck. I tried to change the tablename variable declaration as below . Eventhough the input json could be created as above mentioned but with single quotes but no columns were being fetched in the SQL in Expression builder:

1. DECLARE @tablename NVARCHAR(500) = CONCAT('''', @{pipeline().parameters.table_name}, '''')
2. Tried by hardcoding : DECLARE @tablename NVARCHAR(500) = CONCAT('''', Currency rate table, '''')

Expectation : Any insights on how to handle the tables from SQL Server database which has whitespaces in it. The above mentioned script working perfectly fine when the table names doesn't have any white spaces like if it's Currency_table, it would have worked.

Any insights on how to handle the tables from SQL Server database which has whitespaces in it.

If your Object name (table name, column name, procedure name, etc.) contain spaces then you need to eclosed it in the [].

you can use below expression to add [] around the table name.

@{concat('[',pipeline().parameters.table_name,']')}

Here I tried similar expression in lookup query.

Providing parameter value as table name with space.

Getting proper result.