I am using a .dqy file to import some rows from a sql server 2003
database to microsoft excel. Unfortunately, I have am importing more
rows than excel can handle. Is there a way around this?
It appears that there is no equivalent of the LIMIT(offset, number of
rows to return) function of mysql in tsql. Had there been an
equivalent, I would have created multiple .dqy files, which would query
the database for pages of results as appropriate. There is an upper
limit to the number of records that there can be in the database, so
this would have been a feasible solution.
Also, I must use .dqy files (or something equivalen) because the
queries are invoked from a website, and it is necessary to download the
results, sort/filter, etc. (in excel).
Thanks for any suggestions.imagine if you will a world where you get all the data you ever wanted
with subsecond response times.
you can slice and dice all of your data, sort the results, and
otherwise find exceptional values in an instant, no matter how large
your data is.
The answer is OLAP.
Barring that, you can use a reporting tool like Crystal.
Barring that, well, hmmmm.|||Thanks for your opinions.
Unfortunately, my reports are for non-technical users, otherwise I
wouldn't even need to bother with all of this - they could have queried
the sql database directly. However, your comments are appreciated.|||RA wrote:
> Thanks for your opinions.
> Unfortunately, my reports are for non-technical users, otherwise I
> wouldn't even need to bother with all of this - they could have queried
> the sql database directly. However, your comments are appreciated.
Non-technical users are exactly the audience that the BI tools are
designed for. I recommend you check out that option. There are much
richer, easier and cheaper solutions than the "dump the database in
Excel" method.
That said, it is perfectly possible to segment the data in the manner
you are proposing. In SQL Server 2005 you can make use of the
ROW_NUMBER() function to filter results. For earlier versions take a
look at: http://www.aspfaq.com/show.asp?id=2120.
BTW there is no SQL Server 2003. I expect you mean 2000.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Ok, thanks for the advice guys.
I meant SQL server 2000 (it was a typo). It now appears that the
requirement is no longer critical - the users are happy to be taught
how to query with SQL, until office 12 comes out (excel will have a row
limit of about a million [2^20])sql
