Help get this topic noticed by sharing it on Twitter, Facebook, or email.
Silu Samal September 14, 2018 17:54

Unable to drop column

we have 552198 rows on Account_Load table, it throws below error while running below
EXEC SF_TableLoader 'upsert', 'SalesforceUAT','Account_Load','LLC_BI__lookupKey__c';

but it works with 50 or 100 records.

Please help.

--- Starting SF_TableLoader for Account_Load V3.6.5
13:51:30: Run the DBAmpNet2.exe program.
13:51:30: DBAmpNet2 3.6.5.0 (c) Copyright 2015-2017 forceAmp.com LLC
13:51:30: Parameters: upsert Account_Load DEVMISDB SFDC SalesforceUAT
13:51:30: Using the Salesforce bulkapi2 API.
13:51:34: Warning: BulkAPI operations without a Sort column could perform slowly.
13:51:34: Warning: Column CreatedDate ignored because it not creatable in the account object.
13:51:34: Warning: Column Id ignored because it not creatable in the account object.
13:51:34: Warning: Column Investors_Branch__r.Branch_Code__c ignored because it does not exist in the account object.
13:51:34: Warning: Column Signature_Type__c ignored because it does not exist in the account object.
13:51:34: Warning: Column Createdby ignored because it does not exist in the account object.
13:51:34: Warning: Column RecordId ignored because it does not exist in the account object.
13:51:34: Warning: Column Row_Hashbytes ignored because it does not exist in the account object.
13:51:41: Drop Account_Load_Result if it exists.
13:51:41: Create Account_Load_Result with new structure.
13:51:41: Drop Id column.
13:51:41: Add Id column.
13:51:41: Add Error column.
13:51:41: Error: Unable to drop column: Investors_Branch__r.Branch_Code__c from Account_Load_Result
13:51:41: System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '.'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at DBAmpNet2.BulkOpsJobSubmitProcessor.DropRecreateResultTable()
ClientConnectionId:19d432c0-6bc9-4b90-ab62-8ab1980a9a3b
Error Number:102,State:1,Class:15
13:51:41: DBAmpNet2 Operation FAILED.
13:51:43: Error: DBAmpNet2.exe was unsuccessful.
13:51:43: Error: Command string is E:\"Program Files"\DBAmp\DBAmpNet2.exe BulkOpsBulk "upsert" "Account_Load" "DEVMISDB" "SFDC" "SalesforceUAT" "LLC_BI__lookupKey__c" " "
--- Ending SF_TableLoader. Operation FAILED.
Msg 50000, Level 16, State 1, Procedure SF_TableLoader, Line 308 [Batch Start Line 25]
SF_TableLoader Error: 13:51:30: DBAmpNet2 3.6.5.0 (c) Copyright 2015-2017 forceAmp.com LLC13:51:30: Parameters: upsert Account_Load DEVMISDB SFDC SalesforceUAT13:51:30: Using the Salesforce bulkapi2 API.13:51:34: Warning: BulkAPI operations without a Sort column could perform slowly.13:51:34: Warning: Column CreatedDate ignored because it not creatable in the account object.13:51:34: Warning: Column Id ignored because it not creatable in the account object.13:51:34: Warning: Column Investors_Branch__r.Branch_Code__c ignored because it does not exist in the account object.13:51:34: Warning: Column Signature_Type__c ignored because it does not exist in the account object.13:51:34: Warning: Column Createdby ignored because it does not exist in the account object.13:51:34: Warning: Column RecordId ignored because it does not exist in the account object.13:51:34: Warning: Column Row_Hashbytes ignored because it does not exist in the account object.13:51:41: Drop Account_Load_Result if it exists.13:51:41: Create Account_Load_Result with new structure.13:51:41: Drop Id column.13:51:41: Add Id column.13:51:41: Add Error column.13:51:41: Error: Unable to drop column: Investors_Branch__r.Branch_Code__c from Account_Load_Result13:51:41: System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '.'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String method...
1 person has
this question +1
Reply
  • EMPLOYEE
    1
    Bill Emerson (Developer) September 14, 2018 18:32
    The correct way to use foreign key lookup is to name that column with the following: the foreign key field name and add a period followed by the external ID field name.

    In your case you are using a relationship name as a prefix and this is not supported.

    Replace Investors_Branch__r with the actual saleforce field name in the Account table (maybe Investors_Branch__c) followed by a period and the external id field.
    Comment
    good answer! good answer! (undo)
  • (some HTML allowed)
    How does this make you feel?     Add Image
    I'm

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

    Cancel
  • Silu Samal September 14, 2018 19:09
    Thank for this. how to proceed if we our foreign key field api name is same as external id name(with diffrent namespace)?

    Thanks
    Sam
    Comment
    good answer! good answer! (undo)
  • (some HTML allowed)
    How does this make you feel?     Add Image
    I'm

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

    Cancel
  • EMPLOYEE
    Bill Emerson (Developer) September 14, 2018 19:21
    Can you give an example ?
    Comment
    good answer! good answer! (undo)
  • (some HTML allowed)
    How does this make you feel?     Add Image
    I'm

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

    Cancel
  • Silu Samal September 17, 2018 18:25
    We have some objects has self reference,in that case how should we put column names?
    Comment
    good answer! good answer! (undo)
  • (some HTML allowed)
    How does this make you feel?     Add Image
    I'm

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

    Cancel
  • Silu Samal September 17, 2018 18:27
    Comment
    good answer! good answer! (undo)
  • (some HTML allowed)
    How does this make you feel?     Add Image
    I'm

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

    Cancel
  • EMPLOYEE
    Bill Emerson (Developer) September 17, 2018 18:47
    the foreign key field name, a period and the external ID field name.
    Comment
    good answer! good answer! (undo)
  • (some HTML allowed)
    How does this make you feel?     Add Image
    I'm

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

    Cancel
  • Silu Samal September 17, 2018 18:52
    it is not working. Below is the details.

    Here is the table

    FinServ__FinancialAccountNumber__c is External ID

    and AutoPayAccount__c.FinServ__FinancialAccountNumber__c foreign key

    CREATE TABLE [dbo].[FinServ__FinancialAccount__c_SDB_Load](
    [Box_Size__c] [NVARCHAR](50) NULL,
    [Branch__c.Branch_Code__c] [VARCHAR](255) NULL,
    [Discount_Description__c] [NVARCHAR](80) NULL,
    [Discount_Percentage__c] [DECIMAL](18, 2) NULL,
    [FinServ__BalanceLastStatement__c] [DECIMAL](18, 2) NULL,
    [FinServ__FinancialAccountNumber__c] [NVARCHAR](80) NULL,
    [FinServ__PaymentAmount__c] [DECIMAL](18, 2) NULL,
    [FinServ__PrimaryOwner__c.LLC_BI__lookupKey__c] [VARCHAR](255) NULL,
    [FinServ__Status__c] [NVARCHAR](255) NULL,
    [Last_Statement_Date__c] [DATETIME2](7) NULL,
    [AutoPayAccount__c.FinServ__FinancialAccountNumber__c] [VARCHAR](255) NULL,
    [Createdby] [VARCHAR](250) NULL,
    [CreatedDate] [DATETIME] NULL,
    [Error] [NVARCHAR](2000) NULL,
    [RecordId] [INT] IDENTITY(1,1) NOT NULL,
    [Row_Hashbytes] [VARBINARY](4000) NULL,
    [Id] [NCHAR](18) NULL
    ) ON [PRIMARY]
    GO

    --- Starting SF_TableLoader for FinServ__FinancialAccount__c_SDB_Load V3.6.5
    14:23:14: Run the DBAmpNet2.exe program.
    E:\"Program Files"\DBAmp\DBAmpNet2.exe BulkOpsBulk "upsert" "FinServ__FinancialAccount__c_SDB_Load" "DEVMISDB" "SFDC" "SALESFORCEUAT" "FinServ__FinancialAccountNumber__c" " "
    14:23:14: DBAmpNet2 3.6.5.0 (c) Copyright 2015-2017 forceAmp.com LLC
    14:23:14: Parameters: upsert FinServ__FinancialAccount__c_SDB_Load DEVMISDB SFDC SALESFORCEUAT
    14:23:14: Using the Salesforce bulkapi2 API.
    14:23:24: Warning: BulkAPI operations without a Sort column could perform slowly.
    14:23:24: Warning: Column CreatedDate ignored because it not creatable in the finserv__financialaccount__c object.
    14:23:24: Warning: Column Id ignored because it not creatable in the finserv__financialaccount__c object.
    14:23:24: Warning: Column AutoPayAccount__c.FinServ__FinancialAccountNumber__c ignored because it does not exist in the finserv__financialaccount__c object.
    14:23:24: Warning: Column Createdby ignored because it does not exist in the finserv__financialaccount__c object.
    14:23:24: Warning: Column RecordId ignored because it does not exist in the finserv__financialaccount__c object.
    14:23:24: Warning: Column Row_Hashbytes ignored because it does not exist in the finserv__financialaccount__c object.
    14:23:24: Drop FinServ__FinancialAccount__c_SDB_Load_Result if it exists.
    14:23:24: Create FinServ__FinancialAccount__c_SDB_Load_Result with new structure.
    14:23:24: Drop Id column.
    14:23:24: Add Id column.
    14:23:24: Update Error column in FinServ__FinancialAccount__c_SDB_Load_Result.
    14:23:24: Error: Unable to drop column: AutoPayAccount__c.FinServ__FinancialAccountNumber__c from FinServ__FinancialAccount__c_SDB_Load_Result
    14:23:24: System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '.'.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    at DBAmpNet2.BulkOpsJobSubmitProcessor.DropRecreateResultTable()
    ClientConnectionId:5ef2da99-89ca-4eb7-a0af-a5893881b9be
    Error Number:102,State:1,Class:15
    14:23:24: DBAmpNet2 Operation FAILED.
    14:23:26: Error: DBAmpNet2.exe was unsuccessful.
    14:23:26: Error: Command string is E:\"Program Files"\DBAmp\DBAmpNet2.exe BulkOpsBulk "upsert" "FinServ__FinancialAccount__c_SDB_Load" "DEVMISDB" "SFDC" "SALESFORCEUAT" "FinServ__FinancialAccountNumber__c" " "
    --- Ending SF_TableLoader. Operation FAILED.
    Comment
    good answer! good answer! (undo)
  • (some HTML allowed)
    How does this make you feel?     Add Image
    I'm

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

    Cancel
  • EMPLOYEE
    Bill Emerson (Developer) September 17, 2018 18:56
    Is there a field on the FinServ__FinancialAccount__c object with the complete api name of AutoPayAccount__c ? Maybe you are missing a prefix to that name.
    Comment
    good answer! good answer! (undo)
  • (some HTML allowed)
    How does this make you feel?     Add Image
    I'm

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

    Cancel
  • Silu Samal September 17, 2018 19:03
    Yes,
    See my attached image

    https://d2r1vs3d9006ap.cloudfront.net...
    Comment
    good answer! good answer! (undo)
  • (some HTML allowed)
    How does this make you feel?     Add Image
    I'm

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

    Cancel
  • EMPLOYEE
    Bill Emerson (Developer) September 17, 2018 19:15
    When you run the following query, is that column in the result set:

    select top(1) * from SALESFORCEUAT...FinServ__FinancialAccount__c
    Comment
    good answer! good answer! (undo)
  • (some HTML allowed)
    How does this make you feel?     Add Image
    I'm

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

    Cancel
  • Silu Samal September 17, 2018 19:37
    you are correct ,i don't see that column exists on FinServ__FinancialAccount__c but it exists on sys_sffields_View. How do we map these kind of senario?
    Comment
    good answer! good answer! (undo)
  • (some HTML allowed)
    How does this make you feel?     Add Image
    I'm

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

    Cancel
  • EMPLOYEE
    Bill Emerson (Developer) September 17, 2018 19:45
    If it is in sys_sffields, then it should be in the query.

    Take a look at the field level security on salesforce and make sure the field is visible to the salesforce user the linked server is using.

    DBAmp caches metadat. Run the following query to flush the metadata cache and reload with the latest:

    Select * from salesforceUAT...sys_sfobjects
    Comment
    good answer! good answer! (undo)
  • (some HTML allowed)
    How does this make you feel?     Add Image
    I'm

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

    Cancel