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

Unable to retrieve session properties. Cannot initialize the data source object of OLE DB provider "DBAmp.DBAmp" for linked server

Error when I tried to upload data to Salesforce. Any suggestion is appreciated.
-------------------------------------------------------------------
Error Message:
Unable to retrieve session properties. Cannot initialize the data source object of OLE DB provider "DBAmp.DBAmp" for linked server
-------------------------------------------------------------------
Command Used:

EXEC @SUCESS = [dbo].[SF_TableLoader] 'upsert:bulkapi,IgnoreFailures(100)', 'SALESFORCEUAT', 'LLC_BI__Loan__c_SubloanCIF_Load', 'Source_System_ID__c'
---------------------------------ERROR----------------------------------
08:26:35: Run the DBAmpNet2.exe program.
C:\"Program Files"\DBAmp\DBAmpNet2.exe BulkOpsBulk "upsert:bulkapi," "LLC_BI__Loan__c_SubloanCIF_Load" "DEVSALESFORCEDB" "SFDC" "SALESFORCEUAT" "Source_System_ID__c" ""
08:26:36: DBAmpNet2 3.7.3.0 (c) Copyright 2015-2017 forceAmp.com LLC
08:26:36: Parameters: upsert LLC_BI__Loan__c_SubloanCIF_Load DEVSALESFORCEDB SFDC SALESFORCEUAT
08:26:36: Using the Salesforce bulkapi2 API.
08:26:36: Retrying
08:26:36: Retrying
08:26:36: Retrying
08:26:36: Error: Unable to retrieve session properties.
08:26:36: System.Data.SqlClient.SqlException (0x80131904): Cannot initialize the data source object of OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCEUAT".
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.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds( cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParamet
erEncryptionRequest)CommandBehavior
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolea
n inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at DBAmpNet2.SysSFSession.Retrieve(SqlConnection currentConnection)
ClientConnectionId:d4fe7783-e2fa-4f8f-bcc7-b2055b40d093
Error Number:7303,State:1,Class:16
08:26:36: No files exist in the following directory: E:\DBAmp Work Directory\
08:26:36: DBAmpNet2 Operation FAILED.
08:27:03: Allowed Failure Percent = 100.
Msg 208, Level 16, State 1, Line 3
Invalid object name 'dbo.LLC_BI__Loan__c_SubloanCIF_Load_Result'.
Invalid object name 'LLC_BI__Loan__c_SubloanCIF_Load_Result'.

