Unable to Drop a View in Serverless SQL Pool from Synapse Notebook

I am trying to drop a view from my synpase Notebook using a connection to a Serverless SQL Pool. I followed the instructions from this post: Access our built-in serverless SQL pool from our notebook and successfully made the connection. I can query my tables without any issues. However, when I try to drop a view I get this error: An error occurred while calling o4311.jdbc. The code snippet I used below:

%%pyspark
server = 'REDACTED'
Port = 1433
Database = "REDACTED"
jdbcUrl = f"jdbc:sqlserver://{server}:{Port};databaseName={Database};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"
token=TokenLibrary.getConnectionString("LinkTest")
query = "DROP VIEW dbo.TransformationTest"
conn_Prop = {
"driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver",
"accessToken" : token
}

df = spark.read.jdbc(url=jdbcUrl, table=query, properties=conn_Prop)
display(df)

I was advised to use pyodbc for DDL commands, but I encountered an error with TokenLibrary saying that it is unrecognized, even though it was recognized before using pyodbc. This is The code snipet used:

%%pyspark
%pip install pyodbc
import pyodbc
CONNECTION CODE
token=TokenLibrary.getConnectionString("LinkTest")
conn_str = f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={server},{Port};DATABASE={Database};Authentication=ActiveDirectoryMsi'

# Establish a connection
conn = pyodbc.connect(conn_str)

# Create a cursor from the connection
cursor = conn.cursor()

# Define and execute the DROP TABLE query
drop_query = "DROP VIEW dbo.TransformationTest"
cursor.execute(drop_query)

# Commit the transaction
conn.commit()

# Close the connection
cursor.close()
conn.close()

This is the error I get: NameError: name 'TokenLibrary' is not defined

I would appreciate any guidance on how to successfully drop the view or any insights into resolving the TokenLibrary error with pyodbc.

Error: An error occurred while calling o4311.jdbc

Regarding the above ERROR make sure if you are using the updated drivers for jdbc

To check the driver installed in your Synapse notebook, you can use the following commands.

import pyodbc
pyodbc.drivers()

Results:

['ODBC Driver 18 for SQL Server']

NameError: name 'TokenLibrary' is not defined

Regarding the TokenLibrary I found similar issue about Error accessing Key Vault while executing Azure Synapse Notebook via Synapse Pipelines Please check if it helps you.

I also have tried the below approach:

I have created a view in the serverless pool called [dbo].[populationView]

I have used the below code to drop the view in the serverless pool:

import struct
import pyodbc
server = 'Serverless SQL endpoint-ondemand.sql.azuresynapse.net'
dbname = "Serverlessdb"
auth_key = bytes(mssparkutils.credentials.getToken("DW"), 'utf8')
driver = "{ODBC Driver 18 for SQL Server}"
schema = "dbo"  # Specify the schema name
view_name = "populationView"
connection_string = f"DRIVER={driver};SERVER={server};DATABASE={dbname}"
exp_token = b""
for i in auth_key:
    exp_token += bytes({i})
    exp_token += bytes(1)
token_struct = struct.pack("=i", len(exp_token)) + exp_token
import pyodbc
pyodbc.drivers()
with pyodbc.connect(connection_string, attrs_before={ 1256:token_struct }) as conn:
    conn.autocommit = True
    cursor = conn.cursor()
    cursor.execute("SELECT OBJECT_ID(?, 'V')", f"{schema}.{view_name}")  # Use 'V' for views
    object_id = cursor.fetchone()[0]
    if object_id is not None:
        print(f"Dropping VIEW {schema}.{view_name}")
        cursor.execute(f"DROP VIEW {schema}.{view_name}")
    else:
        print(f"{schema}.{view_name} does not exist")

Results:

Dropping VIEW dbo.populationView