It seems really random what I can get to work and what I can't.
Yes, it does.
Well, just some thoughts...
Just out of interest, does either of the following variations of (3)
work? (some of the characters may need to be moved from strSQL2 to strSQL1)
strSQL1 = "SELECT CONVERT(VARCHAR(12),PermitDate,107) AS
PermitDate,CONVERT(VARCHAR(12),DateAdd(yyyy, 2, PermitDate),107) AS
Expiry, "
strSQL2 = "CONVERT(VARCHAR(12),GETDATE(),107) AS Today,'250-492-0237' AS
Phone, p.EArea FROM [Permits] p WHERE Permit0 AND Active=1 AND
FarmStatus='N' AND PermitDateGetDate()-670 AND PermitDateGetDate()-730
AND EArea='"& sEA& "'"
strSQL1 = "SELECT CONVERT(VARCHAR(12),PermitDate,107) AS
PermitDate,CONVERT(VARCHAR(12),DateAdd(yyyy, 2, PermitDate),107) AS
Expiry, "
strSQL2 = "CONVERT(VARCHAR(12),GETDATE(),107) AS Today,'250-492-0237' AS
Phone, EArea FROM [Permits] p WHERE Permit0 AND Active=1 AND
FarmStatus='N' AND PermitDateGetDate()-670 AND PermitDateGetDate()-730
AND EArea='"& sEA& "'"
Is there any way of determining how the ODSO is parsing the SQL
statement?
I would probably try to find the appropriate SQL logging mechanism to
discover the command that is actually being sent to SQL Server. I
suspect that you can only do that on the server side, but it may be
possible to set up the OLE DB provider to do it.
ODSO seems to be a classic example of a hopelessly naive object-oriented
approach to building SQL queries. It's only really intended to build the
kind of simple query that you can create in Word's data source filtering
panel, and it can't even get that right. If you have a look at its
documentation - around
http://msdn.microsoft.com/en-us/libr...ffice.10).aspx
you may get a clue as to what I mean. Precisely what ODSO does with more
complex queries I cannot tell (perhaps ODS is actually "innocent" and
the fault lies elsewhere), but I suppose another way to find out what it
might do with a query would be to write some VBA to instantiate an ODSO
object directly and play around with it.
As for what you do, I suppose your best bet would probably be to try to
get your queries implemented on the SQL Server system as views, except
for the pesky problem that you have stuff such as EArea='"& sEA& "'",
which mean you'd have to opt for a stored procedure or function instead.
And that's a problem because AFAIK
a. there is no way to get VBA OpenDataSource to return the results of
a stored procedure (because of a problem with multiple result sets.
Although again, it may just be a problem I haven't been able to solve
since Word 2000/ODBC when you could in fact do this)
b. you have to use a stored table-valued function, which AFAICR you
can only do in the more recent versions of SQL Server. I did have some
success with that when I last tried, but that was a while back. Suppose
for example you create a function called mytablefunction, with a single
parameter, you call it using
SELECT * FROM mytablefunction(parameter)
or perhaps
SELECT f.* FROM mytablefunction(parameter) f
Otherwise, I guess you have to get the data separately, e.g. using VBA
and ADO , and either "roll your own" merge, or perhaps stuff it into a
database for which you have write permission (perhaps another SQL
database, or even a local Jet .mdb), and use that as the source for the
merge.
Peter Jamieson
http://tips.pjmsn.me.uk
On 19/02/2010 17:49, Rowena wrote:
Thanks for all your help previously.
Is there any way of determining how the ODSO is parsing the SQL statement?
It seems really random what I can get to work and what I can't.
For example
1.) This works:
strSQL1 = "SELECT CONVERT(VARCHAR(12),PermitDate,107) AS
PermitDate,CONVERT(VARCHAR(12),DateAdd(yyyy, 2, PermitDate),107) AS Expiry, "
strSQL2 = "CONVERT(VARCHAR(12),GETDATE(),107) AS Today,'250-492-0237' AS
Phone FROM [Permits] p WHERE Permit0 AND Active=1 AND FarmStatus='N' AND
PermitDateGetDate()-670 AND PermitDateGetDate()-730 AND EArea='"& sEA& "'"
2.) And this works:
strSQL1 = "SELECT EArea "
strSQL2 = "FROM [Permits] p WHERE Permit0 AND Active=1 AND FarmStatus='N'
AND PermitDateGetDate()-670 AND PermitDateGetDate()-730 AND EArea='"& sEA
& "'"
3.) But this doesn't work:
strSQL1 = "SELECT p.EArea, CONVERT(VARCHAR(12),PermitDate,107) AS
PermitDate,CONVERT(VARCHAR(12),DateAdd(yyyy, 2, PermitDate),107) AS Expiry, "
strSQL2 = "CONVERT(VARCHAR(12),GETDATE(),107) AS Today,'250-492-0237' AS
Phone FROM [Permits] p WHERE Permit0 AND Active=1 AND FarmStatus='N' AND
PermitDateGetDate()-670 AND PermitDateGetDate()-730 AND EArea='"& sEA& "'"
No ammount of adding square brackets or table prefixes will get it to work.
Do you have any suggestions for debugging besides trial and error? Are there
any other solutions to what I am doing?
Thanks for all your help.
Rowena
"Peter Jamieson" wrote:
Here, using a similar construct (actually using the Invoices table in
NorthwindSQL) things are OK, although I do need to prefix at least the
"*" by the table name or a table alias name to get the other fields
(otherwise, I just get the fields in the Invoices table).
Here, my date column is called Invoice Date, and my database happens to
be SQL Server 2008 (I get the same results in SQL Server 2005). So I
don't know what is going wrong with yours. Could there be a difference
in the data type of the column (mine is defined as
[Invoice Date] [datetime] NULL,
in the CREATE TABLE code, so it seems pretty standard to me.
A possible problem is that the "Office Data Source Object" (ODSO) that
Word uses to get the data from OLE DB data sources seems to make a bunch
of invalid assumptions about the code that can be handed to it, and
parses the SQL very badly. So for example if you use
"SELECT *, CONVERT(VARCHAR(12),PermitDate,107) AS Expiry,
CONVERT(VARCHAR(12),GETDATE(),107) AS Today
FROM permits"
in Word 2007 the chances are that ODSO thinks you're trying to use a
table called "ermit", i.e. you have to do something like
"SELECT *, CONVERT(VARCHAR(12),PermitDate,107) AS Expiry,
CONVERT(VARCHAR(12),GETDATE(),107) AS Today
FROM ""permits"""
or
"SELECT *, CONVERT(VARCHAR(12),PermitDate,107) AS Expiry,
CONVERT(VARCHAR(12),GETDATE(),107) AS Today
FROM [permits]"
I found I had to do at least
"SELECT permits.*, CONVERT(VARCHAR(12),PermitDate,107) AS Expiry,
CONVERT(VARCHAR(12),GETDATE(),107) AS Today
FROM ""permits"""
or
"SELECT p.*, CONVERT(VARCHAR(12),PermitDate,107) AS Expiry,
CONVERT(VARCHAR(12),GETDATE(),107) AS Today
FROM ""permits"" p"
Beyond that, I don't know what to suggest.
Peter Jamieson
http://tips.pjmsn.me.uk
On 22/01/2010 00:36, Rowena wrote:
So I have progressed but now am experiencing a new problem.
My sql is querying some dates.
I have a query that returns 4 records. Both queries work correctly just
querying the database but when I try to try to run the second query I get a
"Word was unable to open the data source" error.
This works:
SELECT *, DateAdd(yyyy, 2, PermitDate) AS Expiry,
CONVERT(VARCHAR(12),GETDATE(),107) AS Today
This does not:
SELECT *, CONVERT(VARCHAR(12),PermitDate,107) AS Expiry,
CONVERT(VARCHAR(12),GETDATE(),107) AS Today
Any ideas?
Thanks for your help.
Rowena
"Peter Jamieson" wrote:
It's probably because some of the fields are Unicode and some are not,
and you won't see the Unicode ones via ODBC.
MS Word 2008
I assume you mean Word 2007 - especially with the .docx reference in
there - but as long as you mean any Windows version of Word from 2002
onwards, you may be able to fix this by changing the connection type to
OLE DB. However,
a. that will require that you create and distribute either a .udl file
containing the connection information, or a .odc file. Of the two, .odc
is probably the better choice as .udl seems not to work so well these
days, if at all.
b. In recent versions of Word, I have only ever been able to get this
to work when you are using built-in (Windows) authentication where you
do not have to provide a username and password in the connection string.
I've never managed to do it using "SQL Server" authentication.
You can either create a .odc by going through the process of connecting
to an existing data source, then using the New Source button (Word 2007)
- it should be reasonably obvious after that as long as you are using
the "old" SQL Server OLE DB Provider (SQLOLEDB), or you can (usually)
create an empty one (e.g. create an empty Notepad file and save it as
empty.odc) and put the connection information in the Connection
parameter of the OpenDataSource method. That has the advantage that you
only ever need to distribute one .odc for all your SQL Server merges.
For example, if your SQL Server machine is called "myserver", your
database is called "Permits, and you have an empty .odc called
c:\myodcs\empty.odc
then the following should work if you are using the /old/ SQL Server OLE
DB provider
Dim strConnect as String
strConnect= _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;"& _
"Persist Security Info=True;Initial Catalog=Permits;"& _
"Data Source=myserver;"
wrdDoc.MailMerge.OpenDataSource _
Name:="c:\myodcs\empty.odc", _
Connection:=strConnect, _
SQLStatement:=strSQL1, _
SQLStatement1:=strSQL2)
You may not need "Persist Security Info=True"
You /may/ also need to change the SQL statement either to specify the
table for each field (mytable.myfield etc.) or to use a table alias that
you then use for each field. (In theory you should not have to, but in
some cases Word seems to insist on it).
You have to pare the connection string down because Word only lets you
specify a string up to 255 characters, and connection strings are
typically much longer.
If you are using the /new/ SQL Server provider ("SQL Native Client"), to
use an empty .odc you would probably only have to change the Provider
name, e.g.
strConnect= _
"Provider=SQLNCLI.1;Integrated Security=SSPI;"& _
"Persist Security Info=True;Initial Catalog=Permits;"& _
"Data Source=myserver;"
To use a non-empty .odc you would need to go through a different path
after clicking "New Source - instead of "Microsoft SQL Server", choose
"Other/Advanced" then "SQL Native Client". In "Data Source", put the
server computer name, then select a database in the drop down at the
bottom. Then follow the process through. Or, you can use an empty .odc
with a connect string like
Your Database Administrator should be able to tell you which provider
you need to use. Security on SQL Server 2008 can be set up differently
for each of the two providers so you may find that one of them does not
work at all. Also, the provider has to be present on the client machine
(i.e. you need to know which providers have actually been distributed).
Peter Jamieson
http://tips.pjmsn.me.uk
On 19/01/2010 00:01, Rowena wrote:
Hi,
I am automating a mail merge from VS2008 using an exising MS Word 2008
document. The merge seems to work. The query returns the correct number of
records and some of the fields are populated but not others. For the life of
me I can't figure out why some fields aren't populated. The dates and numeric
values work, but none of the text fields are populated.
Thanks!
Dim strSQL1, strSQL2 As String
strSQL1 = ""
strSQL2 = ""
strSQL1 = "SELECT [Permit], [PermitDate], DateAdd(yyyy, 2,
[PermitDate]) AS [ExpiryDate], [ExtDate], [Description], [Lot],
[DistrictLot], [Plan], [Address], [Inspector], '250-492-0237' AS
[InspectorPhoneNumber], "
strSQL2 = "[Name], [Maddress], [MCity], [Mpostal], [Earea], [Folio],
GetDate() AS [TodaysDate] FROM [permits] WHERE [Permit]0 AND [Active]=1 AND
[FarmStatus]='N' AND [PermitDate]GetDate()-670 AND
[PermitDate]GetDate()-730"
' Create an instance of Word and make it visible.
Dim wrdApp As New Word.Application
wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
' Open document.
Dim wrdDoc As Word.Document
wrdDoc = wrdApp.Documents.Open("warning.docx")
wrdDoc.MailMerge.OpenDataSource(Name:="",
Connection:="DSN=Permits;DATABASE=Permits;uid=uid; pwd=pwd;",
SQLStatement:=strSQL1, SQLStatement1:=strSQL2)
wrdDoc.MailMerge.Execute()
' Release References.
wrdDoc = Nothing
wrdApp = Nothing
.
.