Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server 2000 DTS: Cannot expand named range.

Posted on 2008-10-08
5
Medium Priority
?
1,563 Views
Last Modified: 2013-11-30
I have been racking my brain trying to figure this out. I researched my "cannot expand named range" package error and can't see why it is erroring.

I am using multiple transform data tasks each with its own query to dump data into an excel spreadsheet. All but 5 are working properly, giving me this error:

---------------------------
Package Error
---------------------------
Error Source : Microsoft JET Database Engine

Error Description : The number of failing rows exceeds the maximum specified.
Cannot expand named range.

It doesn't matter if run these separately or not, I still get the error. I verified there is only one row returned for the query, and the transformations, etc. are all identical to the tasks that work. Can someone tell me what this means?
0
Comment
Question by:csho
  • 2
  • 2
5 Comments
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 2000 total points
ID: 22675529
Have you tried deleting the desination Excel file and creating a brand new blank excel file for the destination?
Perhaps its shuffling a named range along and its finally reached 2^16
Can you open the existing Excel file up and identify the named range? To do that you use the drop down box at the top left to the left of the formula bar.
 
 
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22675590
Do you know on which line it is failing?
0
 

Author Comment

by:csho
ID: 22688146
While trying to get the information requested in the first post, I noticed that some of the task's destinations pointed to a "Dta$" table in the spreadsheet. Changing them to "Dta" seemed to do the trick to fix my issues. Oddly enough, this discrepency wasn't on the tasks that were failing, so I'm not sure why that would have impacted anything, but I'm off and running now.

Thanks for your help. It was the first reply that led me to the discrepency, so I will award the points to nmcdermaid.
0
 

Author Closing Comment

by:csho
ID: 31504427
Thanks for your help!
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 22696560
Well... Sheet1$ for example is normally correct syntax.. glad you got a solution anyway,
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question