Sheesh, I wish my memory was better. I last had a look at most of this stuff
a couple of years ago now as I don't really need it for myself.
You could try using OLEDB to execute exactly the same SQL (since the SQL
Server OLE DB provider or the Transact-SQL interpreter, not sure which, also
recognises the ODBC escape syntax ({ exec } and so on).
The easiest way to do that would be to create a .odc in the usual way for
any table in the database you want to access, then change your
OpenDataSource to be more like
OpenDataSource _
Name:="c:\your data source pathname\theodc.odc",
SQLStatement:= "{ call ASDB.dbo.spGetListContacts('Year 3') }"
You may also be able to dispense with the { exec } stuff and use something
like
OpenDataSource _
Name:="c:\your data source pathname\theodc.odc",
SQLStatement:= "ASDB.dbo.spGetListContacts 'Year 3'"
You shouldn't need the connection parameter since Word gets it from the
..odc. You may be able to simplify this further by printing the content of
Activedocument.Mailmerge.Datasource.ConnectString (or adapting it from the
connection string from the .odc), putting it in the Connection string in the
OpenDataSource call (with any necessary syntax modifications), and deleting
all the text from the .odc. That has the benefit that you can use a single
empty .odc file for all your connections and just put the connection and sql
stuff in the OpenDataSource.
I dug out my notes from when I last looked at a specific problem in this
area in the Word XP days. I summarised as follows:
1. Looking at the ODBC log files suggests that
- Word XP is making a lot more ODBC requests (to do with configuration
info, cursor types etc.) than Word 2000 does
- Word XP is using the Unicode versions of ODBC calls (e.g.
SQLExecDirectW rather than SQLExecDirect)
- Word XP fails when it makes its SQLExecDirectW call to execute (say)
"exec proc_test". The error returned is 01S02 ("option value changed") and
is specifically "Cursor type changed".
2. When executing multiple SELECT statements, TRANSACT-SQL does in fact
return multiple result sets - enough to confuse software such as MS Query
that is only equipped to deal with one. AFAICS MS Query always returns the
first result set.
3. I have tried two versions of the ODBC drivers (in essence, the version
that comes with Win2K, and the version that comes with MDAC2.6). This
supports the view that it is Word XP, not ODBC itself, that is the origin
of this problem.
4. The Word XP ODBC log file is 84Kb. The Word 2000 log file is 27Kb,
i.e. roughly speaking, XP is making about 3 times as many calls (probably
about 4 calls per 1Kb, i.e. a hundred or so more calls).
5. It isn't clear to me whether the differing result of the SQLExecDirect
call is purely to do with the use of the Unicode rather than the
non-Unicode version, or whether it is to do with different settings in
force or different parameters in the call. My current /guess/ is that it
results from Word XP explicitly setting up various cursor attributes, or
asking to be informed of such a change. It is also my /guess/ that WOrd
does not strictly need to do this and/or should be treating this error as
a warning and ignoring it.
6. Assuming that there is no way to stop Word XP from doing whatever /it/
does, the only "solution" would seem to be to change the way that the
SQL-TRANSACT statements behave, perhaps by explicitly declaring the CURSOR
characteristics. I haven't gone through all the possibilities yet, but so
far have failed to find any combination that makes any difference - Word
2000 just works, and Word XP just doesn't.
Maybe something in there will give you some clues
--
Peter Jamieson
Michael said:
I think you cracked it!
Simple stored procedures work with or without parameters. The procedure I
need to call is quite complicated and creates two temporary tables before
doing a select on one of them to return the results.
Back to the drawing board I guess.
Many thanks for taking the time to look at this. If you have any other
insights as to why Word can't cope with this I'd be very interested to hear.
Michael
Peter Jamieson said:
When MSQuery returns data to Word does it return the recordset or the
connection details and SQLStatement for Word to rerun the query?
As far as I am aware, the latter.
At the moment I can't think of any reason why this would not work except
a. I've noticed prtoblems in the past when Word tries to execute procedures
that create more than one result table. It's a while since I looked at this
so I can't remember the details. I would certainly try creating a really
simple test procedure if that is not what you are already doing.
b. security, as suggested. But if you are using Integrated Security and the
DSN is set up to say that, you should be OK, assuming of couse that your
login has the necessary permissions.
Do parameterless procedures work?
--
Peter Jamieson
Michael said:
Hi Peter,
The call works in MSQuery but won't return the data to Word.
I'm using Word 2003.
I start Word, click OpenDataSource on the MailMerge toolbar and then select
MSQuery from the dropdown Tools button. When MSQuery starts, the 'Choose
Data Source' dialog appears and I select ASDBdsn. I close the 'Add Tables'
dialog, then click the SQL button and enter the stored procedure call.
MSQuery qives trhe warning "SQL Query can't be represented
graphically",
the
call succeeds and the records are displayed. I then select
File->Return
Data
to Microsoft Word, MSQuery closes but Word gives the error message:
"Word
was
unable to open the data source".
The behaviour is identical if I use :-
exec ASDB.dbo.spGetListContacts 'Year 3'
When MSQuery returns data to Word does it return the recordset or the
connection details and SQLStatement for Word to rerun the query?
Michael
:
A call with the same syntax works OK here (Word 2002 SP2 and Word
2003).
At
the moment I can't easily test with Word 2000
Could there be a security problem - e.g. on your Word workstation do you
have the necessary permissions for the prcedure as well as
ListContacts
and
ListName?
What happens if you issue the same syntax in MS Query (as opposed to Query
Analyzer) on the workstation where you are running Word - create a query,
click the SQL button and replace whatever SQL is there by
{ call ASDB.dbo.spGetListContacts ('Year 3') }
--
Peter Jamieson
This works :-
OpenDataSource _
Name:="", Connection:= "DSN=ASDBdsn;" _
SQLStatement:= "SELECT * FROM [ListContacts] WHERE [ListName] = 'Year
3'"
This doesn't work :-
OpenDataSource _
Name:="", Connection:= "DSN=ASDBdsn;" _
SQLStatement:= "{ call ASDB.dbo.spGetListContacts ('Year 3') }"
The stored procedure works fine in Query Analyzer.
Any ideas what could be wrong?
Thanks
Michael
