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...
Help get this topic noticed by sharing it on
Twitter,
Facebook, or email.


-
EMPLOYEE
1The 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. -
-
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 -
-
-
-
We have some objects has self reference,in that case how should we put column names?
-
-
-
-
the foreign key field name, a period and the external ID field name.
-
-
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. -
-
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.
-
-
-
-
When you run the following query, is that column in the result set:
select top(1) * from SALESFORCEUAT...FinServ__FinancialAccount__c -
-
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?
-
-
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 -