ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near '@P1'. (102)

Question

Here is my code. I am getting error at 'BULK INSERT' statement. Please tell why the bulk insert is not working:

local_path="C:\\Users\\sankalp.patil\\assignment\\upload" 

block_blob_service = BlockBlobService(account_name='samplsa', account_key='+M5icqu9BNzqTMfYMsYhFEROBjdgFHMIyYytsbBRqATVllUP0XyHcsgbxGmEC4zu0QtpW7rAn2Vf4PsBMVa5eg==')
container_name = 'targetcontainer'
block_blob_service.create_container(container_name)

for files in os.listdir(local_path): block_blob_service.create_blob_from_path(container_name,files,os.path.join(local_path,files))

server = 'sample-server1.database.windows.net'    
database = 'targetdb'    
username = 'sankalp'    
password = 'mypassword'    
driver= '{ODBC Driver 13 for SQL Server}'
def sqlconnect(server,database,username,password):
    try:
        return pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
    except:
    print ("connection failed check authorization parameters")  
conn = sqlconnect(server,database,username,password)
cursor = conn.cursor()


for files in os.listdir(local_path):
    path=os.path.join(local_path,files)
    filename=(os.path.splitext(files)[0])
    tablename = 'dbo.'+filename
    print(files)
    print(path)
    print(tablename)
    sql = "BULK INSERT ? FROM ? WITH (DATA_SOURCE = 'j',FIELDTERMINATOR=',',ROWTERMINATOR='\n')", tablename , files
cursor.execute(*sql)

conn.commit()
cursor.close()
print("Done")
conn.close()




I have created a data source in azure sql db as follows :

create external data source j
    with(
        type = BLOB_STORAGE,
        location = 'https://samplsa.blob.core.windows.net/targetcontainer'
        )

So I am copying files from my system to azure blob storage.And then trying to copy files from blob to azure sql database.But I am getting the error as

Error:

[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]
Incorrect syntax near '@P1'
(102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)

Answer 1

As I mentioned in the comment, you can't parametrise object names, or part of the code that need to be a literal. For example, something like the below wouldn't work:

DECLARE @TableName sysname;
SET @TableName = N'MyTable'

SELECT *
FROM @TableName;

You'd get an error stating that the Table variable @TableName hasn't been declared.

Therefore you need to use Dynamic SQL, and safely inject the values into the string. I don't know Python, but I suspect this will work:

sql = "DECLARE @SQL nvarchar(MAX) = N'BULK INSERT ' + QUOTENAME(?) + N' FROM N' + REPLACE(?,'''','''''') + N' WITH (DATA_SOURCE = ''j'',FIELDTERMINATOR='','',ROWTERMINATOR=''\n'');'; EXEC sp_executesql @SQL;", tablename , files

The reason I use REPLACE on the file path is because QUOTENAME's first parameter datatype is sysname (effectively nvarchar(128) NOT NULL) and a file path can be longer than 128 characters. As such QUOTENAME could truncate the value; therefore I have used REPLACE instead.