[TASK_ERROR_DESCRIPTION] = Invalid object name 'LLC_BI__Loan__c_SubloanCIF_Load_Result'.
--END-------------------------
sp_execute_sync_task_sf_upsert
------------------------------
1 person has
this problem
+1
Reply
  • Was this working before and now has stopped ?

    Are other DBAmp commands working ?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • No it did not work in the past, this is the first time we are trying in this environment. It worked in other development environment. We restored database from old development environment to the new one and error is shown on this newly restored database.

    This is the only command we use in current project.

    Thanks for very quick response, really appreciate it.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Can you post a screenshot of the DBAmp.DBAmp Provider Options page (Server Objects/Linked Servers/Providers / right click on DBAmp.DBAmp and choose Properties.) and the Server Options page of SALESFORCEUAT linked server?

    Also, can you post the complete message output, including the starting message?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Full Message:
    --- Starting SF_TableLoader for LLC_BI__Loan__c_Subloan_Sublimit_Load V3.6.5
    13:52:13: Run the DBAmpNet2.exe program.
    C:\"Program Files"\DBAmp\DBAmpNet2.exe BulkOpsBulk "upsert:bulkapi," "LLC_BI__Loan__c_Subloan_Sublimit_Load" "DEVSALESFORCEDB" "SFDC" "SALESFORCEUAT" "Source_System_ID__c" ""
    13:52:14: DBAmpNet2 3.7.3.0 (c) Copyright 2015-2017 forceAmp.com LLC
    13:52:14: Parameters: upsert LLC_BI__Loan__c_Subloan_Sublimit_Load DEVSALESFORCEDB SFDC SALESFORCEUAT
    13:52:14: Using the Salesforce bulkapi2 API.
    13:52:14: Retrying
    13:52:14: Retrying
    13:52:14: Retrying
    13:52:14: Error: Unable to retrieve session properties.
    13:52:14: System.Data.SqlClient.SqlException (0x80131904): Cannot initialize the data source object of OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCEUAT".
    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.SqlDataReader.TryConsumeMetaData()
    at System.Data.SqlClient.SqlDataReader.get_MetaData()
    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParamet
    erEncryptionRequest)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolea
    n inRetry)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader()
    at DBAmpNet2.SysSFSession.Retrieve(SqlConnection currentConnection)
    ClientConnectionId:464957fc-bcc7-4a26-a452-ec6f72385393
    Error Number:7303,State:1,Class:16
    13:52:14: No files exist in the following directory: E:\DBAmp Work Directory\
    13:52:14: DBAmpNet2 Operation FAILED.
    13:52:40: Allowed Failure Percent = 100.
    13:52:41: Percent Failed = 0.000.
    --- Ending SF_TableLoader. Operation successful.

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

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

  • You skipped a step when you upgraded, you did not update the stored procs. Navigate to the Program Files/DBAmp/SQL directory and run the Create DBAmp SPROCS.sql file in your Salesforce database.

    Also, can I see all three pages of the SALESFORCEUAT linked server?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • I logged in to Server directly as DBA and ran the following script and it executed without error. If I run from my own user, it still the message:" Error: Unable to retrieve session properties.". What permissions are missing for my user?

    -----------Script:

    EXEC [dbo].[SF_TableLoader] 'upsert:bulkapi,IgnoreFailures(100)', 'SALESFORCEUAT', 'LLC_BI__Loan__c_Subloan_Sublimit_Load', 'Source_System_ID__c'

    -------------Message Log (using DBA/sa account):

    --- Starting SF_TableLoader_Investors for LLC_BI__Loan__c_Subloan_Sublimit_Load V3.7.3
    SF_TableLoader:A595445B-B66F-4254-965F-8
    15:10:41: Run the DBAmpNet2.exe program.
    C:\"Program Files"\DBAmp\DBAmpNet2.exe BulkOpsBulk "upsert:bulkapi," "LLC_BI__Loan__c_Subloan_Sublimit_Load" "DEVSALESFORCEDB" "SFDC" "SALESFORCEUAT" "Source_System_ID__c" ""
    15:10:42: DBAmpNet2 3.7.3.0 (c) Copyright 2015-2017 forceAmp.com LLC
    15:10:42: Parameters: upsert LLC_BI__Loan__c_Subloan_Sublimit_Load DEVSALESFORCEDB SFDC SALESFORCEUAT
    15:10:42: Using the Salesforce bulkapi2 API.
    15:10:45: Sort column will be used to order input rows.
    15:10:45: Warning: Column LLC_BI__Amount_Available__c ignored because it not creatable in the llc_bi__loan__c object.
    15:10:45: Warning: Column Id ignored because it not creatable in the llc_bi__loan__c object.
    15:10:45: Warning: Column CreatedDate ignored because it not creatable in the llc_bi__loan__c object.
    15:10:45: Warning: Column Sort ignored because it does not exist in the llc_bi__loan__c object.
    15:10:45: Warning: Column Row_Hashbytes ignored because it does not exist in the llc_bi__loan__c object.
    15:10:45: Warning: Column TASK_ID ignored because it does not exist in the llc_bi__loan__c object.
    15:10:45: Warning: Column Createdby ignored because it does not exist in the llc_bi__loan__c object.
    15:10:45: Drop LLC_BI__Loan__c_Subloan_Sublimit_Load_Result if it exists.
    15:10:45: Create LLC_BI__Loan__c_Subloan_Sublimit_Load_Result with new structure.
    15:10:45: Update Error column in LLC_BI__Loan__c_Subloan_Sublimit_Load_Result.
    15:10:45: Drop Id column.
    15:10:45: Add Id column.
    15:10:45: 2 rows read from SQL Table.
    15:10:45: Job 750M0000003D89OIAS created on salesforce.
    15:10:46: Using the bulkapi with polling every 60 seconds
    15:11:01: Job Complete.
    15:11:32: 2 rows successfully processed.
    15:11:32: 0 rows failed. See Error column of row for more information.
    15:11:32: 0 rows unprocessed.
    15:11:32: Deleted file: E:\DBAmp Work Directory\LLC_BI__Loan__c_Subloan_Sublimit_Load.csv
    15:11:32: DBAmpNet2 Operation successful.
    15:11:58: Allowed Failure Percent = 100.
    15:11:59: Percent Failed = 0.000.
    --- Ending SF_TableLoader. Operation successful.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Can I see all three pages of the SALESFORCEUAT linked server?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Also, I want to open a case on our end, what company name is your DBAmp license under?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • This reply was removed on 2019-02-05.
    see the change log
  • Take the login mapping off the Security page of linked server and just use the login credentials at the bottom. Then retry the command that is failing.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Could you hide/delete the image. because it has login name, i dont want it to be in public forum. Please delete it, i is not allowing me to do so.

    It worked! I removed the login name as you suggested.

    Please note that we have login in other development and it working fine there. Do you know why it is a non-issue in other environment?

    Bill/Justin,

    Really really appreciate your help. you guys rock!
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Do you have a login mapping similar to what you had here or not?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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