Populating mail merge doc with data from MSsql 2K table

(Win2Kpro,Office 2003, MSSql 2000)
How can I (step by step from within MSWord) use a query of data in a large
SQL2000 db table to merge into an MSword doc?
If it needs MS Query - where can I download it from?

tia

Hi Sydney,

(Win2Kpro,Office 2003, MSSql 2000)
How can I (step by step from within MSWord) use a query of data in a large
SQL2000 db table to merge into an MSword doc?
If it needs MS Query - where can I download it from?

You wouldn't need to d/l MS Query, it's part of the Office 2003 package. And
if it's already installed, you'd find it in the list under the button on the
far right in the Open Data Source dialog box.

Office 2003 should be able to link directly to SQL Server, however. In the
Open Data Source dialog box click the "New Source" button.
- Select SQL Server from the list
- Enter the Server name in the following dialog box, and choose the
security/login type you want/need to use. NEXT
- Select the database, then the table/stored proc
- Give it a "friendly name" and description (because you'll be able to
re-use it from "My data sources" without having to go through all these
steps, again)

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)

MS Query is part of Office so you should have it already. However, it may
not have been installed depending on what you chose during SETUP, so you may
need to go to Control Panel to sort that out. It is also rather hard to find
the MS Query option in Word - when you get to the Select Data Source dialog
box, look for the Tools menu in the top right - MS Query should be at the
bottom of that.

However, you should only need MS Query if you need to create a query to
filter and/or sort the data in some way that cannot be done in the Advanced
filter/sort dialog in Word. If you already have a table or view in SQL
Server that gives you exactly the data you need, you can connect to that
without using MS Query.

Assuming you do not need MS Query, you can make the connection as follows:
a. Choose Word Tools|Letters and Mailings|Mail Merge... Word displays the
Mail Merge Wizard, Step 1.
b. Select the document type (say Letters) and click Next to get to Step 2
c. Leave "Use the current document" selected and click Next to get to Step
3
d. Leave "Use an existing list" selected and click Next. The Select Data
Source Dialog box should be displayed. If it opens with the default folder
showing (My Data Sources) you should see an item called "+New SQL Server
Connection.odc". If not, navigate to My Data Sources. If you still don't see
the item, jump to point (m) below. If you see it, select it and click the
Open button.
e. You may see a dialog box saying "Confirm Data Source", with an entry
"OLE DB Database files" selected. If so, click OK. This may pop up again
later - if so, clcik OK.
f. You should now see a "Data Connection Wizard" prompting for the server
name. Enter the name of the Windows computer that is running SQL Server
(e.g. myserver). If you have a complex multi-instance SQL Server environment
this may not be enough and I don't know what you put there, so let's hope
you haven't...
g. Either accept "Use Windows authentication" or click the other button and
enter your SQL Server user name and password, depending on how
authentication is managed by your SQL Server server. If you don't know, you
will need to ask your SQL Server administrator, but in essence, with
"Windows Authentication" your permissions in SQL Server are associated with
your Windows networking logon, so you do not (usually) need separate logon
credentials. Click Next
h. You should now see a list of databases available on the server, and
tables within the currently selected database. Select the database, if
necessary check "Connect to a specific table", then select the table, and
click Next.
i. In the next dialog, you are asked for a file name for the connection
info. you have just entered. The file created will be an "Office Database
Connection" (.odc) file, which is an HTML file with some XML in it. if you
selected Windows Authentication in step (g), the option "Save password in
file" should be greyed out. If not, only check it if you do not mind having
your username and password stored in the .odc file in plain view. Click
Finish.
j. Word should now create the .odc file and select it as the data source.
If you selected Windows Authentication, or opted to store the password in
the .odc file, you should see the "Mail Merge Recipients" dialog box. If so,
you can now click OK and continue setting up your merge document. I would
check fairly early on that when you close and re-open your document, Word
correctly reconnects to the SQL Server table/view.
Otherwise, if you did not sleect Windows Authentication and you did not opt
to store the password, you will probably see a standard OLEDB "DataLink
Properties dialog box. You will need to provide the password here, and you
will also need to check the "allow saving password" box. Note that although
this suggests that the password will now be stored in the Word document, if
you save and close the document and re-open it, the DataLink Properties
dialog box should be redisplayed and you will have to re-enter the password.

If you now create a new mail merge main document and select the recently
created .odc file as your data source, you should only see the Datalink
Properties dialog box if you did not select Windows Authentication, you did
not opt to store the password, or (maybe) if the login/password is no longer
adequate.

If you do not check "Connect to a specific table" in step (h), you will be
asked for the table in step (j) just after Word has selected the .odc as the
source.

If you need to use MS Query, the process is rather different, but perhaps
you can let us know if that is the case.

--
Peter Jamieson

"Sydney Lotterby" wrote in message
...
(Win2Kpro,Office 2003, MSSql 2000)
How can I (step by step from within MSWord) use a query of data in a large
SQL2000 db table to merge into an MSword doc?
If it needs MS Query - where can I download it from?

tia

Thanks Cindy.

