Reply
 
Thread Tools Display Modes
  #1   Report Post  
Mark Olbert
 
Posts: n/a
Default Sql Query: 255 character Limit

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=\"\";Us er 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
OLEDBat";

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?


  #2   Report Post  
Peter Jamieson
 
Posts: n/a
Default

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


Mark Olbert wrote in message
...
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=\"\";Us er
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
OLEDBat";

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?




  #3   Report Post  
ianc
 
Posts: n/a
Default

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...b;en-us;242072

"Peter Jamieson" wrote:

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


Mark Olbert wrote in message
...
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=\"\";Us er
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
OLEDBat";

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
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Save file as .doc having all data in ascii character set Freddy Microsoft Word Help 1 February 3rd 05 09:44 AM
Can't Open Data Source (Query) where criteria is defined by functi l_stocky Mailmerge 5 January 21st 05 03:01 PM
Character limit in mail merge Carson Mailmerge 1 January 6th 05 05:17 PM
255 character limit in Word mail merge Gibson Mailmerge 1 December 7th 04 10:36 AM
character spacing changed when document prints cindy Microsoft Word Help 1 December 6th 04 10:18 PM


All times are GMT +1. The time now is 12:15 PM.

Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"