Solved

SQL Server 2000 DTS: Cannot expand named range.

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Count with a subquery showing details 10 44
transaction in asp.net, sql server 6 33
New to SSRS, extremely slow running report. 8 20
SQL invalid column name 5 13
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…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

803 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