Solved

SQL Server 2000 DTS: Cannot expand named range.

Posted on 2008-10-08
5
1,512 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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…

623 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