How to solve this issue?

Question

4.00/5 (1 vote)

See more:

My application connects to a data base and starts to read data after a while i got the message below: "Impossible to execute the request. The request result size is higher than the database size(2 Go) or, temporary space folder on the disk is insufficient to store the result".

Any idea on how to overcome this problem??
Please try to be clear and give examples if possible.
Thanks

My code is givien below:

C#

public DataTable read(string query)
{   
   OleDbDataReader dr;
   // Connecting to the data base
   conn.ConnectionString = ConString;
   // Prepare SQL query
          
   try
   {
      if (conn.State != ConnectionState.Open)
         conn.Open();

   }
   catch (OleDbException ex)
   {
      System.Windows.Forms.MessageBox.Show("Failed to connect to data source " + ex.Message);
   }

   try
   {
      // Run the query and create a record set
      OleDbCommand cmd = new OleDbCommand(query, conn);
      dr= cmd.ExecuteReader();
      /*  Console.WriteLine("Retrieve schema info for the given result set:");
      for (int column = 0; column < dr.FieldCount; column++)
      {
         Console.Write(" |" + dr.GetName(column));
      }
      while (dr.Read())
      {
         Console.WriteLine("dal: " + dr.GetValue(0).ToString() + " | " + dr.GetValue(1).ToString());
      }*/
      dt.Load(dr);
      dr.Close();
   }
   catch (OleDbException ex)
   {
      System.Windows.Forms.MessageBox.Show("There might be a problem while recording the dataset " + ex.Message);
   }
   finally
   {
      conn.Close();
   }
   return dt;
}

additional information copied from comment below
This is the query: I use Access database

SQL

string queryC = "SELECT DISTINCT dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_LIEN, dbo_BOGEDOTXM_OCCUP_LIENS_SDH.TAUX_OCCUPATION, dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_EQPT_A, dbo_BOGEDOTXM_OCCUP_LIENS_SDH.PORT_A, dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_EQPT_B, dbo_BOGEDOTXM_OCCUP_LIENS_SDH.PORT_B, dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_SLOT_A, dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_SLOT_B FROM dbo_BOGEDOTXM_OCCUP_LIENS_SDH INNER JOIN ((dbo_EQUIPEMENT INNER JOIN dbo_OSMOSE_SITE_GEOGRAPHIQUE ON dbo_EQUIPEMENT.NUM_SITE_GEO = dbo_OSMOSE_SITE_GEOGRAPHIQUE.NUM_SITE_GEO) INNER JOIN dbo_OSMOSE_OPERATION ON dbo_OSMOSE_SITE_GEOGRAPHIQUE.NUM_SITE_GEO = dbo_OSMOSE_OPERATION.NUM_SITE_GEO) ON dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_EQPT_A = dbo_EQUIPEMENT.NOM_EQUIPEMENT WHERE (( (dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_LIEN) Like '%AT%' Or (dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_LIEN) Like '%BT%' Or (dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_LIEN) Like '%CT%' Or (dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_LIEN) Like '%DT%') AND ((dbo_OSMOSE_OPERATION.REGION_EXPLOITATION)=" + region + "));";

Posted 7-Sep-14 11:34am

orélle

Updated 9-Sep-14 7:36am

Nelek

v6

Add a Solution

Comments

[no name] 7-Sep-14 17:53pm

Try querying data that is less than the size of the database or free up some room on the disk.

orélle 7-Sep-14 18:01pm

When the size of the data fits, it works. What is the location of the temporary folder?
I have a lot of free space on my disk anyway. If might be possible to increase the defautt value of the database result to more than 2 Go it will overcome the issue.

George Jonsson 7-Sep-14 22:53pm

Maybe you should show the query that is causing the problem.

Thomas Nielsen - getCore 9-Sep-14 2:56am

Is this an oracle backend or Access?

orélle 9-Sep-14 7:19am

This is the query: I use Access database

