Statement parameter in Mailmerge.OpenDataSource

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Christof Nordiek - 25 Apr 2005 09:22 GMT

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

Reply to this Message

Peter Jamieson - 25 Apr 2005 12:36 GMT

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
[quoted text clipped - 19 lines]
>
> Thanks

Reply to this Message

Peter Jamieson - 25 Apr 2005 12:41 GMT

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.

> 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
[quoted text clipped - 45 lines]
>>
>> Thanks

Reply to this Message

Christof Nordiek - 25 Apr 2005 13:54 GMT

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
[quoted text clipped - 45 lines]
>>
>> Thanks

Reply to this Message

Peter Jamieson - 25 Apr 2005 18:20 GMT

> 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
>
[quoted text clipped - 58 lines]
>>>
>>> Thanks

Reply to this Message

Christof Nordiek - 26 Apr 2005 09:19 GMT

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??
>
[quoted text clipped - 92 lines]
>>>>
>>>> Thanks

Reply to this Message

Peter Jamieson - 26 Apr 2005 09:31 GMT

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
[quoted text clipped - 98 lines]
>>>>>
>>>>> Thanks

Reply to this Message