Unanswered: Exporting data from SQL Server to another using BCP (character conversion problems)
Hi! We have software products which use SQL Express 2008 R1 (MSDE in previous versions) to store the application data (in local PC). User has possibility (in application) to export the data and then import the exported file in another PC.
Currently the data is exported in a way that the database files are just copied and zipped. That works basically fine in our case, but the problem is that SQL Express R1 databases aren't compatible with MSDE, so the import/export can be only done between computers which have same SQL Server version (or target computer has newer version).
One logical solution would be to use BCP to export/import the data. The problem in this case are the character conversion. I haven't been able to figure out how could we use BCP in way that the character information would always be retained during export/import. Application doesn't restrict in any way what characters are stored in database and our application is used all around the world.
Our application is developed using .NET and ADO. Is there any way we could use BCP to handle the export/import process?