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);

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);

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);

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);

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);

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);