3

I am using Delphi XE7 and connecting to a SQL Server 2012 Express Database using TAdoQuery and TAdoDataSet to query a database.

The connection is made through a TADOConnection object and a OLEDB UDL file (with the connection string of the TADOConnection being the "File Name=[UDL file path]").

One of the tables in this database has over 300 columns of various datatypes and when we add sql.text such as select * from table and make the TAdoQuery.Active property true we receive an Out Of Memory error.

If I reduce the number of columns in the select part of the statement then it appears to work ok so that would suggest it has something to do with the number of fields selected but I can't find any documentation regarding this.

I would like to be able to select 300 fields without receiving an out of memory error.

Tim Bowers
  • 41
  • 3
  • Have you tried to limit the records that return to the DataSet e.g. `SELECT TOP 10 * FROM table`? Also, you wrote *"...using TAdoQuery and TDataSet"*. Did you mean `TADODataSet`? – kobik May 17 '16 at 10:00
  • 1
    There is a rowsize limit in SQL server of 8kb, but maybe you are hitting a memory limit in ADO. Are you using client side or server side cursors? – whosrdaddy May 17 '16 at 10:02
  • Thanks Kobik I have adjusted my question regarding the TDataset. The issue is with the number of columns I believe not the number of rows as it fails when there are no rows returned. – Tim Bowers May 17 '16 at 10:03
  • @whosrdaddy Client Side cursors are in use. Would rowsize limit be an issue even when no rows are returned? – Tim Bowers May 17 '16 at 10:06
  • 1
    If you are using client side, yes. Anyway, if you have over 300 columns, it smells like the DB design is not good.... – whosrdaddy May 17 '16 at 10:09
  • @whosrdaddy I understand that and we are looking to change the structure in the long term. But if there are any short term fixes we can put in place it would be helpful as db structure change will not be quick. – Tim Bowers May 17 '16 at 10:14
  • 2
    255 field limit sounds like a familiar issue... maybe ADO or MDAC dependent, I don't remember (what windows version are you using?). If you're using XE7 you could try using FireDAC components instead. I'd seriously consider a more sane database design, though... 300 columns indeed sounds a bit crazy. A database is not Excel. – J... May 17 '16 at 10:15
  • Are you able to run this query in sql management studio? My guess is that @whosrdaddy might be right about row size limit. if you use fixed length columns such char, nchar you might hit the 8060 bytes limit. – kobik May 17 '16 at 10:57
  • @kobik The query runs ok in SQL MS. We do use quite a few fixed length columns as you suggest. – Tim Bowers May 17 '16 at 11:21
  • Have you tried to convert them to variable length? e.g. varchar, nvarchar.... – kobik May 17 '16 at 11:26
  • @kobik Apologies we do in fact use a large number of nvarchar/varchar and not very many nchar/char. – Tim Bowers May 17 '16 at 11:29
  • 1
    what version are you running on SQL server (express, standard,...), and what provider are you using (ie update your post with the connectionstring)? – whosrdaddy May 17 '16 at 12:35
  • I would try server side cursors (set cursorlocation to clUseServer on both Connection and query objects), see if that makes a difference. – whosrdaddy May 17 '16 at 12:43
  • @whosrdaddy given that a go but it still gives the same error message unfortunately – Tim Bowers May 17 '16 at 13:04
  • 1
    I see that you updated your question with some important details, now one thing still misses, what provider are you using? OLEDB, Native client, ? – whosrdaddy May 17 '16 at 13:25
  • Try using a table and not a query. Set the filter property of the table if you want to limit the number of rows retrieved. – Johan May 17 '16 at 14:02

0 Answers0