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

Do you have data over 4000 characters? One simple approach would be to CONVERT(VARCHAR(4000), Question) in export query.

You can output as csv and then open that in excel

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.

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)

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.

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?

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?

Change the table name, put in the columns instead of *
yep.

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?

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

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.

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.

If I had a keyboard I'd have awesome descriptive solutions of awsomeness too ;)

Brilliant comments guy!

Many thanks

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