nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

with nolock option in QueryBuilder option?

bittnere

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.

with nolock error.PNG

Accepted answers

All comments

mmarchandTSI

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)");

bittnere

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;

bittnere

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?

mmarchandTSI

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...."