string queryC = "SELECT DISTINCT dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_LIEN, dbo_BOGEDOTXM_OCCUP_LIENS_SDH.TAUX_OCCUPATION, dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_EQPT_A, dbo_BOGEDOTXM_OCCUP_LIENS_SDH.PORT_A, dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_EQPT_B, dbo_BOGEDOTXM_OCCUP_LIENS_SDH.PORT_B, dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_SLOT_A, dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_SLOT_B FROM dbo_BOGEDOTXM_OCCUP_LIENS_SDH INNER JOIN ((dbo_EQUIPEMENT INNER JOIN dbo_OSMOSE_SITE_GEOGRAPHIQUE ON dbo_EQUIPEMENT.NUM_SITE_GEO = dbo_OSMOSE_SITE_GEOGRAPHIQUE.NUM_SITE_GEO) INNER JOIN dbo_OSMOSE_OPERATION ON dbo_OSMOSE_SITE_GEOGRAPHIQUE.NUM_SITE_GEO = dbo_OSMOSE_OPERATION.NUM_SITE_GEO) ON dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_EQPT_A = dbo_EQUIPEMENT.NOM_EQUIPEMENT WHERE (( (dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_LIEN) Like '%AT%' Or (dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_LIEN) Like '%BT%' Or (dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_LIEN) Like '%CT%' Or (dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_LIEN) Like '%DT%') AND ((dbo_OSMOSE_OPERATION.REGION_EXPLOITATION)=" + region + "));";

orélle 9-Sep-14 9:33am

It's Access

Thomas Nielsen - getCore 10-Sep-14 3:14am

Ok, i see the solutions are comming in. Great!
i would second the thought of moving to MSSQL, the problem being that MSAccess is an ISAAM database and essentially meaning a flat file with data stored sequentially and this will not perform if data is big enough. Access also supports being an ADP which will allow you to continue to use access but against a MSSQL database backend which is very efficient combintation. if you use MSSQL Express formely known as MSDE you can use it for free.
Anyway then you could use MS SQL Management studio to look at 'actual execution plan' which would suggest you possible indexes depending on your query, which could also help.
The root of the problem you experience lies in your wildcard searches but the right solution if everything being filtered on is indexed properly is already being developed on this thread i see, so my five cents of suggestion is to change to Access ADP. From personal experience when users increase over a certain number, more or less just beginning 2 digits of concurrent users, you really wont get the performance we all love from an ISAAM 'RDBMS' so begin that migration project too i would suggest. But of cause all text searches should have indexes on what is being wildcard ('like') searched on :)

3 solutions

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)

MukeshSagar · Answer 1 · 2014-09-07T18:49:00

Solution 1

If the database value are stored in database, then there is no issue regarding the space while retrieving the data from database. There may be issue in your query / Stored procedure that is used to fetch the data from database.

Posted 7-Sep-14 18:49pm

MukeshSagar

Comments

orélle 9-Sep-14 7:23am

i have posted the query above. The query runs well if i execute directly on access so it's correct, the issue is while reading data in my application. Thanks

MukeshSagar · Answer 2 · 2014-09-09T02:29:00

Solution 2

It is better to change the database from Microsoft Access to Microsoft SQL Server, which you can install separately or use the in build SQL Server Express that is available with in Visual Studio.

Posted 9-Sep-14 2:29am

MukeshSagar

Comments

orélle 9-Sep-14 8:39am

Thanks for your suggestion but the problem is that i'm not admin on my computer then i can not install whatever i want!! :(

Manikandan MAC · Answer 3 · 2014-09-09T05:44:00

Hi, Could you try this, may this help you,

SQL

string queryC = "SELECT
DISTINCT dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_LIEN, dbo_BOGEDOTXM_OCCUP_LIENS_SDH.TAUX_OCCUPATION, dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_EQPT_A,
dbo_BOGEDOTXM_OCCUP_LIENS_SDH.PORT_A, dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_EQPT_B, dbo_BOGEDOTXM_OCCUP_LIENS_SDH.PORT_B,
dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_SLOT_A, dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_SLOT_B

FROM dbo_BOGEDOTXM_OCCUP_LIENS_SDH
INNER JOIN dbo_EQUIPEMENT ON dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_EQPT_A = dbo_EQUIPEMENT.NOM_EQUIPEMENT
INNER JOIN dbo_OSMOSE_SITE_GEOGRAPHIQUE ON dbo_EQUIPEMENT.NUM_SITE_GEO = dbo_OSMOSE_SITE_GEOGRAPHIQUE.NUM_SITE_GEO
INNER JOIN dbo_OSMOSE_OPERATION ON dbo_OSMOSE_SITE_GEOGRAPHIQUE.NUM_SITE_GEO = dbo_OSMOSE_OPERATION.NUM_SITE_GEO

WHERE (( (dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_LIEN) Like '%AT%'
Or (dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_LIEN) Like '%BT%'
Or (dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_LIEN) Like '%CT%'
Or (dbo_BOGEDOTXM_OCCUP_LIENS_SDH.NOM_LIEN) Like '%DT%')
AND ((dbo_OSMOSE_OPERATION.REGION_EXPLOITATION)=" + region +  "))";

Please give me your feedback,

How to solve this issue?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us