P
Peter Jamieson
Is "SQ" "SQL Server" ?
If so, in my experience
a. you have to use ODBC, not OLEDB
b. you either have to set up the query using MS Query or directly using VBA
and an OpenDataSource method call
c. you use the special ODBC "escape syntax" to invoke the procedure. So,
for example, if you have a stored procedure called myproc in the pubs
database and owned/created by dbo, you would typically need
{ call pubs.dbo.myproc }
For parameters, I think you use
{ call pubs.dbo.myproc(p1,p2) }
How do you set all that up?
Well, however you do it, you need to create a suitable ODBC DSN to access
your SQL Server database. You may have one already, but otherwise you can
either do it using the ODBC Administrator or within MS Query (I won't
explain how here).
Then, from Word's Open/Select Data Source dialog, you need to get to MS
query. In Word 2000 and earlier, there's a button. In Word 2002/2003 the MS
Query option is on the Tools menu in the to pright of the dialog box. That
assumes MS Query was installed on your system during Word/Office setup or
otherwise.
Once you're in MS Query, you need to get tot he point where some query
results are displayed in the results pane of the graphical query design
window. I won't describe the whole process here but you won't be able to
pick your stored procedure as the data source - choose a table or view
(prefereably a small table or fast-executing view) first, and opt to edit
the query rather than return the results to Word. The objective is to get to
the point where the query designer is available and the SQL button is
enabled.
Then click the SQL button. You can now type whatever SQL you want. Enter
{ call pubs.dbo.myproc }
in the box and proceed. MS Query will probably complain that it can't
represent the query graphically. Continue anyway. The design pane of the
window should disappear and you should see some new results in the data
display area. Use File|Return Data to Microsoft Word. Word should recognise
the column names in the procedure results as usual, but you won't be able to
filter or sort the results using Query Options or the "advanced" facilities
in Edit Recipients, or even in MS Query.
Notice that MS Query is only used to set up the query - you don't actualy
need it to refresh the results in Word.
It may be easier to set up the query in a VBA OpenDataSource call, which
would need to look something like this:
For a trusted connection with a machine (System/User) DSN:
ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection:= "DSN=mysqlserverdsn;", _
SQLStatement:= "{ call pubs.dbo.myproc }"
For a non-trusted connection with a machine (System/User) DSN:
ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection:= "DSN=mysqlserverdsn;" & _
"UID=myid;PWD=mypassword;Trusted_Connection=No", _
SQLStatement:= "{ call pubs.dbo.myproc }"
If you need to use parameters in the call to the procedure, you'll probably
need to quote text strings in single quotes (can't remember exactly how, and
whether in fact there are other problems, off the top of my head)
You can also use EXECUTE pubs.dbo.myproc, but as far as I can tell the {
call } syntax is preferred. If you look up "escape" in SQL Books online,
you'll find out more about this, at least from the SQL Server perspective.
[/QUOTE]
If so, in my experience
a. you have to use ODBC, not OLEDB
b. you either have to set up the query using MS Query or directly using VBA
and an OpenDataSource method call
c. you use the special ODBC "escape syntax" to invoke the procedure. So,
for example, if you have a stored procedure called myproc in the pubs
database and owned/created by dbo, you would typically need
{ call pubs.dbo.myproc }
For parameters, I think you use
{ call pubs.dbo.myproc(p1,p2) }
How do you set all that up?
Well, however you do it, you need to create a suitable ODBC DSN to access
your SQL Server database. You may have one already, but otherwise you can
either do it using the ODBC Administrator or within MS Query (I won't
explain how here).
Then, from Word's Open/Select Data Source dialog, you need to get to MS
query. In Word 2000 and earlier, there's a button. In Word 2002/2003 the MS
Query option is on the Tools menu in the to pright of the dialog box. That
assumes MS Query was installed on your system during Word/Office setup or
otherwise.
Once you're in MS Query, you need to get tot he point where some query
results are displayed in the results pane of the graphical query design
window. I won't describe the whole process here but you won't be able to
pick your stored procedure as the data source - choose a table or view
(prefereably a small table or fast-executing view) first, and opt to edit
the query rather than return the results to Word. The objective is to get to
the point where the query designer is available and the SQL button is
enabled.
Then click the SQL button. You can now type whatever SQL you want. Enter
{ call pubs.dbo.myproc }
in the box and proceed. MS Query will probably complain that it can't
represent the query graphically. Continue anyway. The design pane of the
window should disappear and you should see some new results in the data
display area. Use File|Return Data to Microsoft Word. Word should recognise
the column names in the procedure results as usual, but you won't be able to
filter or sort the results using Query Options or the "advanced" facilities
in Edit Recipients, or even in MS Query.
Notice that MS Query is only used to set up the query - you don't actualy
need it to refresh the results in Word.
It may be easier to set up the query in a VBA OpenDataSource call, which
would need to look something like this:
For a trusted connection with a machine (System/User) DSN:
ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection:= "DSN=mysqlserverdsn;", _
SQLStatement:= "{ call pubs.dbo.myproc }"
For a non-trusted connection with a machine (System/User) DSN:
ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection:= "DSN=mysqlserverdsn;" & _
"UID=myid;PWD=mypassword;Trusted_Connection=No", _
SQLStatement:= "{ call pubs.dbo.myproc }"
If you need to use parameters in the call to the procedure, you'll probably
need to quote text strings in single quotes (can't remember exactly how, and
whether in fact there are other problems, off the top of my head)
You can also use EXECUTE pubs.dbo.myproc, but as far as I can tell the {
call } syntax is preferred. If you look up "escape" in SQL Books online,
you'll find out more about this, at least from the SQL Server perspective.
[/QUOTE]