I'm trying to bulk load csv data to Synapse table using COPY. For that I'm using a procedure call and trying to pass the KEY as a variable from python. While doing so, I'm facing parse error as given below.
ProgrammingError: ('42000', " [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Parse error at line: 2, column: 91: Incorrect syntax near 'XXXXXXXXXXXTbXXuXXXXXXXXXXXXr1XXXXXXXXXXXXP'. (103010) (SQLExecDirectW)")
CREATE PROC [SCHE].[PROC_COPY_DATA] @KeyVal [VARCHAR](250) AS
DECLARE @sql VARCHAR(500);
SET @sql = 'COPY INTO SCHE.TABLETEST FROM ''https://tempaccount.blob.core.windows.net/tempaccount/uploadfile.csv''
WITH (FILE_TYPE = ''CSV'', CREDENTIAL=(IDENTITY= ''Storage Account Key'', SECRET= '+ @KeyVal + '), FIELDTERMINATOR = '','', FIRSTROW = 2);'
KeyVal = XXXXXXXXXXXTbXXuXXXXXXXXXXXXr1XXXXXXXXXXXXP+i/XXXX/XWXW2XWXWXWFGG8s2w==
While trying to run directly from Data Studio I'm getting same parse error.
Command tried from Data Studio.
exec DTAPO.PROC_COPY_DATA N'XXXXXXXXXXXTbXXuXXXXXXXXXXXXr1XXXXXXXXXXXXP+i/XXXX/XWXW2XWXWXWFGG8s2w=='
Msg 103010, Level 16, State 1, Line 1
Parse error at line: 2, column: 91: Incorrect syntax near 'XXXXXXXXXXXTbXXuXXXXXXXXXXXXr1XXXXXXXXXXXXP'.
It seems like from literal +, it is terminating the string and hence failing.
If someone knows any sort of workaround for this, Please suggest!.