Reply
 
Thread Tools Display Modes
  #1   Report Post  
Sebastian Martinez
 
Posts: n/a
Default Word Mail Merge with QueryString longer than 255 characters

Hi,
I have an aplication where I use Automation of Word Mail Merge from SQL
Server, my problem is that the query I use to filter the data from a data
view of the database in SQL Server is longer than 255 characters so I canīt
use the method MailMerge.OpenDataSource.

The solution Iīve found is to make an MailMerge.OpenDataSource without a
query and then set the query using the property
MailMerge.DataSource.QueryString, but it always throw an exception of type
with the message Command failed.

Is there a solution?
Thanks


Here is part of the code:

docApp = new Word.Application();

letterDoc = docApp.Documents.Open(ref docPath, ref missingObject, ref
missingObject,
ref missingObject, ref missingObject, ref missingObject, ref missingObject,
ref missingObject,
ref missingObject, ref missingObject, ref missingObject, ref visible, ref
missingObject,
ref missingObject, ref missingObject, ref missingObject);



object objSQL;
string query;
query = "SELECT * FROM viw_DocumentReport WHERE " + whereClause;

Word.MailMerge letterMerge = letterDoc.MailMerge;
objSQL = (object)query.Trim();

letterMerge.OpenDataSource(ConfigurationSettings.A ppSettings["DocumentReport
Conection"].Trim(),
ref missingObject,ref missingObject,ref missingObject,ref missingObject,ref
missingObject,
ref missingObject,ref missingObject,ref missingObject,ref missingObject,ref
missingObject,
ref missingObject, ref missingObject, ref missingObject, ref missingObject,
ref missingObject);


letterMerge.DataSource.QueryString = query;
letterMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument;
Word.Document newDocument;
object pause = (object)false;
letterMerge.Check();
letterMerge.Execute(ref pause);
string fileName = "Printed_Document";
newDocument = docApp.ActiveDocument;
newDocument.SaveAs(ref newDocPath, ref missingObject,ref missingObject,ref
missingObject,ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref missingObject,ref missingObject,
ref missingObject,ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref missingObject);




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

Have you tried using the OpenDataSource parameters SQLStatement and
SQLStatement1 to extend the SQL beyond 255? The two strings, concatenated,
make up the complete SQL query (so you may need a space at the beginning of
SQLStatement1) ?

However, the total length limit for these strings can vary depending on the
version of Word and the data source (there was a particular problem in Word
2002 with OLEDB data sources) so that may not work.

Peter Jamieson

"Sebastian Martinez" wrote in message
...
Hi,
I have an aplication where I use Automation of Word Mail Merge from SQL
Server, my problem is that the query I use to filter the data from a data
view of the database in SQL Server is longer than 255 characters so I
canīt
use the method MailMerge.OpenDataSource.

The solution Iīve found is to make an MailMerge.OpenDataSource without a
query and then set the query using the property
MailMerge.DataSource.QueryString, but it always throw an exception of type
with the message Command failed.

Is there a solution?
Thanks


Here is part of the code:

docApp = new Word.Application();

letterDoc = docApp.Documents.Open(ref docPath, ref missingObject, ref
missingObject,
ref missingObject, ref missingObject, ref missingObject, ref
missingObject,
ref missingObject,
ref missingObject, ref missingObject, ref missingObject, ref visible, ref
missingObject,
ref missingObject, ref missingObject, ref missingObject);



object objSQL;
string query;
query = "SELECT * FROM viw_DocumentReport WHERE " + whereClause;

Word.MailMerge letterMerge = letterDoc.MailMerge;
objSQL = (object)query.Trim();

letterMerge.OpenDataSource(ConfigurationSettings.A ppSettings["DocumentReport
Conection"].Trim(),
ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref
missingObject,
ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref
missingObject,
ref missingObject, ref missingObject, ref missingObject, ref
missingObject,
ref missingObject);


letterMerge.DataSource.QueryString = query;
letterMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument;
Word.Document newDocument;
object pause = (object)false;
letterMerge.Check();
letterMerge.Execute(ref pause);
string fileName = "Printed_Document";
newDocument = docApp.ActiveDocument;
newDocument.SaveAs(ref newDocPath, ref missingObject,ref missingObject,ref
missingObject,ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref missingObject,ref missingObject,
ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref
missingObject,ref missingObject);






  #3   Report Post  
Sebastian Martinez
 
Posts: n/a
Default

Thanks for the answer but I have already tried to use the OpenDataSource
splitting the QueryString and also throws me an Exception with the message
"Word was unable to open the data source".
If it hepls for something I am using Office 2003.


"Peter Jamieson" wrote in message
...
Have you tried using the OpenDataSource parameters SQLStatement and
SQLStatement1 to extend the SQL beyond 255? The two strings, concatenated,
make up the complete SQL query (so you may need a space at the beginning

of
SQLStatement1) ?

However, the total length limit for these strings can vary depending on

the
version of Word and the data source (there was a particular problem in

Word
2002 with OLEDB data sources) so that may not work.

Peter Jamieson

"Sebastian Martinez" wrote in message
...
Hi,
I have an aplication where I use Automation of Word Mail Merge from

SQL
Server, my problem is that the query I use to filter the data from a

data
view of the database in SQL Server is longer than 255 characters so I
canīt
use the method MailMerge.OpenDataSource.

The solution Iīve found is to make an MailMerge.OpenDataSource without a
query and then set the query using the property
MailMerge.DataSource.QueryString, but it always throw an exception of

type
with the message Command failed.

Is there a solution?
Thanks


Here is part of the code:

docApp = new Word.Application();

letterDoc = docApp.Documents.Open(ref docPath, ref missingObject, ref
missingObject,
ref missingObject, ref missingObject, ref missingObject, ref
missingObject,
ref missingObject,
ref missingObject, ref missingObject, ref missingObject, ref visible,

ref
missingObject,
ref missingObject, ref missingObject, ref missingObject);



object objSQL;
string query;
query = "SELECT * FROM viw_DocumentReport WHERE " + whereClause;

Word.MailMerge letterMerge = letterDoc.MailMerge;
objSQL = (object)query.Trim();


letterMerge.OpenDataSource(ConfigurationSettings.A ppSettings["DocumentReport
Conection"].Trim(),
ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref
missingObject,
ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref
missingObject,
ref missingObject, ref missingObject, ref missingObject, ref
missingObject,
ref missingObject);


letterMerge.DataSource.QueryString = query;
letterMerge.Destination =

Word.WdMailMergeDestination.wdSendToNewDocument;
Word.Document newDocument;
object pause = (object)false;
letterMerge.Check();
letterMerge.Execute(ref pause);
string fileName = "Printed_Document";
newDocument = docApp.ActiveDocument;
newDocument.SaveAs(ref newDocPath, ref missingObject,ref

missingObject,ref
missingObject,ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref missingObject,ref missingObject,
ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref
missingObject,ref missingObject);








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

Can you post your code here please? I particularly need to know exactly what
you are putting into SQLStatement and SQLStatement1

Peter Jamieson

"Sebastian Martinez" wrote in message
...
Thanks for the answer but I have already tried to use the OpenDataSource
splitting the QueryString and also throws me an Exception with the message
"Word was unable to open the data source".
If it hepls for something I am using Office 2003.


"Peter Jamieson" wrote in message
...
Have you tried using the OpenDataSource parameters SQLStatement and
SQLStatement1 to extend the SQL beyond 255? The two strings,
concatenated,
make up the complete SQL query (so you may need a space at the beginning

of
SQLStatement1) ?

However, the total length limit for these strings can vary depending on

the
version of Word and the data source (there was a particular problem in

Word
2002 with OLEDB data sources) so that may not work.

Peter Jamieson

"Sebastian Martinez" wrote in message
...
Hi,
I have an aplication where I use Automation of Word Mail Merge from

SQL
Server, my problem is that the query I use to filter the data from a

data
view of the database in SQL Server is longer than 255 characters so I
canīt
use the method MailMerge.OpenDataSource.

The solution Iīve found is to make an MailMerge.OpenDataSource without
a
query and then set the query using the property
MailMerge.DataSource.QueryString, but it always throw an exception of

type
with the message Command failed.

Is there a solution?
Thanks


Here is part of the code:

docApp = new Word.Application();

letterDoc = docApp.Documents.Open(ref docPath, ref missingObject, ref
missingObject,
ref missingObject, ref missingObject, ref missingObject, ref
missingObject,
ref missingObject,
ref missingObject, ref missingObject, ref missingObject, ref visible,

ref
missingObject,
ref missingObject, ref missingObject, ref missingObject);



object objSQL;
string query;
query = "SELECT * FROM viw_DocumentReport WHERE " + whereClause;

Word.MailMerge letterMerge = letterDoc.MailMerge;
objSQL = (object)query.Trim();


letterMerge.OpenDataSource(ConfigurationSettings.A ppSettings["DocumentReport
Conection"].Trim(),
ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref
missingObject,
ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref
missingObject,
ref missingObject, ref missingObject, ref missingObject, ref
missingObject,
ref missingObject);


letterMerge.DataSource.QueryString = query;
letterMerge.Destination =

Word.WdMailMergeDestination.wdSendToNewDocument;
Word.Document newDocument;
object pause = (object)false;
letterMerge.Check();
letterMerge.Execute(ref pause);
string fileName = "Printed_Document";
newDocument = docApp.ActiveDocument;
newDocument.SaveAs(ref newDocPath, ref missingObject,ref

missingObject,ref
missingObject,ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref missingObject,ref missingObject,
ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref
missingObject,ref missingObject);










  #5   Report Post  
Sebastian Martinez
 
Posts: n/a
Default

The query is generated dinamicaly, this are examples of the values of
SQLStatement and SQLStatement1.

Example 1:
SQLStatement = "SELECT * FROM viw_DocumentReport WHERE DocumentCode =
'doc01' AND PrintDate IS NULL AND SendDocument = '1' AND
SocialSecurityNumber = '090909090' AND AsMemberNumber = '99999999999' AND
PatientFirstName LIKE 'JANIRA%' AND PatientLastName LIKE 'CRUZ RIVER"

SQLStatement1 = "A%' AND PatientSex = 'F' AND CompanyNumber = '90' AND
CompanyDescription LIKE 'Medical Card System%' AND AsGroupNumber = '90005'
AND ConditionProgramCode = 'A001' AND PatientEligible = '1' "

Example 1:
SQLStatement = "SELECT * FROM viw_DocumentReport WHERE DocumentCode =
'doc01' AND PrintDate IS NULL AND SendDocument = '1' AND
SocialSecurityNumber = '090909090' AND AsMemberNumber = '99999999999' AND
PatientFirstName LIKE 'JANIRA%' AND"

SQLStatement1 = " PatientLastName LIKE 'CRUZ RIVERA%' AND PatientSex = 'F'
AND CompanyNumber = '90' AND CompanyDescription LIKE 'Medical Card System%'
AND AsGroupNumber = '90005' AND ConditionProgramCode = 'A001' AND
PatientEligible = '1' "

The code from where I obtain the first example is:
query = "SELECT * FROM viw_DocumentReport WHERE " + whereClause;
Word.MailMerge letterMerge = letterDoc.MailMerge;
objSQL = query.Substring(0,255);
objSQL2 = query.Remove(0,255);

letterMerge.OpenDataSource(ConfigurationSettings.A ppSettings["DocumentReport
Conection"].Trim(),
ref missingObject,ref missingObject,ref missingObject,ref missingObject,ref
missingObject,
ref missingObject,ref missingObject,ref missingObject,ref missingObject,ref
missingObject,
ref missingObject, ref objSQL, ref objSQL2, ref missingObject, ref
missingObject);



The values of the second example where generated manually by me.


"Peter Jamieson" wrote in message
...
Can you post your code here please? I particularly need to know exactly

what
you are putting into SQLStatement and SQLStatement1

Peter Jamieson

"Sebastian Martinez" wrote in message
...
Thanks for the answer but I have already tried to use the OpenDataSource
splitting the QueryString and also throws me an Exception with the

message
"Word was unable to open the data source".
If it hepls for something I am using Office 2003.


"Peter Jamieson" wrote in message
...
Have you tried using the OpenDataSource parameters SQLStatement and
SQLStatement1 to extend the SQL beyond 255? The two strings,
concatenated,
make up the complete SQL query (so you may need a space at the

beginning
of
SQLStatement1) ?

However, the total length limit for these strings can vary depending on

the
version of Word and the data source (there was a particular problem in

Word
2002 with OLEDB data sources) so that may not work.

Peter Jamieson

"Sebastian Martinez" wrote in message
...
Hi,
I have an aplication where I use Automation of Word Mail Merge

from
SQL
Server, my problem is that the query I use to filter the data from a

data
view of the database in SQL Server is longer than 255 characters so I
canīt
use the method MailMerge.OpenDataSource.

The solution Iīve found is to make an MailMerge.OpenDataSource

without
a
query and then set the query using the property
MailMerge.DataSource.QueryString, but it always throw an exception of

type
with the message Command failed.

Is there a solution?
Thanks


Here is part of the code:

docApp = new Word.Application();

letterDoc = docApp.Documents.Open(ref docPath, ref missingObject, ref
missingObject,
ref missingObject, ref missingObject, ref missingObject, ref
missingObject,
ref missingObject,
ref missingObject, ref missingObject, ref missingObject, ref visible,

ref
missingObject,
ref missingObject, ref missingObject, ref missingObject);



object objSQL;
string query;
query = "SELECT * FROM viw_DocumentReport WHERE " + whereClause;

Word.MailMerge letterMerge = letterDoc.MailMerge;
objSQL = (object)query.Trim();



letterMerge.OpenDataSource(ConfigurationSettings.A ppSettings["DocumentReport
Conection"].Trim(),
ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref
missingObject,
ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref
missingObject,
ref missingObject, ref missingObject, ref missingObject, ref
missingObject,
ref missingObject);


letterMerge.DataSource.QueryString = query;
letterMerge.Destination =

Word.WdMailMergeDestination.wdSendToNewDocument;
Word.Document newDocument;
object pause = (object)false;
letterMerge.Check();
letterMerge.Execute(ref pause);
string fileName = "Printed_Document";
newDocument = docApp.ActiveDocument;
newDocument.SaveAs(ref newDocPath, ref missingObject,ref

missingObject,ref
missingObject,ref missingObject,ref missingObject,ref

missingObject,ref
missingObject,ref missingObject,ref missingObject,
ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref
missingObject,ref missingObject);















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

Not sure if you are still there, but it was a long time before I was in a
good position to check this again.

As far as i can see from a simple example there is still a problem in Word
2003 where the maximum combined length of SQLStatement and SQLStatement1 is
255 when you are using OLEDB providers - or at least some of them, including
the SQL Server provider.

As far as I can tell, this limitation does not exist if you use ODBC, but
then you lose access to Unicode data and possibly other more recent SQL
Server features. I suppose you could also create the query dynamically as a
View within SQl Server (perhaps using ADO) and use it, but that introduces
other issues that are almost certainly best avoided.

Peter Jamieson

"Sebastian Martinez" wrote in message
...
The query is generated dinamicaly, this are examples of the values of
SQLStatement and SQLStatement1.

Example 1:
SQLStatement = "SELECT * FROM viw_DocumentReport WHERE DocumentCode =
'doc01' AND PrintDate IS NULL AND SendDocument = '1' AND
SocialSecurityNumber = '090909090' AND AsMemberNumber = '99999999999'
AND
PatientFirstName LIKE 'JANIRA%' AND PatientLastName LIKE 'CRUZ RIVER"

SQLStatement1 = "A%' AND PatientSex = 'F' AND CompanyNumber = '90' AND
CompanyDescription LIKE 'Medical Card System%' AND AsGroupNumber =
'90005'
AND ConditionProgramCode = 'A001' AND PatientEligible = '1' "

Example 1:
SQLStatement = "SELECT * FROM viw_DocumentReport WHERE DocumentCode =
'doc01' AND PrintDate IS NULL AND SendDocument = '1' AND
SocialSecurityNumber = '090909090' AND AsMemberNumber = '99999999999'
AND
PatientFirstName LIKE 'JANIRA%' AND"

SQLStatement1 = " PatientLastName LIKE 'CRUZ RIVERA%' AND PatientSex =
'F'
AND CompanyNumber = '90' AND CompanyDescription LIKE 'Medical Card
System%'
AND AsGroupNumber = '90005' AND ConditionProgramCode = 'A001' AND
PatientEligible = '1' "

The code from where I obtain the first example is:
query = "SELECT * FROM viw_DocumentReport WHERE " + whereClause;
Word.MailMerge letterMerge = letterDoc.MailMerge;
objSQL = query.Substring(0,255);
objSQL2 = query.Remove(0,255);

letterMerge.OpenDataSource(ConfigurationSettings.A ppSettings["DocumentReport
Conection"].Trim(),
ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref
missingObject,
ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref
missingObject,
ref missingObject, ref objSQL, ref objSQL2, ref missingObject, ref
missingObject);



The values of the second example where generated manually by me.


"Peter Jamieson" wrote in message
...
Can you post your code here please? I particularly need to know exactly

what
you are putting into SQLStatement and SQLStatement1

Peter Jamieson

"Sebastian Martinez" wrote in message
...
Thanks for the answer but I have already tried to use the
OpenDataSource
splitting the QueryString and also throws me an Exception with the

message
"Word was unable to open the data source".
If it hepls for something I am using Office 2003.


"Peter Jamieson" wrote in message
...
Have you tried using the OpenDataSource parameters SQLStatement and
SQLStatement1 to extend the SQL beyond 255? The two strings,
concatenated,
make up the complete SQL query (so you may need a space at the

beginning
of
SQLStatement1) ?

However, the total length limit for these strings can vary depending
on
the
version of Word and the data source (there was a particular problem in
Word
2002 with OLEDB data sources) so that may not work.

Peter Jamieson

"Sebastian Martinez" wrote in message
...
Hi,
I have an aplication where I use Automation of Word Mail Merge

from
SQL
Server, my problem is that the query I use to filter the data from a
data
view of the database in SQL Server is longer than 255 characters so
I
canīt
use the method MailMerge.OpenDataSource.

The solution Iīve found is to make an MailMerge.OpenDataSource

without
a
query and then set the query using the property
MailMerge.DataSource.QueryString, but it always throw an exception
of
type
with the message Command failed.

Is there a solution?
Thanks


Here is part of the code:

docApp = new Word.Application();

letterDoc = docApp.Documents.Open(ref docPath, ref missingObject,
ref
missingObject,
ref missingObject, ref missingObject, ref missingObject, ref
missingObject,
ref missingObject,
ref missingObject, ref missingObject, ref missingObject, ref
visible,
ref
missingObject,
ref missingObject, ref missingObject, ref missingObject);



object objSQL;
string query;
query = "SELECT * FROM viw_DocumentReport WHERE " + whereClause;

Word.MailMerge letterMerge = letterDoc.MailMerge;
objSQL = (object)query.Trim();



letterMerge.OpenDataSource(ConfigurationSettings.A ppSettings["DocumentReport
Conection"].Trim(),
ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref
missingObject,
ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref
missingObject,
ref missingObject, ref missingObject, ref missingObject, ref
missingObject,
ref missingObject);


letterMerge.DataSource.QueryString = query;
letterMerge.Destination =
Word.WdMailMergeDestination.wdSendToNewDocument;
Word.Document newDocument;
object pause = (object)false;
letterMerge.Check();
letterMerge.Execute(ref pause);
string fileName = "Printed_Document";
newDocument = docApp.ActiveDocument;
newDocument.SaveAs(ref newDocPath, ref missingObject,ref
missingObject,ref
missingObject,ref missingObject,ref missingObject,ref

missingObject,ref
missingObject,ref missingObject,ref missingObject,
ref missingObject,ref missingObject,ref missingObject,ref
missingObject,ref
missingObject,ref missingObject);















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
Mail Merge Word 2003 Issue S.Nicks Mailmerge 5 February 20th 09 01:55 AM
Web-Site-based Word Mail Merge & Access DB Alex Maghen Mailmerge 3 January 17th 05 09:52 PM
Mail Merge Final Step in Word 2003 XP blnorwood Mailmerge 2 January 14th 05 06:02 AM
How do I create & merge specific data base & master documents? maggiev New Users 2 January 12th 05 11:30 PM
International characters in mail merge sandra Mailmerge 1 December 23rd 04 05:42 AM


All times are GMT +1. The time now is 11:40 AM.

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

About Us

"It's about Microsoft Word"