?
Solved

Could not export sql server 2008  table's varchar (8000) column to excel 2007

Posted on 2011-10-02
17
Medium Priority
?
4,211 Views
Last Modified: 2012-05-12
while trying to export a table with varchar column(8000) to excel 2007 or 2003 i m getting the following error. The mappings show the varchar(8000) is trying to convert in long text

Could not connect destination component.

Error 0xc0204016: SSIS.Pipeline: The "output column "Question" (71)" has a length that is not valid. The length must be between 0 and 4000.


any ideas how can we fix this
0
Comment
Question by:mmalik15
  • 7
  • 6
  • 4
17 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36900532
Do you have data over 4000 characters? One simple approach would be to CONVERT(VARCHAR(4000), Question) in export query.
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 36900535
You can output as csv and then open that in excel
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36900538
The other option, as I believe it is type conversion issue, is to convert to NVARCHAR(MAX) or NVARCHAR(8000). Aaron's suggestion looks promising also as the conversion issue is between providers, so it may just be a conflict with the Excel provider that can be overcome by going to CSV.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 36900561
You could also cast as text instead of varchar 4000, that way you don't lose anything.

Another thought, I've had excel be unhappy with varchar(max) even though it should be the same as varchar(8000). If it is set to varchar(max) try cast as varchar(8000)
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36900579
Nice suggestions, aarontomosky. I would NOT go with a cast to TEXT, though. It is being deprecated and really should be used in SQL 2008 if can be avoided.
0
 

Author Comment

by:mmalik15
ID: 36900625
Thanks for the comments guys. I have tried exporting in csv and it exports okay. The root problem that I have is that  I have three versions of the same database. Three different people have inserted data into the same table and i need to synchronise that table now from those databases. I thought it would be easier to export in excel and use excel filter to remove duplicates and import into database again. This import/export between MS Sql server 2008 and excel 2007 is not looking very friend and i wonder if there is a better solution to acheive the desired result.

p.s. shall I put this as seperate question as the context of the problem is different now?
0
 
LVL 39

Accepted Solution

by:
Aaron Tomosky earned 1000 total points
ID: 36900633
If you can get all three db on the same server (or do some trickery to cross server query) you can do it all right from ssms. Basically From the db with new data:
 insert into dbname.dbo.table select * from table where pk not in (select pk from dbname.dbo.table)

Repeat from other DBs.
0
 

Author Comment

by:mmalik15
ID: 36900646
As the db name is same for all the three dbs so jus rename the db's attach them all to the same instance of sql server and use insert into dbname.dbo.table select * from table where pk not in (select pk from dbname.dbo.table)

is that right?
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 36900715
Change the table name, put in the columns instead of *
yep.
0
 

Author Comment

by:mmalik15
ID: 36900727
thanks again for the comments. One final issue the pk is auto genrated and could be same in different databases. Guess we should be able to filter on varchar fieids too?
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 1000 total points
ID: 36900730
Correct. If you have the ability to put these in the same instance and have for example.

ProductDB.dbo.TableName
RestoredDB1.dbo.TableName
RestoredDB2.dbo.TableName
RestoredDB3.dbo.TableName

Then it is pretty straight forward. I would use NOT EXISTS personally.

INSERT INTO ProductDB.dbo.TableName({column list})
SELECT {column list}
FROM RestoredDB1.dbo.TableName r
WHERE NOT EXISTS (
   SELECT 1
   FROM ProductDB.dbo.TableName p
   WHERE p.pkColumn = r.pkColumn
)
;

Other notes, if these are on different databases and pkColumn is sequential, you may need to account for the fact that pkColumn may exist but reflect different data. Therefore, is there other data that identifies a row as a unique entry? If so, take advantage of that. In addition, you will need to determine which database wins in conflict resolution.

Similarly, within a table if there are duplicates, you can use ROW_NUMBER() and CTE to eliminate the duplicates like:

;WITH cte AS (
   SELECT *, ROW_NUMBER() OVER(PARTITION BY {columns that make unique row} ORDER BY pkColumn) RN
   FROM {any table}
)
SELECT *
FROM cte
WHERE RN > 1
;

Change this to a DELETE and it will remove all the extra rows, leaving the one you want to keep, i.e., RN = 1. ORDER BY should be how you want these. You may choose to take last updated for example (e.g., ORDER BY LastModifiedDate DESC).

Hope that helps!
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 36900733
Does the table have one unique field across the databases? Maybe you do want to make this a separate question with more detail on the table
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36900735
Sorry, I was typing and missed the other posts in the thread. Hopefully, my comment answers some of your questions. Apologies for duplication already handled in previous couple of posts from aaron.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36900741
Agree. I think http:#36900730 should be pretty detailed to get you on the way, coupled with aaron's advice to this point; therefore, I would give it a try and then if you have specific errors or challenges post a new question. For now, it appears http:#36900535 answers the original question.
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 36900742
If I had a keyboard I'd have awesome descriptive solutions of awsomeness too ;)
0
 

Author Comment

by:mmalik15
ID: 36900746
Brilliant comments guy!

Many thanks
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36900759
Aaron,

You made some pretty "awesome descriptive solutions of awesomeness" without a keyboard. It was a pleasure collaborating with you BOTH.

mmalik15, glad we could help!

Best regards and happy coding,

Kevin
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

850 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