Hi dtooker,

Replicating data to a local database can be done by using any number of approaches.  Which approach to use depends on your specific requirements.

The questions that come to mind that must be answered before arriving at the correct approach are:

1. Is this a one time bulk load (snap shot)?

2. Is this a recurring bulk load (snap shot)?

3. Is this a one time bulk load (snap shot) with a recurring incremental load (replication)?

4. Will this be bi-directional?

For a start, assuming a one bulk time load or recurring bulk load, you need to first design a query the gets all the records you want.  If you want everything, then don't include a where clause in your query.  If you want every field on the table then you will need to build the select list portion of the query from the results of a describe call on the object to load.  This involves simply looping through the array of fields and building a comma delimeted string of the field names.

Once you have retrieved a set of data from salesforce.com, you will want to load it into SQL server.  You can obviously step through thre results and create an insert statement for each record.  But it sounds like you don't want to do this.  The alternative is to create bcp (bulk copy ?process?) file for sql server.  You can then run bcp to upload the data in a big batch (see the sql server documentation for more info on this).  I've also found that if you do the single inserts wrapped in transactions from code, it is faster than without transactions.  If you have any indexes created on the tables that where generated for the salesforce.com data, I would remove those during load and re-index after the load.  This will save time loading as well.

You may be able to create a DTS process that runs an ActiveX script to generated a text file and then loads that text file into the appropriate table.  This is nice because once you design and build the DTS package you can execute it at will.

Another alternative might be to leverage the XML capabilities in SQL server.  With some slight modifications to the sforce .NET Managed Data Provider, you can programatically fill a dataset and cause the contents to be emitted as an XML file.  Unfortunately, I'm not real familiar with the XML data loading features of SQL Server.

For ongoing maintenance of the data in sql server, we provide two calls that are used by polling applications to detect new/updated records and deleted records.  This usage of these two calls is illustrated in the sforce Explorer.  Using the getUpdate call, you can detect when a new record is created or when an existing record is modified (you really don't know wheter it's new or modified till you check either the existence of the record in your database or the createdDate on the record) and then fetch the record and load into SQL Server.  Using the getDeleted call you can obtain the id of a record that was deleted and, using that id field, perform an archive or deletion on the SQL Server.

Hope this helps.

Dave,

This is for a daily download.  Records dont need to be bi-directional.  I was writing a VB app in .NET to logon and get the field names for the download query statement.  I was trying to open a ADODB recordset and use the results of the query to populate the SQL table.  I cant find a way to use the  sforce.QueryResult in the recordset.  I dont want to have to step through each record and do an insert statement.  Is there any way you can think of to do this?  Also didnt want to use BCP to do this.

If I used a DTS package, would I just write the same thing I am doing with the .NET app and logon to SF, then use the sforce.QueryResult object to insert into a text file.  Then have DTS upload the data from the text file to SQL? 

Thanks for any help.

DTOOKER

Dave,

Thaks there is alot of good code in here.  I am having a problem though converting the c# code to use in my VB app.  The main problem I am having is getting the field values while looping(string fieldValue).  In the code below, I can't figure out how to convert it to VB.  I am using the code from the sample vb_quickstart as the template for my project.  Here is the code I am having a problem converting:

      DataRow dataRow = dataTable.NewRow();
      for (int j=0;j<qr.records[i].Any.Length;j++)
      {
       string fieldName = qr.records[i].Any[j].LocalName;
       string fieldValue = qr.records[i].Any[j].InnerText;
       if (!qr.records[i].Any[j].HasAttribute("xsi:nil"))
       //if (qr.records[i].Any[j].GetAttribute("nil") == null)
        dataRow[fieldName] = fieldValue;
      }
      dataTable.Rows.Add(dataRow);

Thanks for any help....

DTOOKER

Oh, dang.  Had I known you were a VB person I would have done it in that.

So, lets try this:

DataRow dataRow = dataTable.NewRow()

For j As Integer=0 to qr.records(i).Any.Length - 1

       Dim fieldName As String = qr.records(i).Any(j).LocalName
       Dim fieldValue As String = qr.records(i).Any(j).InnerText
       If Not qr.records(i).Any(j).HasAttribute("xsi:nil") Then

              dataRow(fieldName) = fieldValue

       End If

       dataTable.Rows.Add(dataRow)

Next