?
Solved

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

Posted on 2003-03-12
12
Medium Priority
?
1,451 Views
Last Modified: 2012-06-27
Folks,

   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.

Thanks!

lamb
0
Comment
Question by:lamb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 3
12 Comments
 
LVL 13

Expert Comment

by:ispaleny
ID: 8123153
VB6 string:
A variable-length string can contain up to approximately 2 billion (2^31) characters.
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8123180
Access 2000 memo
Limited to size of the database.
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8123232
With ADODB, you can use functions working with chunks.
i.e. you use limit of the database.
Directly in ADODB adLongVarChar - 2^31 chars.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:lamb
ID: 8128362
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.

Thanks,

-lamb
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8129082
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.
0
 

Author Comment

by:lamb
ID: 8129482
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!

-lamb
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8130515
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
RECONFIGURE
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8130588
So the upper limit is from 32MB to 4GB. Both for 2000 and 7.0, any edition.
0
 
LVL 13

Accepted Solution

by:
ispaleny earned 2000 total points
ID: 8130638
For ACCESS I found "ABOUT 64000 CHARS".
This information is directly from help and it is not very accurate.
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8130709
I measured 63998 chars by select len("xxxx...xxxx").
It is really "about".
0
 

Author Comment

by:lamb
ID: 8131229
  Wasn't sure which of your (multitude of) answers
was closest to the original Q, but I chose this one.

   Thanks!!
0
 
LVL 13

Expert Comment

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

Good luck with DAO !
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question