Cant fit results of tsql query in excel

Hi,

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

Cant eliminate duplicate rows from results

This query:

SELECT a.[Name], b.Title, b.ISBN
FROM Authors a
INNER JOIN BookAuthors ba ON a.AuthorID = ba.AuthorID
JOIN Books b ON b.ISBN = ba.ISBN
GROUP BY a.[Name], b.Title, b.ISBN
ORDER BY b.Title

returns 26 rows, however, several of the rows are duplicates, and I can't eliminate them! Arrrgh!

Here's a url to my Database diagram:
Database Diagram Image (http://www.panvox.net/tests/dbpic.jpg)

Any help will be greatly appreciated,

PASIf the Distinct logical operator hasn't addressed the issue (?), consider posting a table ddl / sample data script that reproduces the result).

SELECT Distinct a.[Name], b.Title, b.ISBN
FROM Authors a
INNER JOIN BookAuthors ba ON a.AuthorID = ba.AuthorID
JOIN Books b ON b.ISBN = ba.ISBN
GROUP BY a.[Name], b.Title, b.ISBN
ORDER BY b.Title|||Part of the problem is these tables are not normalized properly!

Anyhow, here's the .sql file for the tables involved,

PAS

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_BookAuthors_Authors]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[BookAuthors] DROP CONSTRAINT FK_BookAuthors_Authors
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_BookAuthors_Books]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[BookAuthors] DROP CONSTRAINT FK_BookAuthors_Books
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_OrderItems_Books]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[OrderItems] DROP CONSTRAINT FK_OrderItems_Books
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Authors]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Authors]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BookAuthors]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BookAuthors]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Books]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Books]
GO

CREATE TABLE [dbo].[Authors] (
[AuthorID] [int] NOT NULL ,
[Name] [char] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[BookAuthors] (
[BookAuthorID] [int] NOT NULL ,
[ISBN] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AuthorID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Books] (
[ISBN] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Publisher] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [char] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UnitPrice] [money] NOT NULL ,
[Abstract] [char] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Pages] [smallint] NOT NULL ,
[Published] [datetime] NOT NULL ,
[Stock] [int] NOT NULL
) ON [PRIMARY]
GO|||RE:
Part of the problem is these tables are not normalized properly!

Much easier to see the ddl than the diagram; "not normalized properly" almost always becomes an issue. (Same ISBN, multiple different pub dates, etc., etc., no doubt.) In that case, normalize appropriately, or :( insert the duplicate rows result set into #worktable and use the Distinct logical operator on the #worktable to return a de-duplicated result set.

Cant delete records from DB .. says : Too many rows were affected by update.

Hi,

I've added multiple records with same info during practice. Now I"m trying to delete those records from SQL Server DB. but it says

"Key column information is insufficient or incorrect. To many rows were affected by update."

What to do, to delete these records?

Appreciated..if you post the code we can help you out but the error message you're getting means you're trying to update a row that has a duplicate. So, if your table had a primary key, you couldn't have a duplicate row and wouldn't have a problem.

hth|||Add a new column to your table and give each record a unique value for the column. Quickest way to do this is to add an Indentity column to the table (SQL Server will add the unique values). Delete the records you don't want and then you can remove the Indentity column if you want.