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:
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', FIRSTROW = 2, 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()
Advertisement
Answer
This shoould 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 . HTH
CREATE PROCEDURE dbo.bulk_load @sas_token VARCHAR(500) AS BEGIN --DECLARE @sas varchar(500) --SET @sas = ''''+ @sas_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 = '''+@sas_token+''' ) );' print @query exec(@query) END