Help get this topic noticed by sharing it on Twitter, Facebook, or email.

Copy User table to SFDCUser table -- Failing

We have a job with 2 steps, Job2 is failing for last 1 month, Could you please help us to understand this. Below are 2 job steps.
Job(1) :Exec SF_ReplicateAll 'EYEFORCE'.
Job (2):
USE [salesforce backups]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SFDCUser]') AND type in (N'U'))
DROP TABLE [dbo].[SFDCUser]
GO

USE [salesforce backups]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

SELECT * INTO [dbo].[SFDCUser] FROM [dbo].[User]
GO

SET ANSI_PADDING OFF
GO
Error message:
Message
Executed as user: NT AUTHORITY\SYSTEM. ...--- Starting SF_Replicate for Account [SQLSTATE 01000] (Message 0) 09:50:11: Drop Account_Previous if it exists. [SQLSTATE 01000] (Message 0) 09:50:11: Create Account_Previous with new structure. [SQLSTATE 01000] (Message 0) 09:50:11: Run the DBAmp.exe program. [SQLSTATE 01000] (Message 0) 09:50:12: DBAmp Bulk Operations. V2.16.3 (c) Copyright 2006-2013 forceAmp.com LLC [SQLSTATE 01000] (Message 0) 09:50:12: Populating local table Account_Previous , AMOBISQL01 / salesforce backups . [SQLSTATE 01000] (Message 0) 09:50:12: DBAmp is using the SQL Native Client. [SQLSTATE 01000] (Message 0) 09:50:12: Opening SQL Server rowset [SQLSTATE 01000] (Message 0) 09:56:36: Error: Fatal error occurred mapping salesforce object into buffer. [SQLSTATE 01000] (Message 0) 09:56:36: Error: Replication failed. [SQLSTATE 01000] (Message 0) Source: ForceAmpAPI.FAField.1 [SQLSTATE 01000] (Message 0) Description: Failed to convert server data type to variant type::AMO_Cataract_YTD_Growth_Per_no_equip__c [SQLSTATE 01000] (Message 0) 09:57:17: Error: DBAmp.exe was unsuccessful. [SQLSTATE 01000] (Message 0) 09:57:17: Error: Command string is E:\"Program Files"\DBAmp\DBAmp.exe Export "Account_Previous" "AMOBISQL01" "salesforce backups" "EYEFORCE" [SQLSTATE 01000] (Message 0) --- Ending SF_Replicate. Operation FAILED. [SQLSTATE 01000] (Message 0) Error: SF_Replicate failed for table Account [SQLSTATE 01000] (Message 0) Error 50000, Severity 16, State 1, Line 214 [SQLSTATE 01000] (Message 0) --- Ending SF_Replicate. Operation FAILED. [SQLSTATE 01000] (Message 0) --- Starting SF_Replicate for AccountContactRole [SQLSTATE 01000] (Message 0) 09:57:17: Drop AccountContactRole_Previous if it exists. [SQLSTATE 01000] (Message 0) 09:57:17: Create AccountContactRole_Previous with new structure. [SQLSTATE 01000] (Message 0) 09:57:18: Run the DBAmp.exe program. [SQLSTATE 01000] (Message 0) 09:57:18: DBAmp Bulk Operations. V2.16.3 (c) Copyright 2006-2013 forceAmp.com LLC [SQLSTATE 01000] (Message 0) 09:57:18: Populating local table AccountContactRole_Previous , AMOBISQL01 / salesforce backups . [SQLSTATE 01000] (Message 0) 09:57:18: DBAmp is using the SQL Native Client. [SQLSTATE 01000] (Message 0) 09:57:18: Opening SQL Server rowset [SQLSTATE 01000] (Message 0) 09:57:18: 0 rows copied. [SQLSTATE 01000] (Message 0) 09:57:18: Drop AccountContactRole if it exists. [SQLSTATE 01000] (Message 0) 09:57:18: Rename previous table from AccountContactRole_Previous to AccountContactRole [SQLSTATE 01000] (Message 0) Caution: Changing any part of an object name could break scripts and stored procedures. [SQLSTATE 01000] (Message 15477) 09:57:18: Create primary key on AccountContactRole [SQLSTATE 01000] (Message 0) --- Ending SF_Replicate. Operation successful. [SQLSTATE 01000] (Message 0) --- Starting SF_Replicate for AccountHistory [SQLSTATE 01000] (Message 0) 09:57:18: Drop AccountHistory_Previous if it exists. [SQLSTATE 01000] (Message 0) 09:57:18: Create AccountHistory_Previous with new structure. [SQLSTATE 01000] (Message 0) 09:57:19: Run the DBAmp.exe program. [SQLSTATE 01000] (Message 0) 09:57:19: DBAmp Bulk Operations. V2.16.3 (c) Copyright 2006-2013 forceAmp.com LLC [SQLSTATE 01000] (Message 0) 09:57:19: Populating local table AccountHistory_Previous , AMOBISQL01 / salesforce backups . [SQLSTATE 01000] (Message 0) 09:57:19: DBAmp is using the SQL Native Client. [SQLSTATE 01000] (Message 0) 09:57:19: Opening SQL Server rowset [SQLSTATE 01000] (Message 0) 10:01:40: 151202 rows copied. [SQLSTATE 01000] (Message 0) 10:01:40: Drop AccountHistory if it exists. [SQLSTATE 01000] (Message 0) 10:01:41: Rename previous table from AccountHistory_Previous to AccountHistory [SQLSTATE 01000] (Message 0) Caution: Changing any part of an object name could break scripts and stored procedures. [SQLSTATE 01000] (Message 15477) 10:01:41: Create primary key on AccountHistory [SQLSTA... The step failed.
1 person has
this problem
+1
Reply
  • Description: Failed to convert server data type to variant type::AMO_Cataract_YTD_Growth_Per_no_equip__c

    There is a problem with data in the field shown above.

    Is this a formula field ? If so what is its definition and is it possible that it is calculating a value that is absurd ?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Hi Bill,
    Thanks for your reply.
    This is a formula field.
    IF (AMO_Cataract_PYTD_no_equip__c<>0,((AMO_Cataract_YTD_no_equip__c - AMO_Cataract_PYTD_no_equip__c)/AMO_Cataract_PYTD_no_equip__c) ,0)
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated

  • What is the precision and scale of the field as defined in salesforce ?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated

  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Somehow that calculation is exceeding a 17,1 number for some values.

    Try rounding the calculation :

    IF.... Round(calc,1) would round the result of the calculation to 1 decimal point

    Also, run the following query in SQL to see some of the large values

    Select Top 10 AMO_Cataract_YTD_Growth_Per_no_equip__c
    from EYEFORCE...Account
    order by AMO_Cataract_YTD_Growth_Per_no_equip__c
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Hi Bill,
    Got below values when I run SQL query which you suggested.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated

  • What about this query:

    Select Top 10 AMO_Cataract_YTD_Growth_Per_no_equip__c
    from EYEFORCE...Account
    order by AMO_Cataract_YTD_Growth_Per_no_equip__c desc
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Hi Bill,
    Manually run the job now and failed with below error this time.
    Executed as user: NT AUTHORITY\SYSTEM. ...--- Starting SF_Replicate for Account [SQLSTATE 01000] (Message 0) 13:13:13: Drop Account_Previous if it exists. [SQLSTATE 01000] (Message 0) 13:13:13: Create Account_Previous with new structure. [SQLSTATE 01000] (Message 0) 13:13:13: Run the DBAmp.exe program. [SQLSTATE 01000] (Message 0) 13:13:14: DBAmp Bulk Operations. V2.16.3 (c) Copyright 2006-2013 forceAmp.com LLC [SQLSTATE 01000] (Message 0) 13:13:14: Populating local table Account_Previous , AMOBISQL01 / salesforce backups . [SQLSTATE 01000] (Message 0) 13:13:18: DBAmp is using the SQL Native Client. [SQLSTATE 01000] (Message 0) 13:13:18: Opening SQL Server rowset [SQLSTATE 01000] (Message 0) 13:21:13: 109739 rows copied. [SQLSTATE 01000] (Message 0) 13:21:13: Drop Account if it exists. [SQLSTATE 01000] (Message 0) 13:21:13: Rename previous table from Account_Previous to Account [SQLSTATE 01000] (Message 0) Caution: Changing any part of an object name could break scripts and stored procedures. [SQLSTATE 01000] (Message 15477) 13:21:14: Create primary key on Account [SQLSTATE 01000] (Message 0) --- Ending SF_Replicate. Operation successful. [SQLSTATE 01000] (Message 0) --- Starting SF_Replicate for AccountContactRole [SQLSTATE 01000] (Message 0) 13:21:16: Drop AccountContactRole_Previous if it exists. [SQLSTATE 01000] (Message 0) 13:21:16: Create AccountContactRole_Previous with new structure. [SQLSTATE 01000] (Message 0) 13:21:17: Run the DBAmp.exe program. [SQLSTATE 01000] (Message 0) 13:21:18: DBAmp Bulk Operations. V2.16.3 (c) Copyright 2006-2013 forceAmp.com LLC [SQLSTATE 01000] (Message 0) 13:21:18: Populating local table AccountContactRole_Previous , AMOBISQL01 / salesforce backups . [SQLSTATE 01000] (Message 0) 13:21:18: DBAmp is using the SQL Native Client. [SQLSTATE 01000] (Message 0) 13:21:18: Opening SQL Server rowset [SQLSTATE 01000] (Message 0) 13:21:19: 0 rows copied. [SQLSTATE 01000] (Message 0) 13:21:19: Drop AccountContactRole if it exists. [SQLSTATE 01000] (Message 0) 13:21:19: Rename previous table from AccountContactRole_Previous to AccountContactRole [SQLSTATE 01000] (Message 0) Caution: Changing any part of an object name could break scripts and stored procedures. [SQLSTATE 01000] (Message 15477) 13:21:19: Create primary key on AccountContactRole [SQLSTATE 01000] (Message 0) --- Ending SF_Replicate. Operation successful. [SQLSTATE 01000] (Message 0) --- Starting SF_Replicate for AccountHistory [SQLSTATE 01000] (Message 0) 13:21:19: Drop AccountHistory_Previous if it exists. [SQLSTATE 01000] (Message 0) 13:21:19: Create AccountHistory_Previous with new structure. [SQLSTATE 01000] (Message 0) 13:21:19: Run the DBAmp.exe program. [SQLSTATE 01000] (Message 0) 13:21:19: DBAmp Bulk Operations. V2.16.3 (c) Copyright 2006-2013 forceAmp.com LLC [SQLSTATE 01000] (Message 0) 13:21:19: Populating local table AccountHistory_Previous , AMOBISQL01 / salesforce backups . [SQLSTATE 01000] (Message 0) 13:21:19: DBAmp is using the SQL Native Client. [SQLSTATE 01000] (Message 0) 13:21:19: Opening SQL Server rowset [SQLSTATE 01000] (Message 0) 13:23:27: 151203 rows copied. [SQLSTATE 01000] (Message 0) 13:23:27: Drop AccountHistory if it exists. [SQLSTATE 01000] (Message 0) 13:23:27: Rename previous table from AccountHistory_Previous to AccountHistory [SQLSTATE 01000] (Message 0) Caution: Changing any part of an object name could break scripts and stored procedures. [SQLSTATE 01000] (Message 15477) 13:23:27: Create primary key on AccountHistory [SQLSTATE 01000] (Message 0) --- Ending SF_Replicate. Operation successful. [SQLSTATE 01000] (Message 0) --- Starting SF_Replicate for AccountPartner [SQLSTATE 01000] (Message 0) 13:23:27: Drop AccountPartner_Previous if it exists. [SQLSTATE 01000] (Message 0) 13:23:27: Create AccountPartner_Previous with new structure. [SQLSTATE 01000] (Message 0) 13:23:28: Run the DBAmp.exe program. [SQLSTATE 01000] (Message 0) 13:23:... The step failed.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated

  • OK, we are passed the first error but now have a second issue.

    Unfortunately, the job history is truncated and we cannot see the error message.

    Please modify the job step using the Advanced tab and capture the output to a table.

    Then rerun the job. Return to the advanced tab and view the table to see the output.

    Forward the output to support at forceamp.com for analysis. Do not post here, it will be too long and truncated by the forum software.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated

  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Hi Bill,
    This time it ran to success, I am not sure what it caused for failure(Could be because of huge value which exceeds more than 17 numbers ).
    Anyway I appreciate your support.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated