[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Export MS SQL 7 Server tables having TEXT fields to an ASCII file

Posted on 2004-11-12
Medium Priority
Last Modified: 2008-03-03
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?

Question by:brevai
  • 2
  • 2
  • 2
LVL 19

Expert Comment

by:Melih SARICA
ID: 12570753

why dont u use a specific delimeter for Example <rowdelimeter> or <fielddelimeter>

bcp msdb.dbo.sysjobsteps out "c:\test.txt" -c -U"Username" -P"password" -t"<fielddelimeter>" -r"<rowdelimeter>"

Melih sARICa

Author Comment

ID: 12570826
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.

LVL 19

Expert Comment

by:Melih SARICA
ID: 12570890
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


What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 26

Expert Comment

by:Alan Warren
ID: 12571991
Hi Balazs

have you considered exporting as XML?

Persisting an ADO Recordset to XML



Author Comment

ID: 12654705
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?

LVL 26

Accepted Solution

Alan Warren earned 2000 total points
ID: 12671620
Hi Balazs

I don't have a solution other the one you mentioned, I believe sql server has some better ways of doing this, with things such as the For XML clause, not done much in this area though.

The following is how I parse ADO - persited XML into a Jet catalog.

' The main thing we have going for us that the field names and types in the XML
'  are the same as the field names and types in the target table
' If they aren't then it is the old steam method :(

' Instantite an ADO recordset for the target table
' Instantiate an ADO recordset from the source XML

' Loop through

With rs
  For Each fld In rsEmail.Fields
    'ProgCtrl_One.Value = ProgCtrl_One.Value + 1
    Dim sFieldName As String
    sFieldName = fld.Name
    If fld.Value = "" Then
        .Fields(fld.Name).Value = Null
        .Fields(fld.Name).Value = fld.Value
    End If
    Next fld
End With

Re: Sql server and XML:

Success with your app!


Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question