I'm getting this error
Msg 103010, Level 16, State 1, Line 1
Parse error at line: 33, column: 15: Incorrect syntax near '@sas_token'
For reference here are the guides I am following:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/quickstart-bulk-load-copy-tsql-examples#b-shared-access-signatures-sas-with-crlf-as-the-row-terminator-windows-style-new-line
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-stored-procedures#stored-procedures-in-synapse-sql
I tried the commented part because I thought it didn't recognize the quotes but it still didn't work
CREATE PROCEDURE dbo.bulk_load
@sas_token VARCHAR(500)
AS
BEGIN
--DECLARE @sas varchar(500)
--SET @sas = ''''+ @sas_token + ''''
IF OBJECT_ID('dbo.ITEM_INFORMATION') IS NOT NULL
DROP TABLE dbo.ITEM_INFORMATION;
CREATE TABLE dbo.ITEM_INFORMATION
(
model VARCHAR(20),
col1 VARCHAR(15),
col2 VARCHAR(3),
col3 VARCHAR(255)
);
COPY INTO dbo.ITEM_INFORMATION
FROM 'https://server.blob.core.windows.net/container/subfolder/file.csv'
WITH (FILE_TYPE = 'CSV',
FIELDTERMINATOR = ',',
FIRSTROW = 2,
ROWTERMINATOR = '
',
CREDENTIAL = (
IDENTITY = 'Shared Access Signature',
SECRET = @sas_token --@sas also tried '''' + @sas_token + ''''
)
);
END
This procedure will be called from Python like so:
def update_database(sas_token):
#Runs the stored procedure to load the data from Azure storage to table.
#Uses the password stored in user environment variable.
server = 'server.database.windows.net'
database = 'database'
username = 'username'
if 'server_password' not in os.environ:
print("Missing environment variable 'server_password' containing database password.")
password = os.getenv('server_password')
driver= '{ODBC Driver 17 for SQL Server}'
sql = f'EXEC dbo.bulk_load, {sas_token};'
connection = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
connection.autocommit = True
connection.execute(sql)
connection.close()
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…