question

VimalSivaraman-5180 avatar image
0 Votes"
VimalSivaraman-5180 asked KranthiPakala-MSFT commented

Parse error in Synapse COPY command

Hi,

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', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Parse error at line: 2, column: 91: Incorrect syntax near 'XXXXXXXXXXXTbXXuXXXXXXXXXXXXr1XXXXXXXXXXXXP'. (103010) (SQLExecDirectW)")

Procedure Body


GO
CREATE PROC [SCHE].[PROC_COPY_DATA] @KeyVal [VARCHAR](250) AS
BEGIN
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);'
EXEC (@sql)

END;

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!.

Thank You.



azure-synapse-analytics
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

HimanshuSinha-MSFT avatar image
2 Votes"
HimanshuSinha-MSFT answered HimanshuSinha-MSFT commented

Hello @SQL-6436,
Thanks for the ask and using Microsoft Q&A platform .

I have replied to something similar here . https://stackoverflow.com/questions/65833908/syntax-error-when-using-input-parameter-to-create-procedure/65895040#65895040

Adding the comment here for completedness .

This should work . I have created the Copy command dynamically and passing the SAS token to that . Once I have the copy command created I execute the command . The print statement , i used it as a test

CREATE PROCEDURE dbo.bulk_load
anonymous user_token VARCHAR(500)
AS
BEGIN
--DECLARE anonymous user varchar(500)
--SET anonymous user = ''''+ anonymous user_token + ''''
declare @query varchar(max)
IF OBJECT_ID('dbo.ITEM_INFORMATION') IS NOT NULL
DROP TABLE dbo.ITEM_INFORMATION

;

 CREATE TABLE dbo.ITEM_INFORMATION 
 (
     SSN VARCHAR(20),
     Name VARCHAR(15)
 );

 SELECT @query  ='
 COPY INTO dbo.ITEM_INFORMATION
 FROM ''https://server.blob.core.windows.net/container/subfolder/file.csv''
      WITH (FILE_TYPE = ''CSV'',
            FIELDTERMINATOR = ''',''',
            FIRSTROW = 2,
            ROWTERMINATOR = ''\n'',
            CREDENTIAL = (
                          IDENTITY = ''Shared Access Signature'',
                          SECRET =  '''+anonymous user_token+'''
                          )
         );'
         print @query
         exec(@query)

END

Please do let me know how it goes .
Thanks
Himanshu


  • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

  • Want a reminder to come back and check responses? Here is how to subscribe to a notification

  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators







· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Himanshu,

It helped.

Even with SET command instead of SELECT also works fine. SELECT does works too.

And for FIELDTERMINATOR I have to use like this '',''. When given like this ''',''' it throws error at compiling saying, -

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations. When used with SELECT operation.

And, when used with SET, the error shows as - Parse error at line: 6, column: 110: Incorrect syntax near ','.

Another incorrect quotation in my code was at - SECRET= '+ @KeyVal + '. When changed to, SECRET= '''+ @KeyVal + ''' it also works fine.

So my final PROC code is like this:-

GO
CREATE PROC [SCHE].[PROC_COPY_DATA] @KeyVal [VARCHAR](250) AS
BEGIN
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);'
EXEC (@sql)

END;


And from python I'm calling the procedure as, mycur.execute("EXEC SCHE.PROC_COPY_DATA N'"+account_key+"'").

Thanks a lot for the help provided.

Vimal PS

0 Votes 0 ·

Thanks Vimal .
I did execute the piece of code shared by me in SSMS and it just ran fine . So, it looks like the data studio behave differently . I am happy that my answer was helpful .
Thanks
Himanshu

1 Vote 1 ·