Solved

SQL Server 2000 DTS: Cannot expand named range.

Posted on 2008-10-08
5
1,478 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 500 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

837 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