Word Mail Merge with QueryString longer than 255 characters

S

Sebastian Martinez

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.AppSettings["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);
 
P

Peter Jamieson

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
 
S

Sebastian Martinez

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 said:
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 said:
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.AppSettings["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);
Click to expand...
Click to expand...
 
P

Peter Jamieson

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 said:
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 said:
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 said:
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.AppSettings["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);
Click to expand...
Click to expand...
Click to expand...
 
S

Sebastian Martinez

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.AppSettings["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 said:
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 said:
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 said:
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
Click to expand...
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

Hi,
I have an aplication where I use Automation of Word Mail Merge
Click to expand...
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();
Click to expand...
letterMerge.OpenDataSource(ConfigurationSettings.AppSettings["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);
Click to expand...
Click to expand...
Click to expand...
 
P

Peter Jamieson

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 said:
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.AppSettings["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 said:
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 said:
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.


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

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.AppSettings["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);
Click to expand...
Click to expand...
Click to expand...
 
Top