Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
SolvedPrivate

SQL Server Import and Export Wizard Errors

Posted on 2014-01-10
11
Medium Priority
?
34 Views
Last Modified: 2016-02-10
I'm getting the attached errors when I run my query through the SQL Server Import and Export Wizard (SQL 2008R2).  

I'm extracting table date to Excel.  When I run the query via SSMS it runs fine. When I look at the Preview inside the Wizard it looks fine.  I ran the same query on a smaller database and it ran fine. I know it has to do with size/number of records. It fails somewhere after row 65000. There are over 200000 rows.

Anyone have any idea how to fix this? I've read much online but can't get an fix specific to my issue. Someone mentioned in a post that in the Column Mappings the Source Type-Destination type entries might be causing it and need to be modified (example: Column Source Type=varchar, Destination Type=LongText).

Any help is greatly appreciated.
ExportContactsReports.txt
0
Comment
Question by:CityofDelrayBeach
[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
  • 6
  • 5
11 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 39771637
Does this work locally but not when you deploy?  On MSDN blog, I read that one potential cause of DTS_E_OLEDBERROR is saved password will not decrypt on machine other than the one it was encrypted on.  In addition, it lists cause for 0x80004005 error number as a permissions with the SQL Agent job.  The resolution is to change the permissions on the Teamp directory of the SQL Server agent service account according to Microsoft.

You can also read the blog as it goes through other SSIS Error Code DTS_E_OLEDBERROR scenarios that may help you troubleshoot this.
0
 

Author Comment

by:CityofDelrayBeach
ID: 39771778
Hi Kevin. Thanks so much for your response.

As mentioned in my original post, it works in SSMS but not in Export Wizard. The query also works on a smaller database (which exactly the same, just less records) but not on the larger one.  Thus, I'm certain it has something to do with the number of records.

I have read the article about Temp directory permissions.  Unfortunately, it is not specific. I would have to call Microsoft to clarify exactly what they are talking about. I've sent Feedback on that article to this effect.

I'm looking at the other article now.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39771797
Sorry.  I focused on the error being SSIS.  I see you actually are using the Export wizard, which creates an SSIS package automatically.  I have run large tables through Export before, but you may be hitting a file limit.  What file are you going to (e.g., Excel)?  If Excel, what version?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39771819
Argh.  Not enough coffee today.  I see you specified Excel, so my questioning was to point out that Excel 2003 has a row limit of 65,536; therefore, you may be seeing a failure because of that.  Try exporting to Excel 2007 or a CSV file to see if it works for you.

EDIT: if the CSV approach works for you, you can split the file to work with Excel 2003.  Otherwise, you can pull data in batches of 65K and store in a different Excel file.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39771844
Here is how the error should look if I am correct.

Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft JET Database Engine"  Hresult: 0x80004005  Description: "Spreadsheet is full.".
 (SQL Server Import and Export Wizard)

If I export the same table to Excel 2007, it works just fine.  It has over 200K rows.
0
 

Author Comment

by:CityofDelrayBeach
ID: 39772063
Hi again, Kevin.

I've was originally using Excel 97-2003 because I didn't have the Access Database Engine installed, but I installed the ADE and chose Excel 2007 (which is the latest listed in the drop-down) but get the same errors.

I exported to CSV now using"Flat File Destination" in the drop-down and it went though.  149,000 rows.

It's just soooo annoying how cryptic Microsoft errors are! Why not just say "too many records"?

But what still doesn't make sense is why there is a limit when I'm using Excel 2007.
0
 

Author Comment

by:CityofDelrayBeach
ID: 39772095
OK, so something isn't right.  the csv file doesn't have the data correct in some of the columns.  It says $GROUP instead of the actual data.

I may need to call Microsoft on this one.  It doesn't make sense why the export isn't working.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39776454
I agree.  I will double check as I may have Excel 2010, but still should have worked for you.  Please update the thread if you get a good explanation from Microsoft.  It will be helpful to others who may run into this.
0
 

Author Comment

by:CityofDelrayBeach
ID: 39776621
I am going to close this and open a new thread because I have to start over. I'm not getting the results I need with my queries. I'm not sure whether I need to use JOIN or UNION to get the results I need.

Thanks for all your help.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39776905
Okay.  I see Sharath already is helping you with that.  As an aside, I find that sometimes it works well to use the query functionality of Excel to pull data versus to push from SQL Server.

Anyway, best regards, Kevin
0
 

Author Comment

by:CityofDelrayBeach
ID: 39776931
Hi Kevin.  Yes, I tried that but I'm still stuck on how to combine all the data into one worksheet when there are multiple JOINS.  Can you help?
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

670 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