Export MS SQL 7 Server tables having TEXT fields to an ASCII file
Posted on 2004-11-12
We are developing an application that uses MS SQL 7 database. The database contains 100-500 thousand records. The application needs to be able to save the database to a text file on user request and we also have to be able to reproduce the database from the saved text file (kind of syncronisation on the client).
The database contains TEXT fields as well. We are looking for a way to export the database to the text file.
The problem is that the TEXT fields can contain any type of characters. So we don't think we can use the BCP tool because it's not possible to define any delimiter that does not appear for sure in the TEXT field.
We were also thinking of solutions like replacing all " characters to "" in the TEXT field and put a " in the beginning and the end of the field but there does not seem to be any REPLACE function for TEXT fields to accomplish this.
Iterating through the table records and fields and exporting them one by one seems to be the only solution but it's not too fast.
Do you have experience with this problem?