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

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
  • 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.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • 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
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

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

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

  • We have some objects has self reference,in that case how should we put column names?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

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

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

  • the foreign key field name, a period and the external ID field name.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • 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.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • 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.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

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

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

  • When you run the following query, is that column in the result set:

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

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

  • 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?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • 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
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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