Sql Query: 255 character Limit

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark Olbert - 31 Jan 2005 02:43 GMT

Situation:

Office Apps: Excel XP and Word XP

My .NET Windows.Forms app creates an Excel file using OLE Automation and populates that file with
data from a database. I then use MailMerge.OpenDataSource() to open the Excel file with the
following connection string and Sql command:

string connStg = "Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";User ID=Admin;Data Source=<insert
file user-selected Excel data file with full path here>;Mode=Read;Extended
Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet
OLEDB:Dat";

string sqlStg = "SELECT * FROM `Sheet1$`";

These are based to OpenDataSource() as ref'd objects.

Problem:

There are actually two mailmerge operations I'm testing. The first succeeds, the second fails with
the infamous "255 character limit" exception. The length of connStg + sqlStg is 277 characters for
the first, successful merge and 290 characters for the second, failing merge. I'm not sure if those
character lengths are what Word is "seeing" on its end, as .NET uses Unicode and I'm not sure what
Word uses internally.

Any suggestions on how to resolve this situation?

- Mark

p.s. BTW, may I assume that whatever, ahem, person left a 255 character limit in the Word mailmerge
subsystem at least through Office XP has been summarily fired?

Reply to this Message

Peter Jamieson - 31 Jan 2005 10:55 GMT

In this case you may find that using both SQLStatement and SQLStatement1
parameters in the OpenDataSource call fixes the problem - just make sure
that the two strings, when concatenated, have valid SQL. I don't think you
can do this by setting the QueryString property.

However, there definitely used to be a problem with Word XP and OLEDB
providers whereby the combined length limit, which should be more like 511
chars, is actually only more like 255. So this may not work either. I only
looked at it briefly a couple of years ago but also suspected the problem
may have had something to do with byte count vs. character count.

The only other things I can suggest are
1. using ODBC insted of OLEDB. Unfortunately, you cannot do a DSN-less
connection so you must have a DSN of some kind on the user machine
2. create the .xls you really need for the merge, if possible - if
necesary, in addition to the .xls you (presumably) need for other purposes
3. you might be better off putting your data in a .mdb, e.g. using ADO and
ADOX, and creating a Query/View in the database using ADO, then using that
as the data source. if you don't need the .xls, that might be enough. If you
do, you could consider
a. creating the .mdb as well
b. creating the .xls, and creating a .mdb with a query that grabs data from
the .xls, and using the query as the data source.

All messy, and I don't know for sure if any of them will work,

Peter Jamieson

> Situation:
>
[quoted text clipped - 36 lines]
> limit in the Word mailmerge
> subsystem at least through Office XP has been summarily fired?

Reply to this Message

ianc - 31 Jan 2005 19:33 GMT

Not sure if this is appropriate but I had a similar problem and found a
solution on MSDN using DDE -
http://support.microsoft.com/default.aspx?scid=kb;en-us;242072

> In this case you may find that using both SQLStatement and SQLStatement1
> parameters in the OpenDataSource call fixes the problem - just make sure
[quoted text clipped - 64 lines]
> > limit in the Word mailmerge
> > subsystem at least through Office XP has been summarily fired?

Reply to this Message

Rate this thread: