Word 2003 MailMerge with SQL Server 2000 problem

[crm]

I'm developing a Form Letter engine for my customers which pulls data
from their SQL Server 2000 database. The user clicks a button on the
toolbar which brings them to a data selection form. This form builds a
SQL Query behind the scenes based on their selections. I then Mail
Merge against the query. This works perfectly except when the filter
criteria on the query is a single date field.

Sample VBA Code:
Public Sub ExecuteMailMerge(query As String)

On Error GoTo err_handler

Dim dsn As String
dsn = "My DSN"

With ActiveDocument.MailMerge
.OpenDataSource dsn, LinkToSource:=False, _
SqlStatement:=query
.Execute False
End With

Exit Sub
err_handler:

MsgBox ("Failed to execute mail merge: " + Err.Description)

End Sub

Sample Query:
"SELECT * FROM [View Name] WHERE ( DateField >= '9/08/2005 12:00:00 AM'
)"

Sample Query 2:
"SELECT * FROM [View Name] WHERE ( DateField >= '9/08/2005' )"

When using a query involving a single date field, I get a "Failed to
execute mail merge: Word was unable to open the data source." error.

I have been able to hack my way past this problem by adding "AND 1=1"
to the end of the query. However, this is still a hack, and I hate
hacks. I'd much rather identify and solve the underlying problem. Any
ideas?
Thanks,
Chris McKenzie
http://weblogs.asp.net/taganov

 

[Peter Jamieson]

To be honest I'm amazed that your code would work with Word 2003 - if I use
an OpenDataSource call that specifies a dsn file but no connection string,
Word just thinks you're trying to open a text file.

Anyway, a few experiments here suggest that
a. if you are using ODBC to connect, you don't encounter this problem.
b. if you are using OLEDB to connect, you do encounter the problem you
describe. I have always found that the OLEDB provider seems to be more picky
about SQL syntax than the ODBC driver. It's possible that the ODBC driver is
translating the query (ODBC is certainly designed to do stuff like that) and
getting it right, and OLEDB is not, or it's possible that they are passing
different settings to SQL Server that cause Transact-SQL to behave
differently. I don't know.
c. What I generally find is that qualifying table names with an alias (and
possibly even /using/ the alias) makes OLEDB behave - e.g.

SELECT o.* FROM Orders o WHERE rderDate > ...

Personally I wouldn't use the 1=1 workaround on the grounds that the query
optimiser might not eliminate the expression (seriously! I don't know what
SQL Server does but I once did this with Oracle to get around some problem
and the query processor examined every candidate record, presumably to
ensure that 1 still equalled 1. Maybe that's gone now).

It's not part of your issue, but personally, I would also tend to use
'YYYY-MM-DD' format for dates, or even the official ISO one
(YYYY-MM-DDTHH:MM:SS, e.g. 2006-09-28T13:06:05 ) if it is supported in SQL
Server 2000, or do something like

myDate < CONVERT(DATETIME, '1996-07-06 00:00:00', 102)

(you need to look up CONVERT in SQL Server books online to get the right
number - instead of 102 - for the date format you want to use). Assuming
that's in SQL SERVER 2000.

Peter Jamieson

I'm developing a Form Letter engine for my customers which pulls data
from their SQL Server 2000 database. The user clicks a button on the
toolbar which brings them to a data selection form. This form builds a
SQL Query behind the scenes based on their selections. I then Mail
Merge against the query. This works perfectly except when the filter
criteria on the query is a single date field.

Sample VBA Code:
Public Sub ExecuteMailMerge(query As String)

On Error GoTo err_handler

Dim dsn As String
dsn = "My DSN"

With ActiveDocument.MailMerge
.OpenDataSource dsn, LinkToSource:=False, _
SqlStatement:=query
.Execute False
End With

Exit Sub
err_handler:

MsgBox ("Failed to execute mail merge: " + Err.Description)

End Sub

Sample Query:
"SELECT * FROM [View Name] WHERE ( DateField >= '9/08/2005 12:00:00 AM'
)"

Sample Query 2:
"SELECT * FROM [View Name] WHERE ( DateField >= '9/08/2005' )"

When using a query involving a single date field, I get a "Failed to
execute mail merge: Word was unable to open the data source." error.

