Statement parameter in Mailmerge.OpenDataSource

[Christof Nordiek]

i'm trying to make some MailMerge by automating word2003.
If i call MailMerge.OpenDataSource, what can i pass for the Satement
Parameter?
For Connectioninformation i'm using a *.odc file, wich connects to a
SQL-Server.
The name of the odc-file i pass as the Name parameter.

when I try
SELECT * FROM MyView
it fails.

SELECT * FROM "MyView"
succeeds.

Also passing the name of the view suceeds only with quotationmarks

What i'm trying to do is, passing a statement calling a table-valued
Function with parameters and/or a statement with a where clause containing
Filterconditions.

Does anyone know, how to do this?

Thanks

 

[Peter Jamieson]

You can certainly pass an SQL SELECT statement with a WHERE clause. Getting
the syntax right can be difficult, as you have discovered. What I find
generally works is
a. quoting table and column names. I think it is clearer to use `` or []
rather than "" but it may be less standard these days
b. aliasing tables - even when aliasing should not be syntactiaclly
required - and always using the alias names when referencing individual
columns
c. using single quotes ' ' as text literal delimiters
e.g. (not tested - you'll need to play around to check)

SELECT [MV].* FROM [MyView] MV WHERE [MV].[MyColumn] = 'abc'

You may also be able to pass a call to a Transact-SQL procedure using the
old ODBC escape syntax which looks something like

{ call functionname(p1,p2) }

You may be able to find out more about that in SQL Server Books Online.
However, I have had a mixed experience using that approach in the past
because, for example, procedures that return multiple results do not seem to
work.

Peter Jamieson

 

[Peter Jamieson]

OH yes, also SQLStatement has a maximum length of around 255 characters. You
/may/ be able to get more by using SQLStatement1 as well, in which case the
complete statement is the concatenation of SQLStatement and SQLStatement1
and you may get up to around 511 characters, but it does vary depending on
the version of Word, the data source and the connection method.

Peter Jamieson

You can certainly pass an SQL SELECT statement with a WHERE clause.
Getting the syntax right can be difficult, as you have discovered. What I
find generally works is
a. quoting table and column names. I think it is clearer to use `` or []
rather than "" but it may be less standard these days
b. aliasing tables - even when aliasing should not be syntactiaclly
required - and always using the alias names when referencing individual
columns
c. using single quotes ' ' as text literal delimiters
e.g. (not tested - you'll need to play around to check)

SELECT [MV].* FROM [MyView] MV WHERE [MV].[MyColumn] = 'abc'

You may also be able to pass a call to a Transact-SQL procedure using the
old ODBC escape syntax which looks something like

{ call functionname(p1,p2) }

You may be able to find out more about that in SQL Server Books Online.
However, I have had a mixed experience using that approach in the past
because, for example, procedures that return multiple results do not seem
to work.

Peter Jamieson

i'm trying to make some MailMerge by automating word2003.
If i call MailMerge.OpenDataSource, what can i pass for the Satement
Parameter?
For Connectioninformation i'm using a *.odc file, wich connects to a
SQL-Server.
The name of the odc-file i pass as the Name parameter.

when I try
SELECT * FROM MyView
it fails.

SELECT * FROM "MyView"
succeeds.

Also passing the name of the view suceeds only with quotationmarks

What i'm trying to do is, passing a statement calling a table-valued
Function with parameters and/or a statement with a where clause
containing
Filterconditions.

Does anyone know, how to do this?

Thanks

 

[Christof Nordiek]

Hi Peter

OK, i now can use a statement with a where-clause for filtering. The next
thing i have to do is, call a table-valued function and give it an actual
value for a parameter.

SELECT * FROM MyFuction(58)

works from QueryAnalyzer and from .NET-application but not for Mail-Merge.
Do You know, how to do this??

Thanks

You can certainly pass an SQL SELECT statement with a WHERE clause.
Getting the syntax right can be difficult, as you have discovered. What I
find generally works is
a. quoting table and column names. I think it is clearer to use `` or []
rather than "" but it may be less standard these days
b. aliasing tables - even when aliasing should not be syntactiaclly
required - and always using the alias names when referencing individual
columns
c. using single quotes ' ' as text literal delimiters
e.g. (not tested - you'll need to play around to check)

SELECT [MV].* FROM [MyView] MV WHERE [MV].[MyColumn] = 'abc'

You may also be able to pass a call to a Transact-SQL procedure using the
old ODBC escape syntax which looks something like

{ call functionname(p1,p2) }

You may be able to find out more about that in SQL Server Books Online.
However, I have had a mixed experience using that approach in the past
because, for example, procedures that return multiple results do not seem
to work.

Peter Jamieson

i'm trying to make some MailMerge by automating word2003.
If i call MailMerge.OpenDataSource, what can i pass for the Satement
Parameter?
For Connectioninformation i'm using a *.odc file, wich connects to a
SQL-Server.
The name of the odc-file i pass as the Name parameter.

when I try
SELECT * FROM MyView
it fails.

SELECT * FROM "MyView"
succeeds.

Also passing the name of the view suceeds only with quotationmarks

What i'm trying to do is, passing a statement calling a table-valued
Function with parameters and/or a statement with a where clause
containing
Filterconditions.

Does anyone know, how to do this?

Thanks

 

[Peter Jamieson]

Do You know, how to do this??

No, I haven't been able to find a way to use a table-valued function using
OLEDB. The only way I can get something similar to work is to use ODBC. For
example, if you have a user/system DSN called sql2000, in Word 2002/2003 the
following works here with suitable values for server, database and security
values:

OpenDataSource _
Name:="", _
Connection:="DSN=sql2000;SERVER=myserver;DATABASE=mydb;UID=myID;PWD=mypw;Trusted_Connection=No;",
_
SQLStatement:="SELECT * FROM myfunc(1)", _
Subtype:=wdMergeSubtypeWord2000

Or use

Connection:="DSN=sql2000;SERVER=myserver;DATABASE=mydb;Trusted_Connection=Yes;"

for a trusted connection.

I've tested the above. Experience suggests that if that works you can also
use a file dsn (which IMO is likely to be more easily distributed, like a
..odc) via

OpenDataSource _
Name:="pathname of the .dsn file", _
connection:="FILEDSN=pathnameof the .dsn file;additional parameters;", _
SQLStatement:="SELECT * FROM myfunc(1)"


Peter Jamieson

Hi Peter

OK, i now can use a statement with a where-clause for filtering. The next
thing i have to do is, call a table-valued function and give it an actual
value for a parameter.

SELECT * FROM MyFuction(58)

works from QueryAnalyzer and from .NET-application but not for Mail-Merge.
Do You know, how to do this??

Thanks

You can certainly pass an SQL SELECT statement with a WHERE clause.
Getting the syntax right can be difficult, as you have discovered. What I
find generally works is
a. quoting table and column names. I think it is clearer to use `` or []
rather than "" but it may be less standard these days
b. aliasing tables - even when aliasing should not be syntactiaclly
required - and always using the alias names when referencing individual
columns
c. using single quotes ' ' as text literal delimiters
e.g. (not tested - you'll need to play around to check)

SELECT [MV].* FROM [MyView] MV WHERE [MV].[MyColumn] = 'abc'

You may also be able to pass a call to a Transact-SQL procedure using the
old ODBC escape syntax which looks something like

{ call functionname(p1,p2) }

You may be able to find out more about that in SQL Server Books Online.
However, I have had a mixed experience using that approach in the past
because, for example, procedures that return multiple results do not seem
to work.

Peter Jamieson

i'm trying to make some MailMerge by automating word2003.
If i call MailMerge.OpenDataSource, what can i pass for the Satement
Parameter?
For Connectioninformation i'm using a *.odc file, wich connects to a
SQL-Server.
The name of the odc-file i pass as the Name parameter.

when I try
SELECT * FROM MyView
it fails.

SELECT * FROM "MyView"
succeeds.

Also passing the name of the view suceeds only with quotationmarks

What i'm trying to do is, passing a statement calling a table-valued
Function with parameters and/or a statement with a where clause
containing
Filterconditions.

Does anyone know, how to do this?

Thanks

 

[Christof Nordiek]

OK
i tried it with ODBC with dsn file. Looked good at first. But then i noticed
that all the textfields are empty.
When I try simple queries that work with odc file the result is the same:
textfields are empty.

Do You know, how to do this??

No, I haven't been able to find a way to use a table-valued function using
OLEDB. The only way I can get something similar to work is to use ODBC.
For example, if you have a user/system DSN called sql2000, in Word
2002/2003 the following works here with suitable values for server,
database and security values:

OpenDataSource _
Name:="", _

Connection:="DSN=sql2000;SERVER=myserver;DATABASE=mydb;UID=myID;PWD=mypw;Trusted_Connection=No;",
_
SQLStatement:="SELECT * FROM myfunc(1)", _
Subtype:=wdMergeSubtypeWord2000

Or use

Connection:="DSN=sql2000;SERVER=myserver;DATABASE=mydb;Trusted_Connection=Yes;"

for a trusted connection.

I've tested the above. Experience suggests that if that works you can also
use a file dsn (which IMO is likely to be more easily distributed, like a
.odc) via

OpenDataSource _
Name:="pathname of the .dsn file", _
connection:="FILEDSN=pathnameof the .dsn file;additional parameters;", _
SQLStatement:="SELECT * FROM myfunc(1)"


Peter Jamieson

Hi Peter

OK, i now can use a statement with a where-clause for filtering. The next
thing i have to do is, call a table-valued function and give it an actual
value for a parameter.

SELECT * FROM MyFuction(58)

works from QueryAnalyzer and from .NET-application but not for
Mail-Merge.
Do You know, how to do this??

Thanks

You can certainly pass an SQL SELECT statement with a WHERE clause.
Getting the syntax right can be difficult, as you have discovered. What
I find generally works is
a. quoting table and column names. I think it is clearer to use `` or []
rather than "" but it may be less standard these days
b. aliasing tables - even when aliasing should not be syntactiaclly
required - and always using the alias names when referencing individual
columns
c. using single quotes ' ' as text literal delimiters
e.g. (not tested - you'll need to play around to check)

SELECT [MV].* FROM [MyView] MV WHERE [MV].[MyColumn] = 'abc'

You may also be able to pass a call to a Transact-SQL procedure using
the old ODBC escape syntax which looks something like

{ call functionname(p1,p2) }

You may be able to find out more about that in SQL Server Books Online.
However, I have had a mixed experience using that approach in the past
because, for example, procedures that return multiple results do not
seem to work.

Peter Jamieson

i'm trying to make some MailMerge by automating word2003.
If i call MailMerge.OpenDataSource, what can i pass for the Satement
Parameter?
For Connectioninformation i'm using a *.odc file, wich connects to a
SQL-Server.
The name of the odc-file i pass as the Name parameter.

when I try
SELECT * FROM MyView
it fails.

SELECT * FROM "MyView"
succeeds.

Also passing the name of the view suceeds only with quotationmarks

What i'm trying to do is, passing a statement calling a table-valued
Function with parameters and/or a statement with a where clause
containing
Filterconditions.

Does anyone know, how to do this?

Thanks

 

[Peter Jamieson]

Another special Word "feature" :-(

If they are Unicode fields (NVARCHAR, NTEXT etc.) Word may not see the
contents (certainly with the ODBC route, but I thought it worked with
OLEDB). The best you can do in that case is use TransactSQL CAST/CONVERT to
return the VARCHAR, TEXT version. Unfortunately you may well be using
characters that are unavailable in the target character set.

Peter Jamieson

OK
i tried it with ODBC with dsn file. Looked good at first. But then i
noticed that all the textfields are empty.
When I try simple queries that work with odc file the result is the same:
textfields are empty.

Do You know, how to do this??

No, I haven't been able to find a way to use a table-valued function
using OLEDB. The only way I can get something similar to work is to use
ODBC. For example, if you have a user/system DSN called sql2000, in Word
2002/2003 the following works here with suitable values for server,
database and security values:

OpenDataSource _
Name:="", _

Connection:="DSN=sql2000;SERVER=myserver;DATABASE=mydb;UID=myID;PWD=mypw;Trusted_Connection=No;",
_
SQLStatement:="SELECT * FROM myfunc(1)", _
Subtype:=wdMergeSubtypeWord2000

Or use

Connection:="DSN=sql2000;SERVER=myserver;DATABASE=mydb;Trusted_Connection=Yes;"

for a trusted connection.

I've tested the above. Experience suggests that if that works you can
also use a file dsn (which IMO is likely to be more easily distributed,
like a .odc) via

OpenDataSource _
Name:="pathname of the .dsn file", _
connection:="FILEDSN=pathnameof the .dsn file;additional parameters;", _
SQLStatement:="SELECT * FROM myfunc(1)"


Peter Jamieson

Hi Peter

OK, i now can use a statement with a where-clause for filtering. The
next thing i have to do is, call a table-valued function and give it an
actual value for a parameter.

SELECT * FROM MyFuction(58)

works from QueryAnalyzer and from .NET-application but not for
Mail-Merge.
Do You know, how to do this??

Thanks

You can certainly pass an SQL SELECT statement with a WHERE clause.
Getting the syntax right can be difficult, as you have discovered. What
I find generally works is
a. quoting table and column names. I think it is clearer to use `` or
[] rather than "" but it may be less standard these days
b. aliasing tables - even when aliasing should not be syntactiaclly
required - and always using the alias names when referencing individual
columns
c. using single quotes ' ' as text literal delimiters
e.g. (not tested - you'll need to play around to check)

SELECT [MV].* FROM [MyView] MV WHERE [MV].[MyColumn] = 'abc'

You may also be able to pass a call to a Transact-SQL procedure using
the old ODBC escape syntax which looks something like

{ call functionname(p1,p2) }

You may be able to find out more about that in SQL Server Books Online.
However, I have had a mixed experience using that approach in the past
because, for example, procedures that return multiple results do not
seem to work.

Peter Jamieson

i'm trying to make some MailMerge by automating word2003.
If i call MailMerge.OpenDataSource, what can i pass for the Satement
Parameter?
For Connectioninformation i'm using a *.odc file, wich connects to a
SQL-Server.
The name of the odc-file i pass as the Name parameter.

when I try
SELECT * FROM MyView
it fails.

SELECT * FROM "MyView"
succeeds.

Also passing the name of the view suceeds only with quotationmarks

What i'm trying to do is, passing a statement calling a table-valued
Function with parameters and/or a statement with a where clause
containing
Filterconditions.

Does anyone know, how to do this?

Thanks