topic Re: Databricks External Data SQL Server Connection Dirty Reads in Data Engineering

Databricks External Data SQL Server Connection Dirty Reads

wallco26 — Wed, 10 Jul 2024 21:30:00 GMT

I've connected a SQL Server database as an external connection in Unity Catalog. It looks like when I write SELECT queries to that connection I end up locking my tables on the SQL Server. Is there a way to query these tables using a "with (nolock)" command like I would inside SQL Server? I've done this in the past in Databricks by using a JDBC connection to the same server and adding "with (nolock)" to the right of my table name string.

Re: Databricks External Data SQL Server Connection Dirty Reads

wallco26 — Mon, 15 Jul 2024 16:43:06 GMT

Thanks Kaniz. How would I go about doing this? I don't see a hint option in the Connection UI or the Catalog UI.

Re: Databricks External Data SQL Server Connection Dirty Reads

szymon_dybczak — Mon, 15 Jul 2024 17:47:34 GMT

Hi @wallco26 ,

If you can't find options on the UI, you can always use SQL syntax to create foreign catalog:

CREATE FOREIGN CATALOG [IF NOT EXISTS] <catalog-name> USING CONNECTION <connection-name>

OPTIONS (database '<database-name>');

Re: Databricks External Data SQL Server Connection Dirty Reads

wallco26 — Tue, 16 Jul 2024 14:56:40 GMT

Thanks Slash - where would the "with (nolock)" command fall into the SQL Syntax...within the OPTIONS section? What would the specific command look like?