README file for DBAmp/Pro WARNING: This version of DBAmp is NOT compatible with SQL 2000. Do not install if you are using SQL 2000. This version of DBAmp is also NOT compatible with Windows XP. ================== INSTALLATION NOTES ================== YOU ARE NOT DONE YET. There are additional installation instructions in the DBAmp Installation Manual. Please continue the install using the procedures in Chapter 1. IF YOU ARE UPGRADING, a SQL restart is required and you must upgrade every SQL database that currently contains DBAmp stored procs with the new versions. Do this by running the Create DBAMP SPROCS.sql file. See the DBAmp Installation Documentation for more information. =============== VERSION HISTORY =============== Version 2.14.9 ================================= - Fixed bug where large negative numbers exceed the column definition - Fixed bug with DAY_ONLY function when using openquery - Corrected locking issue with SSMS when running SF_Refresh Version 2.14.8 ================================= - Added support for Converting Leads to SF_BulkOps stored procedure Version 2.14.7 ================================= - Added support for SOAP Headers in SF_BulkOps stored procedure - Created new logging stored procedure: SF_Logger Version 2.14.6 ================================= - Registry settings are now available in the DBAmp Configuration Program - By default, DBAmp does NOT download the data of Base64 columns and the value is set to NULL. Modify the "Base64 Field Maximum Size" registry setting to change this behavior. - Numerous fixes to Base64 fields (i.e. Attachments) Version 2.14.5 ================================= - DBAmp now compatible with SQL Server 2012 Version 2.14.4 ================================= - Code modified for better memory utilization - Added support for API 24.0 (SPRING 12) - Fixed issue with embedded question marks in string literals - Fixed issue with using ConvertTimezone in openquery - Fixed issue with "Invalid Numeric" when using aggregate functions in openquery Version 2.14.3 ================================= - Added bulkAPI option to sf_refresh. Version 2.14.2 ================================= - SF_Replicate / SF_ReplicateAll now automatically create a primary key on the Id field - SF_CreateKeys / SF_DropKeys now only create/drop foreign keys. - Added support for UnDelete operation with SF_Bulkops Version 2.14.1 ================================= - Added support for loading content and attachments using a file name. - The following features have been deprecated starting with this release: SQL 2000 support, sys_sflastid, and SF_UploadFile. Contact DBAmp support if you need to explore workarounds. Version 2.13.13 ================================= - Corrected length calculations for MaxBase64Size - Fixed bug with OpenQuery / SOQL when using Group By Version 2.13.12 ================================= - Support for API 23.0 (WINTER 12) - Support for the MetadataOverride registry setting Version 2.13.11 ================================== - Added support for running DBAmp out of process - Added support for Delete with OpenQuery - Corrected error with sf_createviews not including date fields Version 2.13.10 ================================== - Modified sf_refresh to add timestamp to the temporary tables it creates - Added 'Verify' option to sf_refresh and sf_refreshall - All base64 fields are now treated as nullable. Version 2.13.9 ================================== - Added 'Repair' option to sf_refresh Version 2.13.8 ================================== - Improved memory utilization for low memory situations - Fixed bug when using OpenQuery to Update a column to null - Fixed issue with embedded single quotes in a where clause - Support for API 22.0 (Summer 11) - The Chatter Feed tables have been removed from sf_replicateall and sf_refreshall Version 2.13.7 ================================== - Fixed bug with WHERE clause evaulation when using the BIT datatype - Fixed bug when UPDATE with OPENQUERY sets a Datetime field to a new value Version 2.13.6 =================================== - Fixed bug with sf_refresh and deleted records (V 2.13.5 users only) Version 2.13.5 ==================================== - Support for API 21.0 - MAJOR BREAKING CHANGE with sf_bulkops. In order to make it easier to detect successful rows after a sf_bulkops run, sf_bulkops now populates the Error field with Operation Successful. Previously, sf_bulkops would write a single blank for successful records. All post sf_bulkops processing must be examined and rewritten to the new output: Select * from Account_Update where Error like '%Operation Successful%' If you require the Error column handling of the previous versions of DBAmp, there is a registry switch you can set to force DBAmp to revert to the previous behavior: LOCAL_MACHINE/SOFTWARE/DBAmp/BulkOpsCompatibility This version of DBAmp writes the phrase Operation Successful to the Error column when the operation succeeds. Previous versions of DBAmp wrote a single blank for successful rows. If your integration code needs the previous version behavior, set BulkOpsCompatibility to 1. Be sure to restart SQL Server after changing this setting. - MAJOR BREAKING CHANGE: SQL 2008 and datetime2(7). On SQL 2008 systems, date and datetime fields of salesforce.com objects are now created as datetime2(7) fields in the local database. To force these fields to be created as datetime fields instead, set the Database Compatibility Level of the Salesforce backup database to 90 prior to replicating the data (step 5 above). This change applies to SQL 2008 only. Version 2.13.4 ==================================== - Fixed issue with V2.13.3 and SQL 2000 - Added SSIS direct connect support. See documentation for details. Version 2.13.3 ==================================== - Fix bug when using the BulkAPI switch of sf_bulkops. Symptom is an erroneous"In transmitted batch" error msg in the error column. - Fix bug when sf_bulkops is passed a link server name that contains an embedded blank Version 2.13.2 ==================================== - Full SOQL support in OPENQUERY (see next item) - IMPORTANT: There are potentially breaking changes in this version for users of OPENQUERY. Please review your integration and correct as needed: * OPENQUERY now accepts full SOQL syntax. SQL syntax is no longer accepted in the OPENQUERY. * Updates that use OPENQUERY must include the Id as a column in the column list. * Deletes that use OPENQUERY are no longer allowed. Version 2.13.1 ==================================== - Support for API 20.0 - Fixed bug with column length changes not being detected as schema change Version 2.12.18 ==================================== - Fixed bug when Inserting with bulkapi using RecordIds Version 2.12.17 ==================================== - Added support for the salesforce bulk api with sf_bulkops - Fixed sf_bulkops bug for msg: Unable to create Salesforce Table Object Version 2.12.16 ==================================== - Fixed bug where sf_refreshall fails because TRY..CATCH fails to ROLLBACK Version 2.12.15 ==================================== - Fixed bug with Professional Edition access - Fixed bug with numeric overflow Version 2.12.14 ==================================== - Fixed bug that cause msg "Invalid data for type "numeric" - Fixed bug with sys_sffield output field SQL_Definition Version 2.12.13 ==================================== - Modified SF_ReplicateAll and SF_RefreshAll to continue processing if an error occurs - Support for API 19.0 Version 2.12.12 ==================================== - Added ToLabel support for picklist conversion Version 2.12.11 ==================================== - Fixed bug that caused dumps when the zip library was not initialized - Added ORDER BY support for SOQL queries - UserProfileFeed object is now skipped by both sf_replicateAll and sf_refreshAll Version 2.12.9 ==================================== - Fixed memory leak that occurs when an error is encountered in a query Version 2.12.8 ==================================== - Support for API 18.0 Version 2.12.7 ==================================== - Fixed bug with SOQL Count() function returning multi-row results - Added (back) Subset option on sf_refresh allowing you to handle schema changes better - All custom fields are now created in SQL to allow nulls. - There are now 2 versions of Create DBAmp SPROCS.sql. SQL 2000 users must execute Create DBAMP SPROCS SQL2000.sql - Fixed bug with detecting delete rows in sf_refresh for linked servers using a read-only account Version 2.12.6 ==================================== - Added support for column subset views as a workaround for the 10K SOQL limit Version 2.12.5 ==================================== - Correct one small memory leak - Improved network retry capability Version 2.12.4 ==================================== - Corrected two small memory leaks Version 2.12.3 ==================================== - Removed buggy implmentation of sf_refresh Subset option Version 2.12.2 ==================================== - New stored procedure- SF_RefreshIAD. See documentation for details Version 2.12.1 ==================================== - Support for Proxy ConfigURL removed. All other proxy options remain. Version 2.11.9 ==================================== - Added support for alternate invocation of sf_bulksops - Added 'subset' option to sf_refresh and sf_refreshall Version 2.11.8 ==================================== - Added support for api version 17.0 - Removed the Vote object from sf_replicateall and sf_refreshall due to salesforce restriction on queries Version 2.11.7 ==================================== - Fixed bug with single quotes in parameter values - Fixed bug causing error message "Cannot get the data of the row from the OLE DB provider Version 2.11.6 ==================================== - Fixed bug with table names included on non-SOQL queries Version 2.11.5 ==================================== - Added support for SOQL relationship queries when using OPENQUERY. This new feature can make dramatic performance differences when joining tables. Version 2.11.4 ==================================== - Fixed bug with __Share and __History tables when using sf_bulkops or sf_generate Version 2.11.3 ==================================== - Fixed bug with decimal rounding Version 2.11.2 ==================================== - Fixed bug with UTF8 conversion Version 2.11.1 ==================================== - Added RAISEERROR statement to the sf_bulkops stored proc when it fails - Added additional error messages for the "Batch Error" of sf_bulkops - Added code for better memory management - Fixed bug with negative literals in a WHERE clause Version 2.10.9 ==================================== - Fixed bug causing message "Error translating SQL statement.." when using parameters - Added ability to use IgnoreNulls option with upsert Version 2.10.8 =================================== - Option added to sf_bulkops to control batchsize. Smaller batch sizes may required to meet APEX restrictions. Version 2.10.7 =================================== - Fixed slow memory leak which caused the error message: Not enough storage is available to process this command. - Fixed bug with sf_bulkops when the table contained an Identity column. - Added the ability to suffix table names with _QueryAll to retrieve archived and deleted records for that table. Version 2.10.6 ==================================== - Added support for BIT datatypes. See documentation for details on how to enable this setting. - Fixed bug introduced in 2.10.4 with 2 single quotes Version 2.10.5 ==================================== - Added ability to Count() records with OPENQUERY. See documentation for more details. - Added support for ConvertCurrency. See documentation for more details. - Fixed bug with sf_refresh when used with Arizona timezone. Version 2.10.4 ==================================== - Fixed "Unable to set salesforce ServerURL and Session Id" bug with sf_replicate when a proxy was configured. - Fixed bug with 2 single quotes in an SQL statement ( where LastName = 'O''Dell' ) Version 2.10.3 ==================================== - Upgraded to API level 15.0 (Spring 09) Version 2.10.2 ==================================== - Fixed bug where a Select against the Idea object returns Invalid data for type "numeric" - Modified queries against the sys_sfobjects table to flush the local metadata cache - Added new Trace option to show salesforce.com API performance Version 2.10.1 ==================================== - Redesigned sf_replicate for performance and reduced API counts Version 2.9.19 ==================================== - Redesigned sf_refresh for performance and reduced API counts - Rewrote sf_replicateall and sf_refreshall to use sys_sfobjects Version 2.9.18 ==================================== - Added support for Winter 09 (API 14.0) - Removed ServerTimestamp from sys_sfsession for performance Version 2.9.17 ==================================== - Fixed bug with SQL Update that returns error msg "Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption." - Corrected sf_refreshall error with the AsyncApexJob table Version 2.9.16 ==================================== - Fixed bug with sf_refresh returns message "Error converting data type DBTYPE_DBTIMESTAMP to datetime" - Improved single SQL Insert performance - Added new option for sf_bulkops: Update:IgnoreNulls. See doc for more details Version 2.9.15 ==================================== - New system table added: sys_sfFields. Contains information about salesforce.com fields. - New SQL script (Create DBAMP UDFS.sql) to create User Defined Functions for all DBAmp system tables. - SF_BulkOps now produces warning messages for columns that do not match a field in salesforce. - SF_ColCompare proc has been improved. See documentation for more details - SF_Generate proc introduced. SF_Generate automatically creates input tables for SF_BulkOps - Improved salesforce.com session caching to minimize the number of salesforce api logins - Upgraded to use salesforce API 13.0 as default Version 2.9.14 ==================================== - Fixed bug that caused UTF8 conversion errors with Task table - Modified sf_refreshall to skip the PartnerNetworkConnection table - Fixed bug with the sys_sfpicklists table - Fixed bug where sf_bulkops of Lead table was adding an AssignmentRuleHeader erronously, causing leads to be reassigned. Version 2.9.13 ==================================== - New parameter for sf_refresh and sf_refreshall that allows you to automatically replicate the object instead of throwing an error when there has been a schema change Version 2.9.12 ==================================== - Fixed issue with timeouts occuring with sf_replicate and sf_refresh Version 2.9.11 ==================================== - Added support for mulitple serial numbers with different expirations - Fixed issue with dates in salesforce earlier than Jan 1, 1753 Version 2.9.10 ==================================== - Error reporting added for low memory situation where DBAmp is unable to allocate needed buffers - Added UseDefaultAssignment registry switch to add Assignment header to create and update requests Version 2.9.9 ==================================== - Fixed issue with SQL Update on 64 bit platforms - Fixed issue with reference fields that have null values Version 2.9.8 ==================================== - Fixed situations where a field is made required but null values still exist on previous records - Added SF_VASQuery for monitoring VAS memory on SQL 2005 systems. - Modified SF_Refresh to handle ID collisions on the OpportunityHistory table Version 2.9.7 ==================================== - Add switch to turn off compression for debug sessions Version 2.9.6 ==================================== -- Stored Procedures sf_refresh, sf_bulkops, sf_uploadfile modified to use less sf session resources -- Maximum size of Documents increased to 12582912 bytes. -- DBAmp Configuration Program modified to allow update of DBAmp Serial Number -- Upgraded to use salesforce API 12.0 as default -- Fixed issue with text literals used in Select clause Version 2.9.5 ==================================== -- Added support for replicating archived and deleted records via sf_replicateIAD -- Corrected processing for IN clause Version 2.9.4 ==================================== -- Added support for encrypted fields -- Fixed bug with sf_bulkops when errors occured in a batch. Version 2.9.3 ==================================== -- Changes made to Trial Key algorithms Version 2.9.2 ==================================== -- Support for EmailHeaders via new registry switches Version 2.9.1 ==================================== -- Support for foreign key relationships when doing bulk operations -- Fixed bug with sf_replicate and large attachments Version 2.9.0 ===================================== -- Internal migration to VS 2005 from VS 2003 -- New version of Office Toolkit included in the install Version 2.8.5 ===================================== -- Added Registry switch NoTimeZoneConversion. Default value of 0 indicates that DBAmp is to do local to GMT conversions Value of 1 indicates that DBAmp is to operate in GMT time only Version 2.8.4 ===================================== -- Fixed bug with sf_replicate failing while downloading a zero byte Attachment. Version 2.8.3 ===================================== -- Fixed bug with sf_bulkops where failed row error msgs were not written to the table. Version 2.8.2 ===================================== -- New versions of sf_replicate and sf_refresh with enhanced performance. Version 2.8.1 ===================================== -- Modified code for better salesforce session management -- Modified code to handle new sandox org id changes with Summer 07 Version 2.8.0 ===================================== -- Modified SF_Refresh and SF_RefreshAll to refresh History tables -- Bug fix: ReceiveTimout Value from registry is now correctly used when replicating Version 2.7.8 ===================================== -- Small enhancement to increase tcp utilization Version 2.7.7 ===================================== -- Bug fix for a small memory leak -- Modification to allow global temporary tables to be pass into sf_bulkops (## tables) Version 2.7.6 ===================================== -- Added a new version of replicate called SF_Replicate2. SF_Replicate2 does the initial download of data into a _Previous table, thus avoiding locking the main table for the duration of the replicate. Version 2.7.5 ===================================== Bug Fixes: - Fixed Memory Leak in DBAmp.dll Version 2.7.0 ===================================== Bug Fixes: - DBAmp modified to work correctly under SQL Server 2005 SP2 - Sf_BulkOps modified to flag invalid XML characters in the input table - Fixed Memory Leak in DBAmpReplicate.exe that affected SF_Replicate and SF_Refresh - SF_BulkOps modified to correctly handle missing ID values on insert - Support for SQL Server SP2 added New Features: - SF_BulkOps now has an Upsert option. This is particularly valuable in integration work. Version 2.4.5 ===================================== Bug Fixes: - Improved Error trapping for bulk inserts of Attachments Version 2.4.4 ===================================== Bug Fixes: - The DBAmp stored procedures have changed to retrieve the servername using SERVERPROPERTY instead of @@SERVERNAME. - The HTTP Connect and Receive timeouts can now be configured using a registry setting. Contact forceAmp support for more information. Version 2.4.3 ===================================== Bug Fixes: - Major changes to the sf_replicate and sf_refresh stored procedures to dramtically reduce the size of the SQL Transaction Log. In addition, the new procedures eliminate the need for the _Previous tables that existed in previous versions. Version 2.4.2 ===================================== Bug Fixes: - Fixed error ('OLE DB provider 'DBAmp.DBAmp.1' supplied inconsistent metadata for a column. Metadata information was changed at execution time.') that occurs when using the sf_replicate stored procedure. Version 2.4.1 ===================================== Bug Fixes: - Fixed an error that occurs when SQL INSERTing rows into the Attachment table. Version 2.4 ===================================== Important: Once again, the DBAmp Stored Procedures have changed. If you are installing version 2.4 over a previous version of DBAmp, you will need to rerun the Create DBAmp SPROCS.qry file. Previous versions of the stored procedure will not work with the new version of DBAmp. Features: - Ability to create primary and foreign keys on the local replicated tables. With this feature, you can build database diagrams of your Salesforce.com database. - Performance Improvements by eliminating some background queries to the salesforce servers. - Improved handling of multiple salesforce.com instances. The salesforce.com UserName/Password are no longer needed by the DBAmp Configuration program. These values are now picked up automatically from the SQL Linked Server definition. Version 2.3.11 ===================================== - Bug fixed when an alternate endpoint (like 7.0) is used with a permanent key Version 2.3.10 ===================================== - DBAmp can now incrementally refresh your local replicated tables. See the sf_refresh and sf_refreshall stored procedures in the documentation. Refreshing a table instead of replicating it can save significant time. - SF_Replicate now will RAISERROR if the procedure fails. This change was added to correctly denote failure when running under a job step. Previously, if the stored proc failed then the job step would report success anyway. This will, however, change the behavior of the SF_ReplicateAll stored procedure when used in a job. If an indivdual table fails to replicate, the job step will end at that point with failure rather than continuing on to the next table. You can alter this behavior by commenting out the RAISERROR statements at the bottom of the SF_Replicate stored procedure. - DBAmp now allows you enter proxy information if required by your network. For more information, see the documentation. Important: Once again, the SF_Replicate and SF_BulkOpts Stored Procedures have changed. If you are installing version 2.3.10 over a previous version of DBAmp, you will need to rerun the Create DBAmp SPROCS.qry file. Previous versions of the stored procedure will not work with the new version of DBAmp. DBAmp now requires V3.0.0.40 of the Office Toolkit (released 3/14/2006). Please download and install the latest version of the Toolkit from www.sforce.com/OfficeToolkit. For SQL Server 2000, you must be at Service Pack 3a or higher. Note that there is a problem with SQL Server 2000 Service Pack 4. If you are at Service Pack 4, you must obtain the hotfix from Microsoft at http://support.microsoft.com/?id=901200 . Version 2.3.9 ===================================== New in this release - Support for Upload files to Documents and Attachments (DBAmp/Pro only) - DBAmp now treats datetime values in a WHERE clause as being the local time, not UTC (as in previous versions). - Fix for table names beginning with 'Order' If you are upgrading from a previous release (other than 2.3.8), please see the information below on the SF_Replicate stored procedure changes.