Solved

SQL Server 2000 DTS: Cannot expand named range.

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now