Too many SQLServer merge fields

[Pete I]

When I use an MS Query file to access a SQLServer 2000
database, it works fine for a couple of fields. But if I
add 10 or 15 more fields from the same table, I get the
error: "Word was unable to open the data source". I am
using Office 2000, SP 1 (9.0.2720). The query runs fine
when I run it and bring data into Excel.
- Pete

 

[Peter Jamieson]

Word imposes a severe limit on the length of any query it uses as a
mailmerge data source. What tends to happen is that if the SQL statement
exceeds that length, Word truncates it. The statement is then invalid and
you see the error.

If all your data is coming from a single table, the simplest thing to do is
a SELECT * FROM tablename and accept that there will be more fields listed
in Word's dropdown/field list than you need or want. You may need to fix the
SQL in MS Query even to get that right. If the data is coming from more than
one table or you really need to reduce the number of fields available, it's
best to create a View in SQL Server if possible, and use that as the data
source. If you are not in a position to do that, you may be able to create
an Access .mdb linked to the SQL Server database and create a Query in there
that will do the trick.

If you look at the SQL code generated by MS Query you will notice that it
makes extensive use of table aliases, which makes the code much longer than
it needs to be. So you can sometimes reduce the query length by shortening
the alias names (e.g. to 1 letter) or, if possible, removing them
altogether.

 

[Peter I]

Peter,
Thanks for the advice. I built a view, and named it
simply X to keep the query short. I'm not sure that I can
shorten the query any more. Is there a specific query
length, or is it possible there is another problem.
- Pete

XLODBC
1
DRIVER=SQL Server;SERVER=XXTEST05;APP=Microsoft Open
Database
Connectivity;WSID=C6XX;DATABASE=WCCCR1;Network=DBMSXXXX;Tr
usted_Connection=Yes;AnsiNPW=No
SELECT * FROM WCCCR1.dbo.X

 

[PeterI]

Peter,
Thanks very much for the detailed reply. I managed to
get it working by using the first method you suggested,
ie. naming each field with a one letter alias, and also
as you had earlier suggested by creating a view to
shorten the where clause. This brings it down to about
360 bytes, so I guess the limit is 510. The final query
is below.
- Pete

XLODBC
1
DRIVER=SQL Server;SERVER=XXTEST05;APP=Microsoft Open
Database
Connectivity;DATABASE=WCCCR1;Network=DBMSXXXX;Trusted_Conn
ection=Yes;AnsiNPW=No
SELECT V.FirstName, V.MiddleName, V.LastName,
V.AddressDetail, V.AddressDetail_1, V.AddressDetail_2,
V.CODE, V.PersonZip4, V.PersonCity, V.PersonState,
V.StratificationCurrent FROM WCCCR1.dbo.View_EnrollDue V

 

[Peter Jamieson]

OK, glad it's working.

FWIW it's only the stuff in the SELECT part that counts towards the
character count - my guess is that you have something slightly over 200
characters in there. Most of the rest of the stuff in the .dqy file is not
part of the Query as understood by Word - some of it will go into the
Connection string, and there's typically a line at the bottom (not shown in
your msg. ) - I think is to do with the column headings used in MS Query's
display of the data and is not used by Word