"Cindy M -WordMVP-" wrote in message
news:VA.000097db.00fb9a47@speedy...
Hi Sydney,

(Win2Kpro,Office 2003, MSSql 2000)
How can I (step by step from within MSWord) use a query of data in a
large
SQL2000 db table to merge into an MSword doc?
If it needs MS Query - where can I download it from?

You wouldn't need to d/l MS Query, it's part of the Office 2003 package.
And
if it's already installed, you'd find it in the list under the button on
the
far right in the Open Data Source dialog box.

Office 2003 should be able to link directly to SQL Server, however. In the
Open Data Source dialog box click the "New Source" button.
- Select SQL Server from the list
- Enter the Server name in the following dialog box, and choose the
security/login type you want/need to use. NEXT
- Select the database, then the table/stored proc
- Give it a "friendly name" and description (because you'll be able to
re-use it from "My data sources" without having to go through all these
steps, again)

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)

Thank you Peter. You spent a lot of time spelling it out for me - I
appreciate that v. much.
Since I have a View for the data I need, it was very easy.

"Peter Jamieson" wrote in message
...
MS Query is part of Office so you should have it already. However, it may
not have been installed depending on what you chose during SETUP, so you
may
need to go to Control Panel to sort that out. It is also rather hard to
find
the MS Query option in Word - when you get to the Select Data Source
dialog
box, look for the Tools menu in the top right - MS Query should be at the
bottom of that.

However, you should only need MS Query if you need to create a query to
filter and/or sort the data in some way that cannot be done in the
Advanced
filter/sort dialog in Word. If you already have a table or view in SQL
Server that gives you exactly the data you need, you can connect to that
without using MS Query.

Assuming you do not need MS Query, you can make the connection as follows:
a. Choose Word Tools|Letters and Mailings|Mail Merge... Word displays the
Mail Merge Wizard, Step 1.
b. Select the document type (say Letters) and click Next to get to Step 2
c. Leave "Use the current document" selected and click Next to get to
Step
3
d. Leave "Use an existing list" selected and click Next. The Select Data
Source Dialog box should be displayed. If it opens with the default folder
showing (My Data Sources) you should see an item called "+New SQL Server
Connection.odc". If not, navigate to My Data Sources. If you still don't
see
the item, jump to point (m) below. If you see it, select it and click the
Open button.
e. You may see a dialog box saying "Confirm Data Source", with an entry
"OLE DB Database files" selected. If so, click OK. This may pop up again
later - if so, clcik OK.
f. You should now see a "Data Connection Wizard" prompting for the server
name. Enter the name of the Windows computer that is running SQL Server
(e.g. myserver). If you have a complex multi-instance SQL Server
environment
this may not be enough and I don't know what you put there, so let's hope
you haven't...
g. Either accept "Use Windows authentication" or click the other button
and
enter your SQL Server user name and password, depending on how
authentication is managed by your SQL Server server. If you don't know,
you
will need to ask your SQL Server administrator, but in essence, with
"Windows Authentication" your permissions in SQL Server are associated
with
your Windows networking logon, so you do not (usually) need separate logon
credentials. Click Next
h. You should now see a list of databases available on the server, and
tables within the currently selected database. Select the database, if
necessary check "Connect to a specific table", then select the table, and
click Next.
i. In the next dialog, you are asked for a file name for the connection
info. you have just entered. The file created will be an "Office Database
Connection" (.odc) file, which is an HTML file with some XML in it. if you
selected Windows Authentication in step (g), the option "Save password in
file" should be greyed out. If not, only check it if you do not mind
having
your username and password stored in the .odc file in plain view. Click
Finish.
j. Word should now create the .odc file and select it as the data source.
If you selected Windows Authentication, or opted to store the password in
the .odc file, you should see the "Mail Merge Recipients" dialog box. If
so,
you can now click OK and continue setting up your merge document. I would
check fairly early on that when you close and re-open your document, Word
correctly reconnects to the SQL Server table/view.
Otherwise, if you did not sleect Windows Authentication and you did not
opt
to store the password, you will probably see a standard OLEDB "DataLink
Properties dialog box. You will need to provide the password here, and you
will also need to check the "allow saving password" box. Note that
although
this suggests that the password will now be stored in the Word document,
if
you save and close the document and re-open it, the DataLink Properties
dialog box should be redisplayed and you will have to re-enter the
password.

If you now create a new mail merge main document and select the recently
created .odc file as your data source, you should only see the Datalink
Properties dialog box if you did not select Windows Authentication, you
did
not opt to store the password, or (maybe) if the login/password is no
longer
adequate.

If you do not check "Connect to a specific table" in step (h), you will be
asked for the table in step (j) just after Word has selected the .odc as
the
source.

If you need to use MS Query, the process is rather different, but perhaps
you can let us know if that is the case.

--
Peter Jamieson

"Sydney Lotterby" wrote in message
...
(Win2Kpro,Office 2003, MSSql 2000)
How can I (step by step from within MSWord) use a query of data in a
large
SQL2000 db table to merge into an MSword doc?
If it needs MS Query - where can I download it from?

tia