import/export problem

Question

import/export problem

fillae

Grasshopper

Points: 14
More actions
January 21, 2005 at 11:41 am

#163166

Hello,
I'm having a small problem exporting data from one sql database to another. I am exporting from the one database into an excel spreadsheet. I am then attempting to import that data into a different database. I am getting the following error when trying to do so:
"Data for source column 15 ('txtNotes') is too large for the specified buffer size."
The field in question is identical in both tables. Is excel doing something crazy with the data in that field?
Thanks for any help,
Ed

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

I would not export in Excel, especially with text fields. If you for some reason can not connect directly from one SQL server to another, I would export to CSV file all non-text fields, import them in SQL Server and then export / import text fields one at a time together with the ID field, ID field comes first. I had really weird cases with Excel with record dissappearing when text field contained apostrophy or commas or combination of symbols.

Another solution is to export the data you need in a table in a new SQL Server database on the source server, back it up, it should not be that big, restore on your second server and import data from table to table directly using

Use MyDestinationDatabase

select * into MyNewTableOnDestinationServer

from MyResortedDatabase.dbo.Mytable

Yelena

Regards,Yelena Varsha

PBirch SSCrazy Eights Points: 8493 More actions · Answer 2

Yes, it looks as if you are trying to move a column that is defined a text. Not sure, since you did not post your DDL.

I would try Yelena's suggestion of creating a new table (if you have the room for it) on your local database or on the server. Be careful, since moving data is resource-intensive.

You might also try copying the structure into the new database and then creating a DTS job that imports the data into the new table.

Quand on parle du loup, on en voit la queue

fillae Grasshopper Points: 14 More actions · Answer 3

Thanks!

I ended up creating a new database to use as a staging area. I imported the data I needed into the new database, modified the data structure as needed, then exported to the destination db.

Great site by the way.

Ed