with nolock option in QueryBuilder option?

[#1]

Is there an option to run Query Builder including a "with nolock" statement to not tie up a database during a long data pull? Copying the QueryBuilder generated SQL into a script window and adding "with nolock"  produced an error.

[#2]

It looks like you sent the "With(NOLOCK)" message to JMP, instead of to the database.  Make sure it is part of the T-SQL you're sending to the database, like this:

Open Database(cnxn_string, "SELECT * FROM example WITH (NOLOCK)");

[#3]

Thanks for the reply! I attached an example of my code. I'm not sure how to send the with (nolock) to the database rather than JMP? Doesn't JMP pass along what is entered to the database?

New SQL Query(
	Version( 130 ),
	Connection( "ODBC:DSN=<enterdsn>;PWD=<enterpassword>" ),
	QueryName( "testquery" ),
	Select(
		Column( "colname1", "t1" ),
		Column( "colname2", "t1" ),
		Column(
			"colname3",
			"t1",
			Numeric Format( "m/d/y h:m:s", "0", "NO", "" )
		),
		Column( "colname4", "t1" )
	),
	From( Table( "table1", Schema( "dbname" ), Alias( "t1" ) ) ),
	Where(
		GE(
			Column(
				"colname3",
				"t1",
				Numeric Format( "m/d/y h:m:s", "0", "NO", "" )
			),
			3772915200,
			UI( Comparison( Base( "Continuous" ) ) )
		) & In List(
			Column( "colname4", "t1" ),
			{"col4val"},
			UI( SelectListFilter( ListBox, Base( "Categorical" ) ) )
		)
	),
	With( nolock )
) << Run; 

[#4]

Connection( "ODBC:DSN=<enterdsn>;PWD=<enterpassword>, With (nolock)" ),

When I added with (nolock) in the connection string I was still able to pull the data, but I'm not sure if the with (nolock) actually prevented the table from locking). Thoughts?

[#5]

I tried something similar, and it did not appear to pass the NOLOCK option to SQL Server.  When I use New SQL Query(), I use CustomSQL, like this:

New SQL Query(
Connection( cnxn_string ),
QueryName( "Important Query" ),
CustomSQL( "SELECT * FROM example WITH (NOLOCK) WHERE [Column 2] > 1" )
) << Run;

I am not sure there's a way to send the NOLOCK option without using CustomSQL within New SQL Query() or by using the Open Database() function.

*edit*

If you use the <<Generate SQL message on your current New SQL Query(), the result can be plugged into CustomSQL, and you can add "WITH(NOLOCK)" immediately after the point where the table alias is defined, like "....FROM table1 t1 WITH(NOLOCK) WHERE...."

[#6]

I think we're almost there. I tried your suggestion in CustomSQL, but I get the following error: 

Error running SQL Query:
[Microsoft][ODBC SQL Server Driver][SQL Server]You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels. [SQLSTATE=42000]

Is there a way to change the isolation level so with (nolock) can be used?

Or does with (readpast) accomplish what I need (ensure production isn't locked out from accessing/modifying the table while my query is running)?

[#7]

You can prepend the entire SQL statement with "SET TRANSACTION ISOLATION LEVEL READ COMMITTED"

or

"SET TRANSACTION LEVEL REPEATABLE READ"

From Microsoft: (link to page)

READ COMMITTED:

Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

REPEATABLE READ:

Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

So WITH(NOLOCK) is basically the same as READ UNCOMMITTED, but it doesn't matter, because WITH(NOLOCK) will override READ COMMITTED or REPEATABLE READ.

New SQL Query(
Connection( cnxn_string ),
QueryName( "Important Query" ),
CustomSQL( "SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM test1.dbo.example WITH(NOLOCK)
WHERE [Column 2] > 0.5" )
) << Run;

[#8]

I appreciate the continued responses.

"SET TRANSACTION LEVEL REPEATABLE READ" throws a syntax error. 

"SET TRANSACTION ISOLATION LEVEL READ COMMITTED" does not throw a syntax error. However, it does not appear that WITH(NOLOCK) will override READ COMMITTED or REPEATABLE READ as I get the following error in the log: Error running SQL Query:
[Microsoft][ODBC SQL Server Driver][SQL Server]You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels. [SQLSTATE=42000]

I'm running out of ideas for this one.

[#9]

Perhaps you don't have the permissions to set the isolation level.  Can you run the following T-SQL?

DBCC useroptions

If you can, it will tell you the default isolation level, and you can decide if NOLOCK is needed.

More information on these options from Microsoft here.  Since NOLOCK allows dirty reads (changes that aren't committed to the database and may revert or change again), MS suggests the following:

You can minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications by using either of the following:

• The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set ON.

• The SNAPSHOT isolation level.

You'll get this sorted eventually.