brevai
asked on
Export MS SQL 7 Server tables having TEXT fields to an ASCII file
Hi,
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?
Thanks,
Balazs
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?
Thanks,
Balazs
ASKER
Because the table also contains TEXT fields (containing for example word documents) and we can't assume that the delimiter we chose won't occur in the TEXT field. And from then on the exported table's structure is messed up in the exported file and it can't be imported anymore.
Balazs
Balazs
if i were u i would try to use a guid for a field delimeter
everytime u send data send a format file with text data
create a GUID for each data Export.. it cant be produced anywhere else and again..
so it ll protect u
If think that its not the solution
u must copy data to an Access file and then Send it to ur customer
Melih SARICA
everytime u send data send a format file with text data
create a GUID for each data Export.. it cant be produced anywhere else and again..
so it ll protect u
If think that its not the solution
u must copy data to an Access file and then Send it to ur customer
Melih SARICA
Hi Balazs
have you considered exporting as XML?
Persisting an ADO Recordset to XML
http://www.vbxml.com/xml/guides/developers/ado_persist_xml.asp
Alan
have you considered exporting as XML?
Persisting an ADO Recordset to XML
http://www.vbxml.com/xml/guides/developers/ado_persist_xml.asp
Alan
ASKER
Hi Alan,
The solution you have provided for exporting the database works fine on the server. We are exporting the database to XML tables.
Now our next problem is that we need to import the database on the client.
Currently we are using an MS Access database on the client. We have managed to load the XML file into a recordset using the LoadFromFile method. But then we don't manage to save the records to a table without looping through them. Do you have a solution for that?
We are also considering to move to SQLite on the client. Any solutions here?
Thanks,
Balazs
The solution you have provided for exporting the database works fine on the server. We are exporting the database to XML tables.
Now our next problem is that we need to import the database on the client.
Currently we are using an MS Access database on the client. We have managed to load the XML file into a recordset using the LoadFromFile method. But then we don't manage to save the records to a table without looping through them. Do you have a solution for that?
We are also considering to move to SQLite on the client. Any solutions here?
Thanks,
Balazs
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
why dont u use a specific delimeter for Example <rowdelimeter> or <fielddelimeter>
like
bcp msdb.dbo.sysjobsteps out "c:\test.txt" -c -U"Username" -P"password" -t"<fielddelimeter>" -r"<rowdelimeter>"
Melih sARICa