• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1498
  • Last Modified:

Maximium length of SQL string in VB6, Access 2000 and DAO...?


   This is more an SQL question than a SQL server question,
but this still seemed to be the best forum to ask.

   I need to know the maximum length in characters of
raw SQL command strings in each of several relevant
Microsoft technologies, namely: VB6, Access 2000 and
DAO (last version). A guess at the answer for Access 2002,
ADO (latest version), ADO.net and even MySQL would
be helpful, too. The first three are the most important
to me (VB6, etc.).

   Links to authoritative sources would be terrific.

   In ADO, for instance, what I'm looking for would be
the maximum length of the Command.CommandText property.


  • 9
  • 3
1 Solution
VB6 string:
A variable-length string can contain up to approximately 2 billion (2^31) characters.
Access 2000 memo
Limited to size of the database.
With ADODB, you can use functions working with chunks.
i.e. you use limit of the database.
Directly in ADODB adLongVarChar - 2^31 chars.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

lambAuthor Commented:
ispaleny wrote:

>VB6 string:
>A variable-length string can contain up to approximately 2 billion (2^31) characters.

>Access 2000 memo
>Limited to size of the database.

>With ADODB, you can use functions working with chunks.
>i.e. you use limit of the database.
>Directly in ADODB adLongVarChar - 2^31 chars.

   Thanks for responding. It isn't clear to me that the intent behind my question was understood.

   What I'm trying to find out isn't really the maximum
length of character strings under the various technologies, but rather the maximum length of SQL commands in practice (in other words actually implementable).

   I mean, would any SQL database engine anywhere actually
accept an SQL _command_ containing 2 billion characters!??
(The command, not the result.) If so, it's news/surprising
to me. Would there be a web reference on this?

   I don't understand why the "Memo" field was cited
regarding Access--what does it have to do with SQL?
In this case, what I really want to know is how big
an SQL command can you throw at the Access 2000 SQL engine
before it barfs? Under DAO (key, for me) and ADO.

   In ADO, I mentioned the Command.CommandText property,
but I didn't see a response to that.


In MSSQL2K is default maximum size of batch 256 MB ( for 4kB packet).
Into ADODB.Command.CommandText, you can put all the VB string (2GB), but will it be used ?  Who knows.

Long running, time consuming tests must be done. Something like
select MaxLen=datalength('xxxxxxx...xxxxxxxxxxxx')
I tried 50 kB of "x" on MSSQL2K and I works :)
But I am not able to test all the enviroments you mentioned.

Someone else must do it for you.
lambAuthor Commented:
ispaleny wrote:

>In MSSQL 2K is default maximum size of batch 256 MB
> ( for 4kB packet).

   Could you just expand on this point that you made
a little? Is a "batch" a batch SQL command? If it's
the default, I assume that it can be changed? Is 256 MB
the upper boundary for a batch, or something else?

   I'll happily send along your points--I'd like to
understand what you said, though!

From BOL:
Length of a string containing SQL statements (batch size) = 65,536 * Network packet size
Network Packet Size is the size of the tabular data scheme (TDS) packets used to communicate between applications and the relational database engine.
The default packet size is 4 KB, and is controlled by the network packet size configuration option.
[Network packet size]=512..65536 B

To view:
exec sp_configure 'network packet size (B)'

To set:
exec sp_configure 'network packet size (B)',4096
So the upper limit is from 32MB to 4GB. Both for 2000 and 7.0, any edition.
For ACCESS I found "ABOUT 64000 CHARS".
This information is directly from help and it is not very accurate.
I measured 63998 chars by select len("xxxx...xxxx").
It is really "about".
lambAuthor Commented:
  Wasn't sure which of your (multitude of) answers
was closest to the original Q, but I chose this one.

At first, I misunderstood your question. This happens, when you post ACCESS/ADO/DAO/MySQL question to MSSQL forum.

Good luck with DAO !

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 9
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now