So here are my settings in the crawl configuration file:

1. While JDBCSQL is empty, JDBCDeleteSQL does contain a query that returns items to be deleted. The query returns two columns, one being the primary key of documents (field1) and the other being the timestamp in seconds (field2).

<parameter name="JDBCSQL" type="string">
   <value></value>
 </parameter>

<parameter name="JDBCDeleteSQL" type="string">

   <value><![CDATA[exec GetContentsToDelete @FromTimeStampInSeconds = %TIMESTAMPSEC%]]></value>
</parameter>

2. <parameter name="JDBCDeleteTimeStampField" type="string">
    <value>field2</value>
   </parameter>

3. <parameter name="JDBCDeleteTimeType" type="string">
 <value>seconds</value>
   </parameter>

4. <parameter name="JDBCUpdateTimeStampOnDeletion" type="boolean">
 <description>
 <![CDATA[should the delete timestamp file still needs to be updated upon deletion <br>Default: false]]>
 </description>
 <value>true</value>
   </parameter>

5. <parameter name="JDBCPrimaryKeyField" type="string">
 <value>field1</value>
   </parameter>
   <parameter name="JDBCDeletePrimaryKeyField" type="string">
 <value>field1</value>
   </parameter>

6. <parameter name="OperationMode" type="string">
 <description>
 <![CDATA[Must be one of add, or update.
  Update should only be used if you want to do a partial update on already existing documents, i.e. during an incremental crawl. 
  Default: (add)]]>
 </description>
 <valid-values>add;update</valid-values>
 <value>update</value>
   </parameter>

#1- When performing the crawl to DELETE documents from FAST, the %TIMESTAMPSEC% is not getting picked from the JDBCLastCrawlTime.txt file and the query to the DB fails. #2- To bypass this, I replaced the %TIMESTAMPSEC% with a direct value, and the deletion of content works, however the jdbcconnector is unable to update the last crawl time to the JDBCLastCrawlTime.txt file in the WorkFolder.

ERROR Log in #1 crawl:

19:02:11,058 INFO  [JDBCAdapter] Running delete SQL: exec GetContentsToDelete @FromTimeStampInSeconds = %TIMESTAMPSEC%
19:02:11,105 ERROR [JDBCAdapter] Error when crawling the source system: Unable to execute JDBC crawl.
19:02:11,105 ERROR [JDBCAdapter] Caused by: Error while executing SQL: exec GetContentsToDelete @FromTimeStampInSeconds = %TIMESTAMPSEC%
19:02:11,105 ERROR [JDBCAdapter] Caused by: Error while executing SQL prepared statement:exec GetContentsToDelete @FromTimeStampInSeconds = %TIMESTAMPSEC%
19:02:11,105 ERROR [JDBCAdapter] Caused by: Incorrect syntax near '%'.
19:02:11,105 ERROR [JDBCAdapter] Adapter thread will terminate....

ERROR Log in #2 crawl

2014-12-01 16:33:42,314 INFO    [JDBCAdapter] Running postSQL
2014-12-01 16:33:42,314 WARN  [JDBCAdapter] Error setting LastCrawlDate
2014-12-01 16:33:42,314 WARN  [JDBCAdapter] Warning when crawling the source system: java.lang.NullPointerException
2014-12-01 16:33:42,314 INFO    [JDBCAdapter] Finished running postSQL

Any ideas on what could be the problem? Am sure I'm overlooking something very simple. Look forward to any help or suggestions.

• Edited by Ramkumar-007 Tuesday, December 02, 2014 5:38 AM formatting debug

Ref - jdbctemplate.xml -http://technet.microsoft.com/en-us/library/ff354942(v=office.14).aspx

 I just noticed something in the documentation which isn't extremely helpful, but adds confusion. Calling it out here, to see if someone has any inputs on it.

Under Transformation parameter group section,

However there is a small note below the section, that says:

Note
Do not use JDBCPrimaryKeyField and JDBCDeletePrimaryKeyField at the same time.

Confusion/Question:- JDBCPrimaryKeyField is Required, and the Note says do not use it along with JDBCDeletePrimaryKeyField at the same time. Hence for DELETES, what should be the configuration setting?

Hello,

JDBCDeletePrimaryKeyField is used specifically when you want to delete a specific Document ID in Fast.  Have you tried configuring a JDBC connector configuration file with the JDBCDeleteTimeType, JDBCDeleteTimeStampField JDBCDeleteSQLFile, or JDBCDeleteSQL parameters?  This parameter should allow you to configure the connector so that it will perform an incremental delete based on the date of the document.  If you have configured these parameters, can you show us your connector configuration for those parameters?

Thanks!

Rob Vazzana | Sr Support Escalation Engineer | US Customer Service & Support