I have been able to hack my way past this problem by adding "AND 1=1"
to the end of the query. However, this is still a hack, and I hate
hacks. I'd much rather identify and solve the underlying problem. Any
ideas?
Thanks,
Chris McKenzie
http://weblogs.asp.net/taganov

 

[crm]

Okay, I switched the way I was connecting, and started using a DSN
instead. However, I have been unable to use a DSN-Less connection
string for Mail Merge purposes, and I have been unable to
programatically create a SQL dsn that has UID and PWD information.

If I could either create a DSN-less connection string for Mail Merge,
or create a dsn during installation that contains all login
information, then I can call this project done.

Any ideas?
Thanks,
Chris McKenzie

Peter Jamieson wote:

To be honest I'm amazed that your code would work with Word 2003 - if I use
an OpenDataSource call that specifies a dsn file but no connection string,
Word just thinks you're trying to open a text file.

Anyway, a few experiments here suggest that
a. if you are using ODBC to connect, you don't encounter this problem.
b. if you are using OLEDB to connect, you do encounter the problem you
describe. I have always found that the OLEDB provider seems to be more picky
about SQL syntax than the ODBC driver. It's possible that the ODBC driver is
translating the query (ODBC is certainly designed to do stuff like that) and
getting it right, and OLEDB is not, or it's possible that they are passing
different settings to SQL Server that cause Transact-SQL to behave
differently. I don't know.
c. What I generally find is that qualifying table names with an alias (and
possibly even /using/ the alias) makes OLEDB behave - e.g.

SELECT o.* FROM Orders o WHERE rderDate > ...

Personally I wouldn't use the 1=1 workaround on the grounds that the query
optimiser might not eliminate the expression (seriously! I don't know what
SQL Server does but I once did this with Oracle to get around some problem
and the query processor examined every candidate record, presumably to
ensure that 1 still equalled 1. Maybe that's gone now).

It's not part of your issue, but personally, I would also tend to use
'YYYY-MM-DD' format for dates, or even the official ISO one
(YYYY-MM-DDTHH:MM:SS, e.g. 2006-09-28T13:06:05 ) if it is supported in SQL
Server 2000, or do something like

myDate < CONVERT(DATETIME, '1996-07-06 00:00:00', 102)

(you need to look up CONVERT in SQL Server books online to get the right
number - instead of 102 - for the date format you want to use). Assuming
that's in SQL SERVER 2000.

Peter Jamieson

I'm developing a Form Letter engine for my customers which pulls data
from their SQL Server 2000 database. The user clicks a button on the
toolbar which brings them to a data selection form. This form builds a
SQL Query behind the scenes based on their selections. I then Mail
Merge against the query. This works perfectly except when the filter
criteria on the query is a single date field.

Sample VBA Code:
Public Sub ExecuteMailMerge(query As String)

On Error GoTo err_handler

Dim dsn As String
dsn = "My DSN"

With ActiveDocument.MailMerge
.OpenDataSource dsn, LinkToSource:=False, _
SqlStatement:=query
.Execute False
End With

Exit Sub
err_handler:

MsgBox ("Failed to execute mail merge: " + Err.Description)

End Sub

Sample Query:
"SELECT * FROM [View Name] WHERE ( DateField >= '9/08/2005 12:00:00 AM'
)"

Sample Query 2:
"SELECT * FROM [View Name] WHERE ( DateField >= '9/08/2005' )"

When using a query involving a single date field, I get a "Failed to
execute mail merge: Word was unable to open the data source." error.

I have been able to hack my way past this problem by adding "AND 1=1"
to the end of the query. However, this is still a hack, and I hate
hacks. I'd much rather identify and solve the underlying problem. Any
ideas?
Thanks,
Chris McKenzie
http://weblogs.asp.net/taganov

 

[Peter Jamieson]

With Word and ODBC you have to have a DSN.

If you use a machine (user/system) DSN (called "mydsn", say), you need

OpenDataSource _
Name:="", _
Connection:="DSN=mydsn;all your other connection info;", _
SQLStatement:="SELECT whatever"

and in Word 2002/2003 you will probably also need a further parameter,

Subtype:=wdMergeSubtypeWord2000

although in recent versions of 2003 you seem to be able to avoid that

If you use a file dsn (say, called c:\a\mydsn.dsn) then you need

OpenDataSource _
Name:="c:\a\mydsn.dsn", _
Connection:="FILEDSN=c:\a\mydsn.dsn;all your other connection info;", _
SQLStatement:="SELECT whatever"

