Import into Excel from Microsoft Query Problem

[vegasrockstar]

Hi all.

I am trying to import data from a SQL Server database into an Excel spreadsheet. I have done this before using the exact same database with no issues.

However, this time, when I go through the exact same process (Data > Import External Data > New Database Query, select datasource, insert SQL) I return the data into Microsoft query fine, but when I hit the Return Data button it does not populate the spreadsheet.

All I get is "Query from " dbname in cell A1. I try to refresh data and check using the refresh status and I see that it runs for a second or so and then completes returning 0 rows. In the Microsoft Query resultset there are over 12,000 rows.

Any ideas guys?

 

[SkipVought]

Hi,


Are you giving the query enought time to run? This is not instantaneous. The Query from... in the target cell is an indication that it is running. Go get a cup of coffee and check your eMail. It could take a couple of minutes.

Skip,

_[red][/red]

 

[vegasrockstar]

Hi Skip

I tried that for an hour ....

You can tell if the query is running by looking at the Refresh Status icon in the External Data tool bar. If the icon is greyed out it means that the query is not running.

When I run the query it lights up for a few seconds and then greys out again, but I still get the "Query From" in A1.

 

[SkipVought]

Stab in the dark -- try downloading a new ODBC driver.

Do you have the same symptoms with other types of drivers/databases or just SQL Server?

Skip,

_[red][/red]

 

[vegasrockstar]

Hi Skip

I have overcome the problem (must have been timing out) by using a view on the SQL Server db and selecting all from that, rather than running the complex SQL query in Excel.

I guess there is a limit to how much processing Excel can do (via MS Query) within the allotted timeout period and I exceeded that.

Thanks for your help anyway.

;-)

 

[SkipVought]

Some drivers have parameters that you can set in the Connect String and/or in the driver setup configuration.

I do some pretty complex joins in DB2 & Oracle from Excel via MS Query. DB2 occasionally times out.

Skip,

_[red][/red]