Executereader with forceplan

Question

1.00/5 (1 vote)

See more:

I am using SQL Server in a Vb.net program and when I try to specify an index with an ExecuteReader command that should be used, I get an error message about forceplan. I would like to use the index since it gives the best access. How do i force forceplan to be 'on' ?

What I have tried:

I open the database as follows:

VB.NET

conLocDb = New System.Data.SqlClient.SqlConnection
cmdLocDb = New System.Data.SqlClient.SqlCommand
conLocDb.ConnectionString = "Server=" + strLocDbDatasource + ";Database=" +
strLocDbDefaultcoll + ";Trusted_Connection=True;"
cmdLocDb.CommandTimeout = 0
conLocDb.Open()
cmdLocDb.Connection = conLocDb

Then my read is as follows:

VB.NET

cmdLocDb.CommandText = "Select IcData From Iscode with(index(Iscode01))" +
"Where IcCodt = 'GLSI' " +
"and IcCode = '" + Format(intCity, "000") + " SCOASO' " +
"and IcLang = 0 " +
"and IcAudl = 0"
Reader = cmdLocDb.ExecuteReader()

System.Data.SqlClient.SqlException:
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN

I have to remove the 'with(index...' for it to run, but would like to specify the index

So the question is where can I specify the 'forceplan' set ?

Posted 22-Nov-23 20:48pm

Member 16146638

Updated 22-Nov-23 23:02pm

Richard Deeming

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2023-11-22T21:54:00

Solution 1

SET FORCEPLAN (Transact-SQL) - SQL Server | Microsoft Learn[^]

Posted 22-Nov-23 21:54pm

Richard MacCutchan

Comments

Member 16146638 23-Nov-23 4:07am

Thanks Richard, but i have to specify it in VB code - that is the real problem

Richard Deeming 23-Nov-23 4:17am

No, the real problem is that you haven't shown us any of your code, or the exception message, or any real details of the problem.

Click the green "Improve question" link and update your question to include those missing details.

Member 16146638 23-Nov-23 4:25am

I open the database as follows:
conLocDb = New System.Data.SqlClient.SqlConnection
cmdLocDb = New System.Data.SqlClient.SqlCommand
conLocDb.ConnectionString = "Server=" + strLocDbDatasource + ";Database=" +
strLocDbDefaultcoll + ";Trusted_Connection=True;"
cmdLocDb.CommandTimeout = 0
conLocDb.Open()
cmdLocDb.Connection = conLocDb

Then my read is as follows:
cmdLocDb.CommandText = "Select IcData From Iscode with(index(Iscode01))" +
"Where IcCodt = 'GLSI' " +
"and IcCode = '" + Format(intCity, "000") + " SCOASO' " +
"and IcLang = 0 " +
"and IcAudl = 0"
Reader = cmdLocDb.ExecuteReader()

I have to remove the 'with(index...' for it to run, but would like to specify the index
So the question is where can i specify the 'forceplan' set ?

Richard MacCutchan 23-Nov-23 4:40am

Sorry, I have not used this feature; you will need to find examples elsewhere.

Richard Deeming 23-Nov-23 4:42am

And the error message you get is?

Member 16146638 23-Nov-23 4:49am

The message is 'System.Data.SqlClient.SqlException: 'Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.''

Richard Deeming 23-Nov-23 5:01am

That means there is no way to execute the query using that index. If you read the error message, you will see that adding SET FORCEPLAN is exactly the opposite of what it's telling you to do.

What happens if you try running precisely the same query in SQL Server Management Studio / Azure Data Studio? You will almost certainly get the same error.

You need to look at the structure of your table and the index you're trying to force to work out why the query optimizer is refusing to use it.

Member 16146638 23-Nov-23 7:18am

Hello Richard
The first 3 fields are exactly in the order of the key, only the last item is not in the key - do you think that that is the problem ?

Member 16146638 23-Nov-23 8:06am

Hello Richard
He really does not like it. Even if I use only the fields in the index and in the exact order the same error appears. So still the same problem.

Richard MacCutchan 23-Nov-23 8:59am

Do what it tells you in the error message first. Then gradually add each hint one at a time until the query fails. That should tell you what is wrong.