Hi,

We have a database in SQL 2005 Server. We would like our Excel 2007
users to be able to create queries and receive the data in Excel. The
users must be able to choose fields, criteria and sorting with a
friendly GUI.

When using the "new style" SQL Server database wizards in Excel I was
very disappointed to notice you can not choose fields, criteria and
sorting. It just pulls in the whole table/view.

I eventually found the ODBC option under "Other Sources" and having
configured my per-user ODBC settings from Vista's Control Panel, I was
able to get the GUI where they can choose fields, criteria and sorting.
I think it uses the MSQUERY bundled with Office 2007.

The users were really pleased with the new facility. In the past they'd
had to copy and paste large data grids from one application to another,
but this was simple and gave them the answer in Excel, which is what
they wanted.

Then the PROBLEM!

With result sets of 18000 rows and 50 columns they started getting an
ODBC error "Protocal error in TDS stream". If I switch back to the "new
style" database wizard I can import 18000 rows and 50 columns without
any problem, so it seems the issue is related to ODBC. If I perform the
operation in MSQUERY I get similar errors on large result sets.

Is there some way I can fix this TDS stream error? If not, is there some
way I can enable the GUI query builder but using the new OLEDB drivers?

I tried starting "ODBC Tracing", but the query never finished and the
SQL.LOG file went over 75Mb, so I had to cancel the job, and I was
unable to use ODBC tracing to find errors only in this large result set.

Thanks for any help with this.

--
Gerry Hickman (London UK)

Hello Gerry,

It's more like an Excle/office related issue that you should post in Excel
newsgroup.

Based on my experience, after you create a connection to SQL Server in
Excel "Data" tab, and you click "Existing Connection"->Open

In "Import data" dialog, click Properties->Definition, and select "Edit
Query" you shall be able to edit the query.

If anything is unclear, please feel free to let's know.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


================================================== ===

When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi Peter,

Thanks for the help with this.

As I said, I tested it outside Excel using the MSQUERY client. I would not
expect the normal users of Excel groups to know how to solve "Protocol Error
in TDS Stream". That's why I posted it here as an SQL client issue...

There are two problems with this.

1. This option allows users to import the WHOLE result set without going
through the GUI wizard first. It's very unintuitive too.
2. The "Edit Query" button is grayed out in my tests on existing
connections, not sure why.

But none of this really helps to troubleshoot why I'm getting problems with
TDS stream while using ODBC, which I believe is an issue with the SQL client
components.

--
Gerry Hickman
London (UK)

Hi London,

Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.

From your description, I understand that you would like to use ODBC to
import the database from SQL Server into Excel. however when result sets of
18000 sets of 18000 rows and 50 columns, the following error comes out. If
I have misunderstood, please let me know.

Error
===========
Protocal error in TDS stream

Based on the current information, it seems that the issue might be a
known-issue, I would like to re-porduce it. So, if it is convenient to you,
please send an email to me. my email address is
v-fathan (AT) onliesadfn (DOT) microsoft.com(remove onliesadfn)

Besides, in order to resolve the issue, please update the ODBC driver.
please download MDAC 2.8 via the following and install it
http://www.microsoft.com/downloadS/d...fe3-c795-4b7d-
b037-185d0506396c&displaylang=en

If there is anything unclear, please let me know.

I look forward to your update.

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subs.../aa948874.aspx

================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Mark,

Thank you for the help with this.

That is correct.

OK.

As I said in my original post, I'm running Windows Vista and this download
does not list Vista as a supported operating system.

The MDAC version listed in the registry is 6.0.6001.18000

Thanks.

--
Gerry Hickman
London (UK)

Hello,

After further testing, I think I know why the "Edit Query" button is
grayed out in #2 above.

There are different ways to import data from SQL Server into Excel:

1. Using ODBC and MSQUERY (this gives the GUI option we need)
2. Using ODBC without MSQUERY (only able to import WHOLE table)
3. Using OLEDB without MSQUERY (only able to import WHOLE table)

It seems you only get offered the "Edit Query" button when you use an
ODBC connection, it is not offered for OLEDB connections.

If you click the "Edit Query" button, it invokes MSQUERY and you end up
with the EXACT same limitation and error messages.

I created a test database with 20,000 rows and 200 columns. I can import
the whole thing into Excel using OLEDB or ODBC, but as soon as I try to
use MSQUERY to choose fields, sort and filter, it FAILS. I can, however,
use MSQUERY to import 20,000 rows and only 3 columns...

--
Gerry Hickman (London UK)

Hello,

I now have confirmation from Microsoft that this is a limitation of MSQUERY.
The ODBC driver itself is able to work with large data sets, but there's a
limitation in MSQUERY that causes the import to fail in an unpredictable way
on large data sets.

--
Gerry Hickman
London (UK)

"Gerry Hickman" <gerry666uk2 (AT) newsgroup (DOT) nospam> wrote

Hi Gerry

Thank you for posting the result of the issue. I'm glad that the issue is
clear now.

if you have any other concerns, we welcome you post them here. it is our
pleasure to work with you.

Have a nice day.

Best regards,
Mark Han
Microsoft Online Community Support
Get Secure! - www.microsoft.com/security
================================================== ==
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ==
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ==