We have a company mandate to have every join include the directive "WITH (NOLOCK)". This is for a newer SQL Server where the isolation level can cause problems.
Unfortunately the Query Builder does not support the "WITH (NOLOCK)" option. Can we get this added? It could be put in the right click join menu or join properties dialog. Many databases have equivalent syntax.
This is a VERY BIG issue for this company. They bought Toad with a large number of Analysts needing to use a SQL Visual designer. They are upset when I told them the Query Builder does not support this.
When you make any changes in the Query tab you have to visualize the change back into the Query Builder. It chokes on the WITH (NOLOCK) and refuses to visualize. So unfortunately manual edit does not work.
OK... assuming that the version of your SQL query with the NOLOCK keyword executes successfully within TDP (Editor), then can you use the Editor in the Workflow step instead of the Visual Query Builder?
That is what I am doing now. But I am getting kick-back from the analyst who want to pick up the workbook. They want to use the Query Builder. It is easier for them as they are not SQL Experts. When I mention that the SQL Editor will support it they come back with --> "But the whole reason we bought Toad was for the Visual Query support". I know in the end the Query Builder has its limitations and you usually end up using the editor if you work on the sql much.
NOTE: In this shop as well as another that I know of, one person builds the Brio workbooks and gives them to others to use. These are built for specific use cases that are not covered by normal reporting. The workbooks are not built by the end user but they have a need to tweak things every once in a while. So when you look at it that way needing the Query Builder makes more sense.
Thanks for the details and, yes, the use case seems valid enough, and the reason why the suggestion has already been added to our TDP backlog for enhancements.
I'll just jump in here. I had already complained about this and it also affects WITH (READUNCOMMITTED). No support in QueryBuilder. The QueryBuilder is not usable for us either and we will do without TOAD in the future if this is not fixed soon. TOAD must offer at least the same functionality as MSSMS. Everything else would be the comfort to switch to TOAD.
I forgot to mention I am using a work around while I wait for the enhancement. Toad supports running a sql script of your choice prior to opening every connection. I am now using this feature to run the following SQL which sets the isolation level to what I want.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
You need to name your script and point this option path to this script.
Once you have set this up you can run the following SQL in an editor and see what the isolation level is.
SELECT CASE
WHEN transaction_isolation_level = 1
THEN 'READ UNCOMMITTED'
WHEN transaction_isolation_level = 2
AND is_read_committed_snapshot_on = 1
THEN 'READ COMMITTED SNAPSHOT'
WHEN transaction_isolation_level = 2
AND is_read_committed_snapshot_on = 0 THEN 'READ COMMITTED'
WHEN transaction_isolation_level = 3
THEN 'REPEATABLE READ'
WHEN transaction_isolation_level = 4
THEN 'SERIALIZABLE'
WHEN transaction_isolation_level = 5
THEN 'SNAPSHOT'
ELSE NULL
END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions AS s
CROSS JOIN sys.databases AS d
WHERE session_id = @@SPID
AND d.database_id = DB_ID();
