@Nick_Pacey Can you try the below approach and let me know how it goes. Make sure that the firewall on the SQL Server machine allows traffic on the dynamic port that the named instance is using. You can find the port being used by the named instance in SQL Server Configuration Manager under SQL Server Network Configuration.
%python
import pymssql
#jdbcHostname = "servername"
#jdbcPort = 1433
jdbcHostname = "servername\\instancename"
jdbcPort = 1433 # Typically, named instances use dynamic ports, so this might not be necessary.
jdbcDatabase = "databasename"
jdbcUsername = "username"
jdbcPassword = dbutils.secrets.get('vault-scope','pwd')
connectionProperties = {
"user" : jdbcUsername,
"password" : jdbcPassword,
"driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver"
}
jdbcUrl = f"jdbc:sqlserver://{jdbcHostname}:{jdbcPort};database={jdbcDatabase}"
conn = pymssql.connect(
server=jdbcHostname, database=jdbcDatabase, user=jdbcUsername, password=jdbcPassword
)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
# Execute Merge logic in SQL Server
merge_query = """
MERGE INTO employees AS target
USING new_employees AS source
ON target.id = source.id
WHEN MATCHED THEN
UPDATE SET target.name = source.name, target.salary = source.salary
WHEN NOT MATCHED THEN
INSERT (id, name, salary)
VALUES (source.id, source.name, source.salary);
"""
# Execute the merge query
cursor.execute(merge_query)
#Commit the changes to the database
conn.commit()
# Provide a success message
print(f"Merge statement executed successfully.")
# Close the database connection
conn.close()