(with the Subtype parameter as necessary.

In either case, you can include login and password information in "all your
other connection info". If you are using SQL Server integrated security, you
shouldn't need specific info., but you will need the correct text in the
connection string. I don't have the exact value names to hand but if you
can't find them, let me know.

It is certainly possible to put login/password in a file .dsn because a file
..dsn is just a text file in much the same format as .ini files, e.g.

[odbc]
keyword1=value1
keyword2=value2

etc.

However, as far as I know, if you include the login/password strings, they
are in clear text, not encrypted. It may be possible to include the same
things in a machine dsn by using the appropriate API or directly adding
entries to the registry, but I don't know for sure.

Not sure that answers your question, exactly, but if not let me know what
you're still missing...

Peter Jamieson

Okay, I switched the way I was connecting, and started using a DSN
instead. However, I have been unable to use a DSN-Less connection
string for Mail Merge purposes, and I have been unable to
programatically create a SQL dsn that has UID and PWD information.

If I could either create a DSN-less connection string for Mail Merge,
or create a dsn during installation that contains all login
information, then I can call this project done.

Any ideas?
Thanks,
Chris McKenzie

Peter Jamieson wote:

To be honest I'm amazed that your code would work with Word 2003 - if I
use
an OpenDataSource call that specifies a dsn file but no connection
string,
Word just thinks you're trying to open a text file.

Anyway, a few experiments here suggest that
a. if you are using ODBC to connect, you don't encounter this problem.
b. if you are using OLEDB to connect, you do encounter the problem you
describe. I have always found that the OLEDB provider seems to be more
picky
about SQL syntax than the ODBC driver. It's possible that the ODBC driver
is
translating the query (ODBC is certainly designed to do stuff like that)
and
getting it right, and OLEDB is not, or it's possible that they are
passing
different settings to SQL Server that cause Transact-SQL to behave
differently. I don't know.
c. What I generally find is that qualifying table names with an alias
(and
possibly even /using/ the alias) makes OLEDB behave - e.g.

SELECT o.* FROM Orders o WHERE rderDate > ...

Personally I wouldn't use the 1=1 workaround on the grounds that the
query
optimiser might not eliminate the expression (seriously! I don't know
what
SQL Server does but I once did this with Oracle to get around some
problem
and the query processor examined every candidate record, presumably to
ensure that 1 still equalled 1. Maybe that's gone now).

It's not part of your issue, but personally, I would also tend to use
'YYYY-MM-DD' format for dates, or even the official ISO one
(YYYY-MM-DDTHH:MM:SS, e.g. 2006-09-28T13:06:05 ) if it is supported in
SQL
Server 2000, or do something like

myDate < CONVERT(DATETIME, '1996-07-06 00:00:00', 102)

(you need to look up CONVERT in SQL Server books online to get the right
number - instead of 102 - for the date format you want to use). Assuming
that's in SQL SERVER 2000.

Peter Jamieson

I'm developing a Form Letter engine for my customers which pulls data
from their SQL Server 2000 database. The user clicks a button on the
toolbar which brings them to a data selection form. This form builds a
SQL Query behind the scenes based on their selections. I then Mail
Merge against the query. This works perfectly except when the filter
criteria on the query is a single date field.

Sample VBA Code:
Public Sub ExecuteMailMerge(query As String)

On Error GoTo err_handler

Dim dsn As String
dsn = "My DSN"

With ActiveDocument.MailMerge
.OpenDataSource dsn, LinkToSource:=False, _
SqlStatement:=query
.Execute False
End With

Exit Sub
err_handler:

MsgBox ("Failed to execute mail merge: " + Err.Description)

End Sub

Sample Query:
"SELECT * FROM [View Name] WHERE ( DateField >= '9/08/2005 12:00:00 AM'
)"

Sample Query 2:
"SELECT * FROM [View Name] WHERE ( DateField >= '9/08/2005' )"

When using a query involving a single date field, I get a "Failed to
execute mail merge: Word was unable to open the data source." error.

I have been able to hack my way past this problem by adding "AND 1=1"
to the end of the query. However, this is still a hack, and I hate
hacks. I'd much rather identify and solve the underlying problem. Any
ideas?
Thanks,
Chris McKenzie
http://weblogs.asp.net/taganov